The Scenario
The regulatory submission deadline is next Tuesday. You're an environmental consultant, and your client has 15 air quality monitoring sites. Each site's coordinates are in a Google Sheet — site name in column A, latitude in column B, longitude in column C. You need historical daily AQI values for each site covering the past 30 days. The submission requires a trend table, not a snapshot.
The bad version:
- You find the OpenWeather Historical Air Pollution endpoint, which requires Unix timestamps for the start and end date parameters. You calculate today's timestamp and 30 days ago, format them correctly, and start calling the endpoint one site at a time — copying coordinates from the sheet, constructing the URL, sending the request, and parsing a response that returns 3-hourly data you then need to manually aggregate into daily averages.
- Site 3 returns 240 rows of 3-hourly readings. You average them in your head, write down one number, and move on. Site 7 has a gap in the data — two days are missing — and you have to decide how to handle that before you can populate the table.
- After three sites, the Tuesday deadline is starting to feel close.
Fifteen sites × 30 days × manual aggregation is not a consulting engagement — it's a data entry job that should not exist.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the site coordinates, calls the OpenWeather Historical Air Pollution API for each site over the date range you specify, aggregates the 3-hourly readings into daily averages, and writes the trend table — either across columns or into a dedicated tab.
Pull historical air quality data for all coordinate pairs in columns B and C covering the date range in cells F1:F2, and write the results into the 'AQI History' tab with columns: site name, date, AQI, main pollutant
What You Get
- A new or updated 'AQI History' tab with one row per site per day covering the 30-day range
- Columns: site name (pulled from column A), date, daily average AQI, and dominant pollutant for that day
- Days with data gaps are noted in the pollutant column so you can flag them in the submission narrative
- The table is structured for direct inclusion in the regulatory report without reformatting
What If the Data Is Not Quite Ready
The date range in F1:F2 is formatted inconsistently — one is MM/DD/YYYY and the other is YYYY-MM-DD
Normalize the date values in F1 and F2 to ISO format (YYYY-MM-DD), then pull historical AQI data from OpenWeather for all site coordinates in columns B and C and write daily average AQI and dominant pollutant into the 'AQI History' tab
Some sites have coordinates with insufficient precision for reliable API results
Check all coordinate pairs in columns B and C for decimal precision — flag any with fewer than 4 decimal places in column D, then pull historical air quality data from OpenWeather for the remaining sites and write the daily AQI trend into the 'AQI History' tab
The client wants a separate tab per site, not a combined table
For each site in this sheet (site name in col A, lat in col B, lon in col C), pull 30 days of historical AQI data from OpenWeather and write the daily readings into a new tab named after the site — columns: date, AQI, main pollutant
Fetch the trend data, calculate 7-day rolling averages, and flag any days above the compliance threshold in one pass
Pull historical air quality data from OpenWeather for all 15 sites using coordinates in columns B and C, covering the past 30 days, write daily AQI values into the 'AQI History' tab, add a column with the 7-day rolling average for each site, and flag any day where AQI exceeds 100 with 'EXCEEDS THRESHOLD'
The fetch, the aggregation, and the compliance flagging land in one operation — the table is submission-ready when it's done.
Try It
Open your monitoring site sheet and Get the 7-day free trial of SheetXAI — ask it to build the 30-day AQI trend table for all 15 sites before your next regulatory deadline. You can also explore AQI enrichment for property lists or browse the complete OpenWeather API integration hub.