The Scenario
The environmental scoring model your team built has been sitting in review for six weeks waiting on one column: nearest monitoring station AQI for each of the 200 properties in the dataset. Nobody set up the AirVisual API call when the model was built because it seemed like something that would get done later. Later is now, because the ESG committee meets Friday.
The bad version:
- Write or borrow a script to call the AirVisual nearest-city endpoint for each pair of lat/lon coordinates, handle auth, parse the JSON, and write the AQI and station name back into the right rows.
- Debug the rows where the coordinate format uses too many decimal places for the API to resolve, causing it to return no results.
- Discover that 40 of the 200 coordinates land in rural areas with no nearby station, which the script handles by crashing rather than flagging cleanly.
If you had the time and the background to do this cleanly, you would have done it when the model was first built.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook that can read your coordinate columns and call IQAir AirVisual's nearest-station lookup for each row — no scripting, no JSON parsing.
I have 150 warehouse locations with GPS coordinates in my Assets sheet. Use IQAir to find the nearest station for each and fill in the station name, current AQI, and main pollutant in columns F, G, H.
What You Get
- Column F: station name or city name returned by IQAir for each property.
- Column G: current AQI from the nearest monitoring station.
- Column H: main pollutant at that station.
- Rows where no nearby station exists get a note in column I rather than a silent null that breaks downstream formulas.
What If the Data Is Not Quite Ready
The coordinate columns have mixed formats
The lat/lon values in columns C and D are inconsistent — some rows use decimal degrees, some use degrees-minutes format. Normalize them all to decimal degrees first, then look up the nearest AirVisual station for each row and write the station name, AQI, and main pollutant into columns F, G, H.
Some rows have duplicate coordinates from data entry errors
Before calling IQAir, deduplicate the properties in my Assets sheet where columns C and D have identical lat/lon values, flag the duplicates in column I, then fetch the nearest station AQI for each unique coordinate.
You need to join the AQI result with a second worksheet of building classifications
Look up the nearest station from IQAir for each property in my Assets sheet using columns C and D, write the station name, AQI, and pollutant into columns F, G, H, then join on property ID to add the building classification from my Classifications worksheet into column I.
Clean coordinates, pull AQI, score each property, and flag outliers in one go
Normalize the coordinate formats in columns C and D of my Assets sheet, fetch the nearest station AQI from IQAir for each row, write it into column G, compute an environmental risk score in column H as AQI divided by 50 rounded to one decimal, and flag any property with a score above 3.0 in column I.
One instruction handles the normalization, the lookup, the calculation, and the flag.
Try It
Get the 7-day free trial of SheetXAI and open the property dataset with your coordinate columns, then ask it to populate the nearest-station AQI, station name, and pollutant. For related workflows, see the spoke on building a dual-scale compliance table, or the hub overview on all methods for connecting IQAir AirVisual to Excel.