API Connector Documentation

Print

Import OpenWeatherMap Data to Google Sheets

Last Updated OnJanuary 9, 2024
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull data from the OpenWeatherMap API directly into Google Sheets, using the API Connector add-on for 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.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your OpenWeatherMap API Key

  1. If you haven't already, sign up for an account at https://home.openweathermap.org/users/sign_up
  2. Once you have an account, click your name to open the account menu and navigate to the API keys section.
    openweathermap-api-img1
  3. 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.
  4. OpenWeatherMap takes some time to recognize newly created API keys, so take a little break before moving to the next section.

Part 2: Pull Data from OpenWeatherMap to Sheets

The easiest way to get started with the OpenWeatherMap API is through API Connector’s built-in integration.

  1. Select OpenWeatherMap from the drop-down list of applications
    openweathermap-application
  2. Choose an endpoint. We’ll start with /geo/1.0/direct to get latitude/longitude coordinates for a location, since OpenWeatherMap needs those for its data requests.
    openweathermap-endpoints
  3. In the parameters section, enter your API key into the appid parameter, and enter your location of interest into the q parameter
  4. Select a destination sheet, name your request, and click Run. Note the values returned under lat and lon.
    openweathermap-response
  5. Plug those lat/lon values into other endpoints to see weather data for your location.
    openweathermap-response2

Part 3: Create a Custom API Request

Alternatively, you can run a custom request instead of using API Connector’s built-in integration, using any of the data points shown in OpenWeatherMap's API documentation. Here is an example setup, just plug in your own API key into the appid parameter.

  • Application: Custom
  • Request method: GET
  • Request URL: https://api.openweathermap.org/data/2.5/forecast?appid=11111&lat=25.0375198&lon=121.5636796

Part 4: API Documentation

Official API documentation: https://openweathermap.org/appid

19 thoughts on “Import OpenWeatherMap Data to Google Sheets”

  1. Hello, thanks for this useful article !
    How do you manage to only pull certain info like I would only want the weather >> main and main >> temp ?

    Thanks

    Reply

  2. Cool, glad it was useful! The response will include all the data returned by OpenWeatherMap. If you're only interested in certain fields, I suggest making another tab in Google Sheets that uses spreadsheet formulas to get what you're looking for. For example, you could use this formula to pull in just columns C and N from a sheet named LondonWeather:

    =QUERY(LondonWeather!A:Z, "select C, N")

    Let me know if that works for you!
    Update: You can now select specific fields using API Connector's visual field editor.

    Reply

  4. Is there a chance to pass a variable date to the API URL?
    e.g. I have a column of dates, and for each date I want the historical weather data.
    How can I pass the fields value (date as unix time) to the API URL?

    Reply

    • Hi Peter, thanks for the message. To get historical weather data from OpenWeatherMap, you would send an API URL like this:
      http://api.openweathermap.org/data/2.5/onecall/timemachine?lat=60.99&lon=30.9&dt=1586468027&appid={API key}

      In API Connector, you can reference a cell instead of hard coding a value, so the URL would actually look something like this:
      http://api.openweathermap.org/data/2.5/onecall/timemachine?lat=60.99&lon=30.9&dt=+++Inputs!A2+++&appid={API key}

      In cell A2, you'd enter a UNIX timestamp so that it populates your URL.

      Reply

      • Thanks Ana,

        works perfect for me!
        I just get weather data which don't go along with the weather I see outside ...but thats not the API connectors fault 😀

        For other who want to copy/paste Ana's code:
        Be sure you choose a date within the last 5 days (free account) and use semicolons as separators in the date function.

      • Thank you for your followup, and for your extra tips, I'm glad that worked for you!

        Btw, the semi-colons vs. commas seems to be a setting in Sheets based on the Locale selected under File > Spreadsheet settings. If the locale is US (like mine), formulas require commas, while European locales seem to require semi-colons. Fun fun 🙂

  5. is it possible to query for data from a specific personal weather station? for example, my neighbor has a PWS and so do i; i want to compare the data i'm collecting specifically with the data he's collecting ... thx

    Reply

  6. Hi Ana,

    Thanks for a great tool and documentation. I'm trying to create a "weather diary" for a trip I'm planning, and I want it to automatically fill in the weather-specific data from the Open Weather Map API. I already have the API working, I just don't know how to organize my data.

    Let me try to explain: For every day in the future (limited to 7 by default), I want the sheet to populate weather data (e.g. max/min temp, description, rain, cloudiness etc.) for specific dates and places (lon/lat), which I have written in separate columns. I'm quite sure I can do this with some VLOOKUP.

    My problem comes with saving the weather data for the days which have passed, as the timemachine call only works for the previous 5 days. Do you have a good idea of how the sheet should only pull historic data once, and then not update the specific date again?

    I've created an example of what I'm trying to accomplish: https://docs.google.com/spreadsheets/d/1I69pi2RtLmmYTdpq4Vrat5YZsXZKcuFjtPJo2UzTtDE/edit?usp=sharing

    Thanks in advance,
    Tom

    Reply

      • Hi Ana,

        I think that could work, but I've run into another issue. I can only get the API's to show for one location (forecast) and one date (historic) data at the time. Do you know if there's a way for the API to pull data based on individual cells?

      • Sorry, I'm not really sure I understand this question, but if you want to base requests on the value in a cell you can do so as described here. If that's not what you meant feel free to reply with more info.

  7. My problem is I list several locations in my sheet, and I want the info to be pulled based on location and date, but the API show "just" one location. Would it be possible for the API to get data for the location per row?

    Reply

      • Sorry, did not see that, and it would have worked perfectly if I was allowed to enter 2 ranges but only one is allowed:

        Request failed: multi-query requests may only include 1 range per line

        I think I'll leave it for now, but thanks for very quick assistance!

      • You're welcome! And for reference, to avoid that error you just need to first create your full list of URLs and then reference that, that way you'll only be referencing one range per line.

Leave a Comment

Jump To