The Scenario
Your manager forwarded a request from the public health team three weeks ago. They want monthly average AQI values for twelve Asian cities over the past twelve months — pre-monsoon and post-monsoon splits so they can document seasonal pollution trends for the grant report. You said you would get to it. The grant submission is next Tuesday.
The bad version:
- Navigate to IQAir's historical data section, select each city individually, export what is available, and figure out which date format the export used this time.
- Manually reshape twelve separate exports into a common format, then pivot them into the one-month-per-column layout the research team wants.
- Realize that three of the cities exported in a different unit scale and spend an extra hour reconciling the values before the data is usable.
The research team hired you to analyze trends, not to chase export formats across a dozen downloads. The deadline is not going to move to accommodate the cleanup work.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet that can reach IQAir AirVisual's historical endpoints and structure the results directly in your sheet — without you managing the export, reshape, or pivot manually.
For each city in my Locations sheet, fetch the past 12 months of historical AQI data from IQAir and write the monthly readings into a new tab named AQI-History, with city in column A and one month per subsequent column.
What You Get
- A new tab called AQI-History with one row per city.
- Column A: city name from your Locations sheet.
- Columns B through M: monthly AQI averages, labeled with the month and year in the header row.
- Cities where historical data is sparse come back with a note rather than an unexplained blank column.
What If the Data Is Not Quite Ready
The Locations tab has inconsistent country entries
Standardize the country names in column C of my Locations sheet to ISO country codes, then fetch the 12-month AQI history from IQAir for each city and write the output into a new AQI-History tab.
You need daily readings instead of monthly averages
Pull the last 6 months of daily AQI readings for the 8 cities in column A of my sheet and paste them into separate columns starting at B, one column per city, on a tab called Historical.
The analysis requires pre- and post-monsoon breakdowns
Fetch the last 12 months of monthly AQI data for each city in my Locations sheet, then in the AQI-History tab add two summary columns: Pre-Monsoon Average (April through June) and Post-Monsoon Average (October through December) computed from the monthly values.
Pull the history, compute the trend, and flag outliers in one pass
For each city in my Locations sheet, fetch 12 months of monthly AQI from IQAir, write the values into AQI-History, compute the month-over-month change in a row below each city, and highlight any month where AQI jumped more than 30 points compared to the previous month.
One prompt handles the fetch, the calculation, and the conditional flag together.
Try It
Get the 7-day free trial of SheetXAI and open the sheet with your location list, then ask it to pull the historical AQI data and lay it out the way the report needs it. For related workflows, see the spoke on ranking office cities by live AQI, or the hub overview on all methods for connecting IQAir AirVisual to Google Sheets.