The Scenario
The board presentation is on Thursday. You're an ESG analyst at a real estate fund, and one section of the deck covers environmental risk by property. You have a Google Sheet with 50 properties — property name in column A, latitude in column B, longitude in column C. You need AQI scores and dominant pollutant data for each one to populate the risk matrix. The request landed in your inbox this morning.
The bad version:
- You find the OpenWeather Air Pollution API documentation, figure out the correct endpoint format (it takes lat/lon as query parameters, not city names), and start calling it manually — copying coordinates from columns B and C, pasting them into a request URL, sending the call, and transcribing the AQI value and main pollutant field from the JSON response into columns D and E.
- The JSON response nests the AQI inside
list[0].main.aqiand the pollutants insidelist[0].components— you have to mentally decode a different structure than the current weather endpoint, and you get it wrong twice before you figure out which number maps to which field. - 50 rows. At two minutes per row, that's over an hour and a half — and the board deck is due at 4 PM.
This isn't research work. It's data movement. And it's eating time that was supposed to go toward the analysis itself.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the coordinates from columns B and C, calls the OpenWeather Air Pollution API for each property, and writes the AQI score and dominant pollutant into the columns you specify — including conditional formatting logic if you ask for it.
For each location in this sheet, use the latitude in column B and longitude in column C to fetch the current air pollution data from OpenWeather and write the AQI value and dominant pollutant into columns D and E — highlight any row red where AQI is above 150
What You Get
- Column D: AQI score on the OpenWeather scale (1–5 index) for each property
- Column E: dominant pollutant label (e.g., PM2.5, NO2, O3) for each location
- Rows where AQI exceeds 150 are highlighted or flagged per your instruction
- Properties where coordinates are missing or malformed are marked so you can address them before the presentation
What If the Data Is Not Quite Ready
Some coordinates are entered with extra spaces or in the wrong decimal format
Clean up the coordinate values in columns B and C (remove extra spaces, standardize to 6 decimal places), then fetch current AQI and dominant pollutant from OpenWeather for each row and write results into columns D and E
You need AQI broken down by pollutant component, not just the composite score
For each property in this sheet, use lat/lon from columns B and C to fetch current air pollution data from OpenWeather — write the composite AQI into column D, and write individual component values (PM2.5, PM10, NO2, O3, CO) into columns E through I
The properties span two tabs — 'Residential' and 'Commercial' — and both need AQI enrichment
Fetch current AQI and dominant pollutant from OpenWeather for all properties in the 'Residential' tab (lat in col B, lon in col C, output to D and E) and all properties in the 'Commercial' tab (same column layout) — apply the same logic to both sheets
Get the data, flag the high-risk properties, and draft a summary sentence for the board section — all at once
Fetch current AQI and dominant pollutant from OpenWeather for all 50 properties using lat/lon from columns B and C, write values into columns D and E, flag column F as 'HIGH RISK' for AQI above 150 and 'MODERATE RISK' for AQI between 100–150, then add a summary row at the bottom counting properties in each risk category
One prompt handles the fetch, the risk classification, and the summary — so the board section is ready when you open the sheet.
Try It
Open the property sheet and Get the 7-day free trial of SheetXAI — ask it to fetch AQI scores for all 50 rows and flag the high-risk properties before your next environmental report. You can also explore how SheetXAI handles historical AQI trend tables or browse the OpenWeather API hub for the full list of supported data types.