Build a simple weather app with OpenWeatherMap!

#1

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

We have to:

  1. Define the input
  2. Get the weather data
  3. 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.

#2

It seems like having REPEAT(GET(…),1) is not sufficient in order for the PARSE(…) functions to also automatically recalculate as the get functions gets refreshed every minute.

Not sure whether this is a bug or whether each PARSE function also requires a REPEAT, in order to represent (automatically recalculate) the latest repeat(get(…),1) data?

Perhaps more generally speaking, what functions require a REPEAT in order to recalculate without manually selecting the cell and pressing enter?

#3

@Torben perhaps you can help me with better understanding the above situation :). Thank you.

#4

Thanks @dknodt!

You should require only one REPEAT function. All dependents on a cell with a REPEAT will be automatically triggered to recalculate.

If this is not what you see in the PARSE functions that depend on the REPEAT(GET), it might be a bug. Can you describe me the behavior in more detail, please?

Thanks!

#5

Do the different lists in the JSON (0,1,2,3,…) refer to different 3-hour time segments?

Do you know a good way to convert the unix time stamps to local time?

#6

Hi Fabian,

Yes, the different lines in the “list” array are the forecasts in 3h increments.

Please try =A1/86400+DATE(1970,1,1) for the conversion, assuming that the UNIX timestamp is in cell A1.