The Scenario
It is 7:45 AM and you have a client delivery at 9. The Google Sheet has sixty global cities in columns A through C — city, state, country — and columns D through G are blank. Those are the AQI, PM2.5, main pollutant, and temperature fields the client expects populated. You have been filling them manually since this contract started, which was fine for ten cities in the pilot. It is not fine now.
The bad version:
- Open IQAir's web interface, search each city by name, read the AQI off the card, type it into column D, then do the same for PM2.5, pollutant, and temperature.
- Repeat that sequence sixty times while the client's data keeps changing because AQI is live.
- Deliver a sheet where city 1 and city 60 were pulled ninety minutes apart, making the dataset inconsistent before it even lands.
The cognitive load of manual data entry at scale is a tax on your attention. You are being paid for environmental analysis, not for reading numbers off a web interface and typing them into cells.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet that reads your location columns and pulls from IQAir AirVisual for you. No API key management, no field mapping, no template to configure.
For each city/state/country in columns A, B, C of my Cities sheet, fetch the current air quality from IQAir and write the US AQI, PM2.5 concentration, main pollutant, and temperature into columns D, E, F, G.
What You Get
- Column D: US AQI value for each city at the moment of the pull.
- Column E: PM2.5 concentration in micrograms per cubic meter.
- Column F: Main pollutant identifier (PM2.5, PM10, O3, NO2, etc.).
- Column G: Current temperature at that location.
- Rows where IQAir returns no data get a status note in an adjacent column rather than silently blank cells.
What If the Data Is Not Quite Ready
The city names use local language spellings
The city names in column A use local-language spellings for some Asian cities. Normalize them to English transliterations first, then fetch the AQI from IQAir and write the results into columns D through G.
Some rows are missing the state field
Column B is blank for 12 rows where the country has no state-level divisions. Use the country in column C and city in column A to look up AQI anyway, and flag any rows that still come back empty in column H.
The sheet has a second tab with region groupings
Join the city list in my Cities tab with the region labels in my Regions tab using the city name as the key. Then fetch AQI from IQAir for each city and write the AQI into column D of the Cities tab alongside the matching region label.
Clean the data, pull the AQI, and flag threshold breaches in one go
Normalize the city spellings in column A, fetch current AQI from IQAir for each row, write the values into columns D through G, and then add a column H flagging any city where US AQI exceeds 150 with the label Unhealthy.
The pattern: ask for normalization and data pull together rather than running them as separate steps.
Try It
Get the 7-day free trial of SheetXAI and open the sheet with your city list, then ask it to populate the AQI, PM2.5, and pollutant columns. When you are done, take a look at the spoke on fetching 72-hour forecasts for venue locations, or the hub overview covering all four methods for connecting IQAir AirVisual to Google Sheets.