Import OpenWeatherMap Data to Google Sheets
We’ll first get an API key from OpenWeatherMap, and then set up a request to pull in weather forecast details from OpenWeatherMap to your spreadsheet.
- Before You Begin
- Part 1: Get your OpenWeatherMap API Key
- Part 2: Create your API Request URL
- Part 3: Pull OpenWeatherMap API Data into Sheets
- Part 4: Get More OpenWeatherMap Data
- Part 5: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your OpenWeatherMap API Key
- If you haven’t already, sign up for an account at https://home.openweathermap.org/users/sign_up
- Once you have an account, click your name to open the account menu and navigate to the API keys section.
- There should be a default API key already created for you (if not, you can click the ‘Generate’ button to make a new one). Keep it handy as we’ll need it shortly. That’s it! You now have access to OpenWeatherMap. Just note that OpenWeatherMap takes 15-20 minutes to recognize newly created API keys, so take a little break before moving to the next section.
Part 2: Create Your API Request URL
We’ll first access current weather data for a specific location.
- API root: http://api.openweathermap.org/data/2.5
- Endpoint: /weather
- Parameters: q=CITY_NAME,COUNTRY_CODE&APPID=YOUR_API_KEY
Putting it all together, we get the full API Request URL:
Just substitute in your own API key and location of interest.
Part 3: Pull OpenWeatherMap API Data into Sheets
Now let’s copy that URL into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create screen, enter the Request URL we just created
- Leave the headers section blank as we don’t need any headers for this request. We don’t need authentication either so just skip that section.
- Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see weather data populate the OpenWeatherMap tab in your Google Sheet:
Part 4: Get More OpenWeatherMap Data
- When querying weather data from OpenWeatherMap, you can identify locations by either city name (
q=London), city name + country code (
q=London,uk), city ID (
id=2172797), geographic coordinates (
lat=35&lon=139), or zip code (
zip=94040,us). If you’re interested in querying results for a specific city, use the city ID to avoid ambiguous results.
- Experiment with endpoints and query strings as described in the documentation to see other types of weather data. For example, you’d get a 5-day forecast for Moscow by entering the following:
(Tip: When you run this query, note that the
list.dt_txtfield contains the date and time stamp for the forecast).
- You can query the following data collections for free: Current weather data, 5 day / 3 hour forecast, and Weather stations. There is also a One Call API that offers 1000 free API calls a day. Free accounts have limited service availability; if you query too rapidly you may receive the following error message: “Your account is temporary blocked due to exceeding of requests limitation of your subscription type.” In those cases, stop and wait for 10 minutes before repeating your request.
Part 5: API Documentation
Official API documentation: https://openweathermap.org/appid