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 aWOEID
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:
- Click
Get API
key under theFree 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?
.jpg?id=36b50aa2-1c48-4cb6-9010-c2b02437b297&table=block&spaceId=66f19bc9-26ac-47b9-9d5c-6b510ac6d351&expirationTimestamp=1701482400000&signature=oTbvzDYxiqHAJNssNuwDl2pNLNrTn1YZlPkr2A8aB3I)
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
andlon
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
, lon
zip
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.