Import OpenWeatherMap Data to Google Sheets

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.

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. 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 OPENWEATHERMAP API REQUEST URL

We’ll follow the OpenWeatherMap API documentation to access current weather data for a specific location. OpenWeatherMap has both free and paid plans, but for the purposes of this example we’ll stick to the free data.

  • 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:

http://api.openweathermap.org/data/2.5/weather?q=London,uk&APPID=01...

Just substitute in your own API key and location of interest.

PART 3: PULL OPENWEATHERMAP API DATA INTO SHEETS

We can now enter all our values into API Connector and start importing OpenWeatherMap data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create screen, enter the Request URL we just created
    openweathermap-api-img2
  3. 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.
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see weather data populate the OpenWeatherMap tab in your Google Sheet:openweathermap-api-img3

PART 4: GET MORE OPENWEATHERMAP DATA

  1. 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.
  2. 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:
    http://api.openweathermap.org/data/2.5/forecast?id=524901&APPID=YOUR_API_KEY

    .
    openweathermap-api-img4
    (Tip: When you run this query, note that the list » dt_txt field contains the date and time stamp for the forecast).

  3. You can query the following data collections for free: Current weather data, 5 day / 3 hour forecast, and Weather stations. There is also a new 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.

Following the above steps, you will now be able to import data from the OpenWeatherMap API directly into Google Sheets.

9 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!

    Reply
  3. 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 the UNIX timestamp so that it would populate your URL. Note: in Sheets, you can convert a regular date to UNIX timestamp with the formula =(A1-DATE(1970,1,1))*86400, assuming A1 contains your date.

      Hope that helps point you in the right direction, let me know if I can clarify anything further.

      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 🙂

Leave a Comment