The Scenario
The quarterly safety compliance report is due at the end of the week. You manage logistics across 30 port cities split between the US and China, and the report requires both US EPA AQI and China MEP AQI for each node — plus wind speed and humidity because the maritime safety team added those requirements in January. Someone built the table last quarter with manual data entry. That person left in March. The table is yours now.
The bad version:
- Look up each port city in IQAir's interface, which shows US AQI by default but requires toggling to find the CN AQI, which is not always shown for non-Chinese cities.
- For wind speed and humidity, navigate to a separate weather layer in the interface and manually record the values while keeping track of which city you are on.
- Build a 30-row table by hand and then realize the report template requires CN AQI in a different unit than what IQAir displays, adding a conversion step at the end.
There is a compliance deadline attached to this. Manual entry that takes three hours and introduces transcription errors is not a defensible process for a safety report.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads your port city list and pulls from IQAir AirVisual in a single pass — US AQI, China AQI, wind speed, and humidity all at once.
For each port city in my Supply Chain sheet, fetch the current air quality from IQAir and write the US AQI, China AQI, wind speed, and humidity into columns D, E, F, G.
What You Get
- Column D: current US EPA AQI for each port city.
- Column E: current China MEP AQI for the same cities (returns N/A for cities outside China's monitoring network).
- Column F: wind speed at the monitoring station nearest to each city.
- Column G: relative humidity.
- Cities where IQAir returns partial data come back with a status note rather than unexplained gaps in the compliance table.
What If the Data Is Not Quite Ready
Some city names are listed under their port name rather than their administrative city
Column A in my Supply Chain sheet uses port names like Port of Tianjin rather than administrative city names. Map each port name to its nearest city using column B which has the country, then fetch US AQI, CN AQI, wind speed, and humidity from IQAir and write into columns D through G.
The CN AQI values need to be in a different index format for the report template
Fetch US AQI and China AQI from IQAir for each city in my Supply Chain sheet, write the raw values into columns D and E, and then in column F compute the China MEP index level label (Good, Moderate, Unhealthy for Sensitive Groups, etc.) from the CN AQI value.
Some port cities appear in multiple regional tabs
My workbook has a US Ports tab and an Asia Ports tab with overlapping column structures. For all cities across both tabs, fetch US AQI, CN AQI, wind speed, and humidity from IQAir, and write the results into the corresponding columns in each tab without mixing data between them.
Pull all four metrics, apply compliance thresholds, and generate the status column in one step
For each port city in my Supply Chain sheet, fetch US AQI, China AQI, wind speed, and humidity from IQAir, write the values into columns D through G, and then in column H add a Compliance Status: Green if US AQI is below 101, Yellow if 101 through 150, and Red if above 150.
One prompt handles the full data pull and the conditional classification.
Try It
Get the 7-day free trial of SheetXAI and open the supply chain sheet with your port city list, then ask it to populate all four metrics and apply your compliance thresholds. For related workflows, see the spoke on enriching GPS coordinates with nearest-station AQI, or the hub overview on connecting IQAir AirVisual to Google Sheets.