The Scenario
Third time this week someone on the regional ops team has asked you for a weather summary around the distribution hubs. You're the operations manager. You have 8 hub coordinates in a Google Sheet — hub name in column A, latitude in column B, longitude in column C. The ask is always some version of "what are conditions like near each hub right now" — not for the hub itself, but for the cities and towns within 50 km that your delivery fleet is actually moving through.
The bad version:
- You open the OpenWeather API docs, find the "cities within a circle" endpoint (which takes lat, lon, and a radius in meters, not kilometers), convert 50 km to 50,000 meters, and start calling it manually for each hub.
- The response returns a list of cities with current conditions for each. You have to scroll through the JSON, extract the city names, temperatures, and descriptions you care about, and manually format them into rows in a second tab.
- For hub 3, the response returns 22 cities — more than fit in the columns you set aside. You reformulate how you're writing the data, which means the first two hubs are now formatted differently than hub 3 onward.
- The ops team Slacks you again while you're still on hub 5.
You should be making decisions based on this data, not building the table that holds it.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the hub coordinates, calls the OpenWeather circle-city weather endpoint for each location, and writes the nearby-city results — city name, temperature, description — into a consolidated tab, organized by hub.
For each hub location in this sheet (lat in col B, lon in col C), fetch current weather for cities within 50 km using OpenWeather and write the city name, temperature, and weather description for the top 5 results into a new tab called 'Nearby Cities' — group results by hub name from column A
What You Get
- A 'Nearby Cities' tab with sections grouped by hub name
- For each hub: up to 5 nearby cities with current temperature and weather description
- Results are sorted by distance from the hub coordinate
- Any hub that returns fewer than 5 nearby cities in the radius gets all available results rather than empty rows
What If the Data Is Not Quite Ready
The radius needs to vary by hub — some hubs cover dense urban areas (30 km) and others cover rural zones (75 km)
For each hub in this sheet, fetch current weather for nearby cities using the radius value in column D (in km) from OpenWeather — write city name, temperature, and weather description for the top 5 results per hub into the 'Nearby Cities' tab, grouped by hub name
You want the output structured as one row per hub rather than one row per city
For each hub in this sheet (lat B, lon C), fetch current weather for cities within 50 km from OpenWeather and write a summary into column D on the hub row — format as: 'City1: 14°C clear | City2: 11°C rain | City3: 16°C cloudy' for the top 3 results
Two of the hub coordinates were entered incorrectly and you need them validated before the API call
Check all coordinate pairs in columns B and C for plausible values (latitude between -90 and 90, longitude between -180 and 180) — flag any invalid rows in column D, then fetch nearby city weather from OpenWeather for all valid hub coordinates and write results into the 'Nearby Cities' tab
Validate coordinates, fetch nearby weather, flag severe conditions, and write the ops summary in one shot
Validate all coordinates in columns B and C, skip any rows with invalid values (flag them in column D), then fetch current weather for cities within 50 km of each valid hub from OpenWeather, write city name, temperature, and description for the top 5 results into the 'Nearby Cities' tab, flag any city with storm or heavy rain conditions as 'MONITOR', and add a hub-level summary row noting how many nearby cities are flagged
One prompt handles coordinate validation, the nearby city fetch, the severe-condition flags, and the hub-level summary — the ops team gets a complete picture without a second pass.
Try It
Open your hub coordinates sheet and Get the 7-day free trial of SheetXAI — ask it to pull nearby city weather for all 8 hubs and surface any flagged conditions for your next ops review. You can also explore bulk current weather fetching for named city lists or browse the full OpenWeather API integration hub.