Import OpenWeatherMap Data to Google Sheets – Step by Step Guide

Import OpenWeatherMap Data to Google Sheets – Step by Step Guide

Last Updated On June 08, 2019
You are here:
< Back

In this guide, we’ll walk through how to pull data from the OpenWeatherMap API directly into Google Sheets, using the free API Connector add-on. 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. That’s it! You now have access to OpenWeatherMap. (Though please note that OpenWeatherMap takes 15-20 minutes to recognize newly created API keys)

PART 2: CREATE YOUR API REQUEST

We’re going to 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 and country code}&APPID={API key}
    Parameters Example: q=London,uk&APPID=efd8f78f9b08925ea781a393bb8d5306

Putting it all together, we get the full API Request URL:
http://api.openweathermap.org/data/2.5/weather?q=London,uk&APPID=efd8f78f9b08925ea781a393bb8d5306
(Of course, you’ll need to substitute in your own API key and location of interest. The API key in the example has already been deleted).

We’re now ready to enter all our values into API Connector to start importing OpenWeatherMap data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, 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.
  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘OpenWeatherMap’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘OpenWeatherMap’
  6. Click Run and a moment later you’ll see weather data populate the OpenWeatherMap tab in your Google Sheet:
    openweathermap-api-img3
  7. 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 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).

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

Comments:3

  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

  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 N and E:

    =QUERY(OpenWeatherMap!A:Z, “select N, E”)

    Let me know if that works for you!

Leave a Reply

Your email address will not be published.