The Scenario
It is 7:45 AM and you have a client delivery at 9. The Excel workbook has sixty global cities across 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, read the AQI off the card, type it into column D, then repeat for PM2.5, pollutant, and temperature.
- Repeat that sequence sixty times while the data keeps changing because AQI is live.
- Deliver a workbook where city 1 and city 60 were pulled ninety minutes apart, making the dataset inconsistent before it even lands with the client.
The cognitive weight of manual data entry at scale is a tax on 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 Excel workbook that reads your location columns and pulls from IQAir AirVisual for you. No API key management, no field mapping, no template to configure.
I have 40 cities in column A of my Excel workbook with states in B and countries in C. Pull the current AQI and PM2.5 for each from AirVisual and fill columns D and E.
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.
- 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 and PM2.5 from IQAir and write the results into columns D and E.
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 F.
The workbook has a second worksheet with region groupings
Join the city list in my Cities worksheet with the region labels in my Regions worksheet using the city name as the key. Then fetch AQI and PM2.5 from IQAir for each city and write the values into columns D and E of the Cities worksheet.
Clean the data, pull the AQI, and flag threshold breaches in one go
Normalize the city spellings in column A, fetch current AQI and PM2.5 from IQAir for each row, write the values into columns D and E, and add column F flagging any city where US AQI exceeds 150 with the label Unhealthy.
The normalization and the pull together, rather than as separate passes.
Try It
Get the 7-day free trial of SheetXAI and open the workbook with your city list, then ask it to populate the AQI and PM2.5 columns for all sixty locations. 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 Excel.