Worked Example – Weather Map in Google Sheets

This post is a little different to those that have gone before. Instead of presenting a hint or tip about some aspect of Google Drive, today let’s build a little spreadsheet application to pull together some of the techniques I’ve already discussed.

Drawing on some of the posts from recent weeks. I’m going to make a quick-and-dirty temperature map of Europe. Unfortunately, that means there’ll be links ducking and diving to other posts, but don’t worry, they all open in new tabs so you shouldn’t lose this page if you need to follow a link.

Firstly, I want to grab a list of European countries and their capitals. There’s a handy one at http://www.nationsonline.org/oneworld/capitals_europe.htm

Click image to enlarge

Click image to enlarge

We’ll grab the data from this table using IMPORTHTML, as described in this post. A quick look at the page’s source code tells me that I want the fourth table that appears on the page, so here’s my function:

=importhtml("http://www.nationsonline.org/oneworld/capitals_europe.htm","table",4)

Here’s the table successfully imported into my spreadsheet:

Click image to enlarge

Click image to enlarge

To get the temperature information, I’m going to use an online service provided by openweathermap.org. This post explains how to use UrlFetchApp() to access resources from elsewhere on the web.

The service returns weather information in JSON format. For example, the request

http://api.openweathermap.org/data/2.5/weather?q=London

returns the following:

{"coord":{"lon":-0.13,"lat":51.51},"sys":{"type":1,"id":5093,"message":0.0305,"country":"GB","sunrise":1407559044,"sunset":1407612869},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"base":"cmc stations","main":{"temp":289.41,"pressure":1007,"humidity":87,"temp_min":287.04,"temp_max":293.15},"wind":{"speed":5.7,"deg":240},"clouds":{"all":40},"dt":1407570620,"id":2643743,"name":"London","cod":200}

Here’s the same object displayed as a tree diagram:

Click image to enlarge

Click image to enlarge

The property I want to chart is the temp property of the main object. Here’s my code wrapped into a function I’ve called getTempInfo(), which grabs and decodes the current temperature based on the location name:

Click image to enlarge

Click image to enlarge

Notice that the temperatures are returned from the remote service in degreees Kelvin, so the line

return out.main.temp - 273;

not only gets the property I want, but also puts it back to degrees Celsius.

After copying the relevant function into each cell of a free column, here’s my sheet:

Click image to enlarge

Click image to enlarge

Unfortunately, one or two rows return errors, presumably where the external service doesn’t recognise the location name:

Click image to enlarge

Click image to enlarge

To tidy this up, I’ve modified my sheet to wrap my custom function in IFERROR as described in this post.

Click image to enlarge

Click image to enlarge

Now to create my GeoMap chart, as explained in this post. Firstly, entering the ranges for the city names and temperatures:

Click image to enlarge

Click image to enlarge

Finally, customizing my chart with my choice of geographical region, colours etc …

Click image to enlarge

Click image to enlarge

… and finally I have my temperature chart. Each time this sheet is opened or refreshed it’ll generate an updated chart of the temperatures across Europe.

Click image to enlarge

Click image to enlarge

There are more improvements I could make. For instance, those locations returning errors could have their rows removed from the chart data, so they don’t appear on the chart at all; I could allow the user to select other items from the returned data, such as wind speed or humidity; and so on. But hopefully there’s enough here to get you started.

Nice day here in Spain …