Monthly Archives: August 2014

Use Filter Views On View-only Google Sheets

Collaboration is central to Google Drive functionality, so it’s not unusual to have others share spreadsheet data with you. But sometimes you need to filter or sort data on a spreadsheet for which the owner hasn’t given you edit permissions. For example, maybe you’re looking at a spreadsheet that only the owner can edit, but you only want to see items for which you’re responsible. ...read more

Use Week Numbers in Google Sheets with WEEKNUM

If you work in an environment that uses numbered weeks, you might find Google Sheets’ WEEKNUM function useful. Here’s the syntax:

WEEKNUM(date, [type])

date is the date for which you want to find out the week number. This parameter must be a reference to a cell containing a date, a function returning a date type, or a number.
The parameter type is optional (the default is 1) and is a number representing the day on which a week starts. Entering 1 (or omitting the parameter) will start each week on a Sunday, while entering 2 will assume Monday as the first day of each week. ...read more

Basics: Keyboard Shortcuts in Google Drive

There are so many keyboard shortcuts in Google Drive that most people haven’t a hope of memorizing them all. That’s usually OK – you probably only ever use a few of those available, and can remember what they are – but occasionally it’s nice to have a quick reference to the others. ...read more

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

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:

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} ...read more