How to Connect OpenWeather API to Excel (4 Methods Compared)

The Problem With Getting Workbook Data In and Out of OpenWeather API

You have an Excel workbook full of locations — field sites, distribution centers, event venues, monitoring coordinates. You need weather data, forecasts, or air quality scores for each one pushed into the right columns. OpenWeather has all of it via API. Getting it there is the problem.

OpenWeather is good at returning highly precise, real-time weather and environmental data for any point on the globe. But moving that data into an Excel workbook, row by row across a list of 50 or 300 locations, is a different job entirely. The default approach is: open the API docs, find the endpoint, paste in a coordinate, parse the JSON response, find the fields you want, copy the values into the right cells, and repeat for every row.

Below are the four common ways teams deal with this. Only the last one actually scales.

Method 1: CSV Export and Import

The default Excel workflow usually looks like this: export a list of your locations, run it through whatever weather data tool you have access to — if you have access to any — and import the results back into the workbook as a CSV. If you don't have a batch tool, you're pasting from the OpenWeather sandbox one location at a time.

Either way, the imported data is a snapshot the moment the file was generated. By the time you've merged it into the workbook and distributed it, the conditions may have shifted — which matters when the whole point was to make a logistics decision based on live conditions.

What makes this unsustainable is the refresh problem. Weather is not a one-time enrichment. The operations lead needs fresh data every morning. The ESG report needs historical readings from last quarter. The forecast for next weekend's events changes every 24 hours. Every one of those requests kicks off the same manual sequence again.

Method 2: Power Automate

Power Automate has an HTTP action that can call external REST APIs, including OpenWeather. You can build a flow that triggers on a schedule or a workbook change, calls the appropriate weather endpoint for each location, and writes the response values back into specific cells.

Quick check: are you familiar with configuring REST API calls? Do you know how to handle JSON path expressions to extract fields from a response body? Have you worked with authentication headers before? If any of those are new territory, jump to Method 3 or 4 — Power Automate's learning curve will cost you more than the time it saves.

If you're still reading: the setup is real and the flow works once it's built. You define the HTTP action, authenticate with the OpenWeather API key, pass the right query parameters, parse the response body for the fields you need, and write them to the workbook.

But the trigger-per-row model has a hard ceiling.

For a workbook with 100 property coordinates, you're looking at 100 separate HTTP calls inside a loop — which means 100 separate billable Flow runs depending on your license, and a runtime that grows linearly with your list. When row 58 returns a malformed response because the coordinate was entered in the wrong format, the run fails silently and someone downstream wonders why that cell is empty.

You probably just need the weather enrichment done across the whole sheet. You probably have no idea how to configure an HTTP connector with JSON extraction in Power Automate — and there's no good reason you should. So you submit it as a request to whoever owns automation infrastructure, and you wait for it to land in their queue.

Method 3: The Previous Generation — Connector Add-Ons

Until recently, the most practical option for repeatable Excel ↔ API enrichment was a category of add-ins that let you configure a request template, map response fields to columns, and reuse the configuration without rebuilding it each time. You set the endpoint, tagged the output columns, saved the config, and ran it.

That was a real improvement over manual work. Outputs were consistent. The template was shareable. Non-technical team members could run the saved configuration and get the same result each time.

But the configuration still required you to know which endpoint to call, which parameters to pass, and which JSON fields to extract. The add-in got the data through, but the architecture was still on you. The moment a column was renamed or the coordinate format changed, the config stopped working until someone went back in to repair it.

This is the previous generation. It got the data there. It didn't remove the setup cost.

The Easy Way: Using SheetXAI in Excel

There is a different way entirely. SheetXAI is an AI agent that lives inside your Excel workbook. It reads the workbook, understands what it's looking at — city names in column A, coordinates in B and C, empty weather columns waiting to be filled — and through its built-in OpenWeather API integration, it fetches and writes the data for you. No endpoint configuration. No JSON parsing. No field mapping. You describe the task.

Example 1: Bulk weather enrichment across a location list

For each city name in column A, fetch the current weather from OpenWeather and write the temperature (°C), weather description, wind speed, and humidity into columns B, C, D, and E — flag column F as 'DELAY RISK' for any row where the weather description contains 'storm', 'snow', or 'heavy rain'

SheetXAI calls the OpenWeather API for each city, writes the data fields to the correct columns, and evaluates the description to apply the delay flag. Empty rows are skipped.

Example 2: AQI enrichment from coordinate columns

For each location in this workbook, use the latitude in column B and longitude in column C to fetch the current air pollution data from OpenWeather and write the AQI value and dominant pollutant into columns D and E

The pattern: instead of configuring endpoints and JSON parsing, you describe the columns and the output in plain language. SheetXAI handles every API detail behind the request.

Try It

Get the 7-day free trial of SheetXAI and open any Excel workbook with location data, then ask it to fetch current weather, forecasts, or air quality readings for each row. The OpenWeather API integration is included in every SheetXAI plan.