Metaweather API

Metaweather - a free api for weather

Looking to build your own weather app or spreadsheet. We’ll review the metaweather api and alternatives and use syncwith to automatically sync data from metaweather to a google sheet.

Metaweather API down?

If meta weather API is down jump to the section on how to build a weather dashboard without metaweather api. We’ve been seeing metaweather.com going down a lot so don’t be surprised if you see this too.

What We’ll Build - Dashboard to Track Weather on Various Cities

Here’s a screen cap of the what you’ll create below, you can jump to the weather dashboard tutorial

MetaWeather Endpoints

GET /api/location/{woeid}/

This endpoint takes a woeid in the url path to return the weather forecast for today and the next 5 days including min temp, max temp, wind speed, humidity.

What is a WOEID? It’s a unique 32-bit ID that identifies a place / feature on earth. It was originally developed by GeoPlanet and is maintained by Yahoo currently.

How do I get the WOEID? Conveniently the meta weather api also has an end point to search for WOEID based on city name: GET /api/location/search/

Example using the GET /api/location/{woeid}/ endpoint. If we wanted to get weather data for new york we’d use WOEID 2459115 so we’d send a request to

https://www.metaweather.com/api/location/2459115/

Try it now:

GET /api/location/search/

As mentioned above you can use the metaweather api to retrieve a WOEID so if we wanted the ID for chicago we’d send a request to:

https://www.metaweather.com/api/location/search/?query=chicago

The endpoint takes a query parmeter query which we set to the city we want a WOEID for

GET /api/location/{woeid}/{date}/

Finally we have an endpoint that is essentially the same as GET /api/location/{woeid}/ accept there is an additional url parameter {date} - that we provide a date in using the formate yyyy/mm/dd so for example the weather in chicago on christmas 2020 would be requested by sending a the following GET request:

https://www.metaweather.com/api/location/2459115/2020/12/15/

METAWEATHER DOWN? USE OPENWEATHERMAP

Open Weather Map API - Another Free Weather API

Open weather map api is a great free weather simple weather api. That we can use if meta weather is down or not working.

Getting an API Key

While the weather api is free you still need to get a weather API key, you can do this by registering for their free plan:

Goto openweathermap.org/price

Click Get API key under the Free plan

Or alternatively go to https://home.openweathermap.org/users/sign_up

Provide a username, email and password and create your account

Verify your email address

After 20mins - 2hrs you’ll get verification that your api key is available

You can see your list of API keys under the menu option My API Keys

You’re now ready to query the Open Weather Map API

Creating a Weather Dashboard

Weather Data in Google Sheets using SyncWith

We’re going to the SyncWith Addon for Google Sheets (click to install in Google Workspace)

Over 100,000 users syncing their data

⭐️⭐️⭐️⭐️⭐️ with over 470 reviews

How does SyncWith Work?

Video Tutorial

Prefer a video tutorial of how to create the weather dashboard:

Open Weather Map API to Google Sheets - Weather Dashboard

We’re going to create a pivot table to track any weather metric over the next 7 days for a list of cities. To do this we’ll use the endpoint https://api.openweathermap.org/data/2.5/onecall which returns hourly, minute and daily weather data.

This endpoint takes

appid which take an API key for your account

lat and lon as query parmeters representing the latitude and longitude of the area we want data on

units representing the unit of measurement, valid values include:

metric
imperial

We’ll use cell references (eg a reference to a list of latitudes and longitudes so we can have a single connection and a list of cities in our spreadsheet)

The screenshot to the right uses the SyncWith addon to configure the request to the query and populate the data into Google Sheets

Decide What Cities you Want to Track Data on

In our example we use:

london toronto tokyo jakarta delhi shanghai sao paulo

mexico city los angeles new york istanbul paris portland vancouver

We’ve placed those cities as a list in the spreadsheet. But in order to get the forecast data from the endpoint https://api.openweathermap.org/data/2.5/onecall we’ll need to get the latitude and longitude of each city. Fortunately Open Weather Map has an endpoint that take a city as a parameter and includes the lat lon on the response, for this we’ll use https://api.openweathermap.org/data/2.5/weather

/data/2.5/weather endpoint

For our purposes this endpoint takes two relevant parameters (there are other query parameters such as lat, lonzip for identifying cities, but for our purposes they’re not needed.

appid which takes your api key

q which takes the city name, eg london, new york, etc.

We’re going to use cell references which lets us pass multiple values into the connection such that the connection runs once per city name (much more efficient as it lets us have a dynamic list of cities in our spreadsheet and a single connector)

Our SyncWith connection looks like:

You can see that q which takes a city is now taking a range using the sheet current - 7d forecast and the range a3:a16, which we can see in the spreadsheet is the aforementioned list of cities:

Syncwith allows you to select a cell range using the cell reference button:

We really only want the lat / lon back so when we click preview we can expand / delete to ensure we’re getting those values and delete everything else. Your preview screen should look like this after you’ve deleted all the data you don’t need:

And it should update the spreadsheet as follows:

Great so now we have a list of cities and their latitudes and longitudes, and so we can pass those lat / lon values into a different endpoint to get back the current and next 7 days weather data.

For that we’ll use the endpoint: https://api.openweathermap.org/data/2.5/onecall

/data/2.5/onecall endpoint

Search for openweathermap and select it:

Choose the onecall endpoint:

Configure the connector to look like:

appid - again is our api key

lat - is our list of latitudes - again we’re using a cell reference

lon - is our list of longitudes - again we’re using a cell reference

city - this is not a real parameter for the endpoint but it lets us pass in the city name so it gets added to the result - syncwith repeats all cell reference params so you can identify the response based on the input

Now hit preview

There are a lot of arrays passed back in the JSON, if you click view raw data in the preview window you can see all the different arrays:

precipitation data provided by the minute:


"minutely": [
      {
        "dt": 1653491400,
        "precipitation": 0
      },
      {
        "dt": 1653491460,
        "precipitation": 0
      },
      {
        "dt": 1653491520,
        "precipitation": 0
      },
      {
        "dt": 1653491580,
        "precipitation": 0
      },
      {
        "dt": 1653491640,
        "precipitation": 0
      },
      {
        "dt": 1653491700,
        "precipitation": 0
      }
    ]

hourly weather data:


"hourly": [
      {
        "dt": 1653490800,
        "temp": 16.22,
        "feels_like": 16.04,
        "pressure": 1013,
        "humidity": 82,
        "dew_point": 13.15,
        "uvi": 0.97,
        "clouds": 75,
        "visibility": 10000,
        "wind_speed": 5.68,
        "wind_deg": 237,
        "wind_gust": 10.89,
        "weather": [
          {
            "id": 803,
            "main": "Clouds",
            "description": "broken clouds",
            "icon": "04d"
          }
        ],
        "pop": 0.02
      },
      {
        "dt": 1653494400,
        "temp": 16.27,
        "feels_like": 16.04,
        "pressure": 1013,
        "humidity": 80,
        "dew_point": 12.82,
        "uvi": 2.28,
        "clouds": 80,
        "visibility": 10000,
        "wind_speed": 5.52,
        "wind_deg": 250,
        "wind_gust": 10.33,
        "weather": [
          {
            "id": 803,
            "main": "Clouds",
            "description": "broken clouds",
            "icon": "04d"
          }
        ],
        "pop": 0.04
			}
		]

and daily data for the next 7 days and the current day:


"daily": [
      {
        "dt": 1653476400,
        "sunrise": 1653450957,
        "sunset": 1653508754,
        "moonrise": 1653445200,
        "moonset": 1653488820,
        "moon_phase": 0.84,
        "temp": {
          "day": 13.57,
          "min": 8.79,
          "max": 16.53,
          "night": 12.83,
          "eve": 16.53,
          "morn": 9.49
        },
        "feels_like": {
          "day": 13.07,
          "night": 12.41,
          "eve": 16.22,
          "morn": 7.64
        },
        "pressure": 1012,
        "humidity": 80,
        "dew_point": 10.19,
        "wind_speed": 6.27,
        "wind_deg": 232,
        "wind_gust": 12.19,
        "weather": [
          {
            "id": 804,
            "main": "Clouds",
            "description": "overcast clouds",
            "icon": "04d"
          }
        ],
        "clouds": 93,
        "pop": 0.12,
        "uvi": 2.34
      },
      {
        "dt": 1653562800,
        "sunrise": 1653537292,
        "sunset": 1653595232,
        "moonrise": 1653532380,
        "moonset": 1653579660,
        "moon_phase": 0.87,
        "temp": {
          "day": 18.18,
          "min": 10.15,
          "max": 19.4,
          "night": 14.81,
          "eve": 19.4,
          "morn": 10.44
        },
        "feels_like": {
          "day": 17.7,
          "night": 14.51,
          "eve": 18.94,
          "morn": 9.73
        },
        "pressure": 1022,
        "humidity": 63,
        "dew_point": 10.91,
        "wind_speed": 6.34,
        "wind_deg": 251,
        "wind_gust": 10.97,
        "weather": [
          {
            "id": 804,
            "main": "Clouds",
            "description": "overcast clouds",
            "icon": "04d"
          }
        ],
        "clouds": 86,
        "pop": 0.07,
        "uvi": 4.77
      }
	]

We need to expand the JSON (using the expand buttons in the preview window) to expand the daily nested json - ultimately we’ll get 8 rows per city (current day + 7 future days). We don’t want the precipitation by minute or weather by hour (we can delete those.

Keep expanding all the daily data until non of it is nested and then click update.

We now have our daily data which will look something like:

We can now pivot this data using a pivot table, but first we need to fix a small problem, you’ll notice the date column is not an exact date but a date plus time of day:

For that reason we’ll add a helper column called simple date and insert it to the left of the data table, eg:

We’ll use a arrayformula to change all the daily.dt dates to simple dates (no hours) - this is easy we just need to rounddown the daily.dt to remove the hours (which are represented as decimals on the whole number) - in the screenshot below our daily.dt column is K and you can see the arrayformula we use for our simple data column:

Now that we have a proper date we can select the whole table of data and create our pivot table.

You can see our pivot table params here:

Our range starts at row 2 - this is because to get sheets to show nice column names the first row must be where the column names are

We want one row per city

We want one column per date - we use simple date

We want to graph the daily max temperature so we use the column dail.temp.max

We have a filter to get rid of the bogus date generated in our blank rows (we could fix this with a better formula for simple date - but filtering it in the pivot table is just as easy

That’s it now we get our finished pivot table ready to be graphed

Here’s what the pivot table looks like:

and if we graph this data:

That’s it. Remember to watch the video on youtube if you want to see the data being created in real-time and follow along.

Crypto Coin Tracker