You want to build a simple weather forecast in a spreadsheet that allows you to see more data than your regular mobile app? Let’s do it.
Out of many APIs to get weather data, one that is simple to use is OpenWeatherMap. You can simply sign up for a free account to get an API key. It will allow you to get current weather data and a 5-day forecast.
So let’s create a simple forecast app in dashdash! The result can looks like this:
We have to:
- Define the input
- Get the weather data
- Parse the data we want
1. Define the input
C1: “[the city]”
C2: “[the country code]”
C3: “[your API key]”
C4: “[how often you want this updated]”
You can find your API key in your account. The value you enter in C4 is in minutes, e.g. 60 for hourly updates.
2. Get the weather data
Now let’s get the data! We use GET and REPEAT for this.
F1: =REPEAT(1,C4)
A8: =IF(F1=1,GET("api.openweathermap.org/data/2.5/forecast?q="&C1&","&C2&"&APPID="&C3))
Note that I referred parts of the API call to the three input cells we defined. This allows us to quickly get data for other cities in our spreadsheet later. Also note that this API call will be repeated hourly.
The JSON that appears in our cell is structured like this:
3. Parse the data we want
Now we need to use PARSE, which allows us to grab any object of the JSON and organize it neatly in a spreadsheet table. For example, if we want to grab the most relevant forecast information, the formulas looks like this:
B8: =PARSE(A8,“list[0].main.temp”)
C8: =B8-273.15
D8: =PARSE(A8,“list[0].main.humidity”)
E8: =PARSE(A8,“list[0].clouds.all”)
F8: =PARSE(A8,“list[0].wind.speed”)
G8: =PARSE(A8,“list[0].wind.deg”)
Note that in C8, we simply convert the temperature in K to C.
Like this, you can customize the weather forecast app you want, analyze the data with other spreadsheet functions, or send yourself alerts, e.g. by using an SMS API that supports JSON.