The Scenario
Your team is building an internal air quality dashboard that pulls from IQAir. You need a reference worksheet listing every IQAir-supported city in India — state and city — so you can find the gaps between IQAir coverage and your own sensor deployment map. The dashboard engineer needs the list by end of day to configure the city dropdown.
You do not know how many cities IQAir supports in India. You do not know the state-level structure. And there is no export button on the IQAir coverage page.
The bad version:
- Call the IQAir API to list states in India, then loop through each state to call the cities endpoint, then flatten the nested results into a two-column table.
- Write the script, run it, discover that the cities endpoint paginates, add pagination handling, run it again, and finally get a flat list after an hour of iteration.
- Compare the flat list against your internal sensor map manually, city by city, because you did not have time to build the join logic.
The dashboard engineer is waiting. You are a data analyst, not a backend developer, and this kind of hierarchical API traversal is not something you should have to build from scratch for a coverage audit.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook that can traverse the IQAir country, state, and city hierarchy for you and write the complete flat list into your workbook in one step.
Build me a full list of every IQAir-supported city in India by fetching all states first, then all cities in each state, and paste the results as a two-column table in my Excel sheet.
What You Get
- Column A: state name for each IQAir-supported location in India.
- Column B: city name within that state.
- One row per city, with the full state-city enumeration from the IQAir hierarchy.
- If any state returns an empty city list, that row is flagged rather than silently omitted.
What If the Data Is Not Quite Ready
You need the coverage list for multiple countries
Get all IQAir-supported states and cities for India, Pakistan, and Bangladesh. Write all results into my Coverage worksheet with Country in column A, State in column B, and City in column C.
You need to cross-reference against an existing sensor map
After pulling all IQAir-supported cities in India into my Coverage worksheet, join the results against the sensor locations in my Sensors worksheet using city name as the key, and add a column labeled IQAir Coverage with Yes or No for each row in the Sensors worksheet.
The dashboard needs a deduplicated city list without the state grouping
Pull all IQAir-supported cities in India, flatten them into a single sorted list with no duplicates, and write just the city names into column A of my City List worksheet.
Fetch coverage, cross-reference the gap, and flag missing cities in one pass
Pull every IQAir-supported city in India into my Coverage worksheet with state in column A and city in column B. Join against my Sensors worksheet where column A has city names. In column C of the Coverage worksheet, write Gap if the IQAir city has no matching row in the Sensors worksheet, and Covered if it does.
The audit and the join in a single instruction.
Try It
Get the 7-day free trial of SheetXAI and open a workbook with a Coverage worksheet, then ask it to pull the full IQAir city hierarchy for your target country and cross-reference it against your sensor list. For related workflows, see the spoke on ranking office cities by live AQI, or the hub overview on connecting IQAir AirVisual to Excel.