The Scenario
The study protocol review is next month. You're on a dermatology research team, and part of the patient outcome correlation study requires historical UV index readings at the home city of each participant. You have 20 cities in a Google Sheet — city name in column A, latitude in column B, longitude in column C. The date range is the past 60 days. That's 20 cities × 60 days = 1,200 individual data points that need to land in the sheet before the analysis can begin.
The bad version:
- You locate the OpenWeather UV Index Historical endpoint, note that it requires Unix timestamps for the start and end dates, convert the dates using an online tool, and start calling the API manually — one city at a time, copying coordinates from the sheet, constructing the URL, sending the request, and receiving a response that returns one reading per measurement interval rather than one per day.
- You realize you need to aggregate the readings into daily values. You download the JSON response for the first city into a text editor, manually pick out the highest UV reading for each day, type 60 values into the sheet, and move to city 2.
- City 2 has a different time zone than city 1. The timestamp alignment is off. You've now spent 40 minutes on two of the twenty cities.
The analysis depends on this data. Right now, the data collection is the bottleneck.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the city coordinates, calls the OpenWeather historical UV index endpoint for each location over the date range you specify, aggregates the readings into daily values, and writes them — either across a row or into per-city tabs — exactly as you describe.
Pull UV index history from OpenWeather for all coordinate pairs in columns B and C over the date range in cells G1 and G2, then write a summary into the 'UV Summary' sheet showing average and peak UV per location
What You Get
- A 'UV Summary' tab (or updated existing tab) with one row per city
- Columns: city name (from column A), average UV index over the 60-day range, and peak UV value recorded
- For the per-city tab variant: daily UV readings in a separate tab named after each city
- Any days with missing readings in the API response are noted rather than silently averaged out
What If the Data Is Not Quite Ready
The date range in G1 and G2 is in plain English ("60 days ago" and "today") rather than ISO dates
Convert the date descriptions in G1 and G2 to ISO format dates, then fetch historical UV index data from OpenWeather for all coordinate pairs in columns B and C over that range and write average and peak UV per location into the 'UV Summary' tab
You want the full day-by-day readings, not just the summary, written into per-city tabs
For each location in this sheet (lat in col B, lon in col C), fetch historical UV index data for the past 60 days from OpenWeather and write the daily UV index values into a new tab named after the city in column A — one row per day, columns: date and UV index
The city list includes international locations across multiple time zones, and the aggregation needs to account for local solar noon
For each city in this sheet, fetch 60 days of historical UV index data from OpenWeather using coordinates in columns B and C, adjust daily peak values to local solar noon for each location's time zone, and write daily peak UV into a tab named after each city
Fetch the UV history, compute the correlation-ready statistics, and flag any city with unusually high average UV in one pass
Pull historical UV index data from OpenWeather for all 20 cities using coordinates from columns B and C, covering the date range in G1:G2, write average and peak UV per city into the 'UV Summary' tab, add a standard deviation column for each city's readings, and flag any city with an average UV above 8 as 'HIGH EXPOSURE SITE'
Ask for the data pull, the statistical summaries, and the classification in one prompt — the analysis-ready dataset lands in the sheet without intermediate steps.
Try It
Open your research location sheet and Get the 7-day free trial of SheetXAI — ask it to pull 60 days of UV readings for all 20 cities and write the summary statistics before your next protocol review. You can also explore current UV index fetching by coordinates or browse the full OpenWeather API integration hub.