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 is decimal degrees with five places versus three, causing the API to return no results.
- Discover that 40 of the 200 coordinates land in ocean or rural areas with no nearby station, which the script handles by crashing rather than flagging.
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 Google Sheet that can read your coordinate columns and call IQAir AirVisual's nearest-station lookup for each row — no scripting, no JSON parsing.
For each row in my Properties sheet with lat/lon coordinates in columns C and D, look up the nearest AirVisual monitoring station and write its current AQI and station name into columns E and F.
What You Get
- Column E: current AQI from the nearest IQAir monitoring station to each property.
- Column F: station name or city name returned by IQAir.
- Rows where no nearby station exists get a note in column G 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 AQI for each property and write the results into columns E and F.
Some rows have duplicate coordinates from data entry errors
Before calling IQAir, deduplicate the properties in my Properties sheet where columns C and D have identical lat/lon values, flag the duplicates in column H, then fetch the nearest station AQI for each unique coordinate.
You need to join the AQI result with a second tab of building classifications
Look up the nearest station AQI from IQAir for each property in my Properties sheet using columns C and D, write the AQI into column E, then join on property ID to add the building classification from my Classifications tab into column F.
Clean coordinates, pull AQI, score each property, and flag outliers in one go
Normalize the coordinate formats in columns C and D of my Properties sheet, fetch the nearest station AQI from IQAir for each row, write it into column E, compute an environmental risk score in column F as AQI divided by 50 rounded to one decimal, and flag any property with a score above 3.0 in column G.
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 and station name. 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 Google Sheets.