The Scenario
Your team is building an internal air quality dashboard that pulls from IQAir. You need a reference sheet listing every IQAir-supported city in Germany — 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 Germany. 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 Germany, 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, go back and 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 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 Google Sheet that can traverse the IQAir country, state, and city hierarchy for you and write the complete flat list into your sheet in one step.
Get all supported states in Germany from IQAir, then for each state fetch the list of supported cities, and write the complete state-city pairs into my Coverage sheet with State in column A and City in column B.
What You Get
- Column A: state name for each IQAir-supported location in Germany.
- 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 Germany, France, and the Netherlands. Write all results into my Coverage sheet 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 Germany into my Coverage sheet, join the results against the sensor locations in my Sensors tab using city name as the key, and add a column D labeled IQAir Coverage with Yes or No for each row in the Sensors tab.
The dashboard needs a deduplicated city list without the state grouping
Pull all IQAir-supported cities in Germany, flatten them into a single sorted list with no duplicates, and write just the city names into column A of my City List sheet.
Fetch coverage, cross-reference the gap, and flag missing cities in one pass
Pull every IQAir-supported city in Germany into my Coverage sheet with state in column A and city in column B. Then join against my Sensors tab where column A has city names. In column C of the Coverage sheet, write Gap if the IQAir city has no matching row in the Sensors tab, 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 sheet named Coverage, 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 Google Sheets.