The Scenario
The sustainability report goes to the executive team in two weeks. Your manager wants a slide showing the air quality ranking for all 20 global office cities — cleanest to most polluted, with current AQI — and they want it sourced from live data, not last quarter's numbers.
You have the city list in a Google Sheet. What you do not have is a workflow for getting live AQI into it without spending an hour on manual lookups.
The bad version:
- Open IQAir for each of the 20 cities, read the AQI, record it in a separate notes doc, paste the values into the sheet, and then sort manually.
- Realize that you pulled the cities in a random order and the sort mixed up the original city groupings that had regional labels in an adjacent column.
- Pull a second time an hour later to verify some values you were not sure about, which now produces two sets of numbers that do not quite match.
The slide needs to show a clean ranked list, and it needs to be defensible as sourced from live data at a specific timestamp. Manual entry across twenty lookups does not give you that.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads your office city list, fetches live AQI from IQAir AirVisual for each location, and sorts the ranked output into a new tab in one step.
Fetch current AQI data for all 20 cities in my Office Locations sheet, then sort them by AQI from lowest to highest and write the ranked list with city name, country, and AQI into a new tab called Air Quality Ranking.
What You Get
- A new tab called Air Quality Ranking with one row per city.
- Column A: city name.
- Column B: country.
- Column C: current US AQI at time of pull, sorted ascending (cleanest first).
- Cities where IQAir returns no data come back at the bottom of the list with a status note rather than silently missing from the ranking.
What If the Data Is Not Quite Ready
Some office cities are listed informally and IQAir does not recognize the name
Column A in my Office Locations sheet has informal city names like Bay Area instead of San Francisco. Resolve each informal name to the nearest IQAir-recognized city, then fetch current AQI and build the ranked list in a new tab called Air Quality Ranking.
You need the ranking split by region
Fetch current AQI for all 20 cities in my Office Locations sheet, then write the ranked list into a new tab called Air Quality Ranking with region in column A, city in column B, country in column C, and AQI in column D, sorted by AQI ascending within each region group.
The sheet has a column of office headcount and you want a weighted ranking
Fetch current AQI for all 20 cities in my Office Locations sheet, write the results into a new tab, and add a column showing the product of AQI multiplied by the headcount from column D of the source sheet, which represents total exposure across each office.
Pull AQI, rank, add category labels, and flag the top three most polluted in one shot
Fetch current AQI for all 20 cities in my Office Locations sheet. Sort by AQI descending in a new tab called Air Quality Ranking. Add a label column: Good for AQI below 51, Moderate for 51 through 100, Unhealthy for Sensitive Groups for 101 through 150, and Unhealthy above 150. Flag the top three most polluted cities in a separate column labeled Escalate.
One prompt for the full ranked and labeled output.
Try It
Get the 7-day free trial of SheetXAI and open the sheet with your office city list, then ask it to pull live AQI and generate the sorted ranking tab. For related workflows, see the spoke on pulling historical AQI trend data, or the hub overview on connecting IQAir AirVisual to Google Sheets.