On Monday I’ll be helping Tony (Hirst) deliver a workshop at Internet Librarian International … W2 – Appropriating IT: innovative uses of emerging technologies. Tony has already posted a draft outline of the day. For the event I wanted to prepare some notes on using Google Spreadsheets/Sheets (I very confused about what they are called now. There was a very quiet announcement here of a change).

I’ve come up with some practical exercises for participants to try. There will be a lot of contextualisation going on as part of the workshop, the main idea is to seed some ideas of what’s possible. Here’s the Google Docs version of the guide. The focus is on what you can do with the importHTML, importFeed, importXML, importRange and importData spreadsheet formula with a few others, including QUERY, thrown into the mix. Using QUERY isn’t straight forward but it opens up lots of possibilities in how you reshape data (extra examples of these are included in the Google Docs version).

Note: All text and images with the exception of text in grey boxes is licensed Creative Commons Attribution 3.0 Unported License. CC-BY Hirst & Hawksey (if this isn’t clear check the source or get in touch)

importHtml

Exercise 1: Importing a html table and graphing the result

1. Create a new Google Spreadsheet
2. In cell A1 enter the formula to import a table from Wikipedia=importHTML("http://en.wikipedia.org/wiki/2012_Summer_Olympics_medal_table","table",3)
3. Select Insert > Chart and then select the data ranges for country name and total medals
   
4. While still in the Chart Editor select the Charts tab, then Maps > Geo charts - regions
5. Still in the Chart Editor select Customise and change the No value colour to blank.
6. Finally click Insert

You should now have a chart that looks like this:

Notice that the chart has No values (black) for most of the countries.  To fix this we need to remove the country codes in brackets. One way to do this is trim the text from the left until the first bracket “(“. This can be done using a combination of the LEFT, FIND and ARRAYFORMULA (ARRAYFORMULA allows you to apply formulas to multiple cells).

1. In cell H2 enter the formula =ARRAYFORMULA(LEFT(B2:B86,FIND("(",B2:B86)-2)) this should create a column of country names without brackets)
2. Click on your Chart and select Advanced edit.
3. Make sure you are on the Start tab in the Chart Editor and edit the data range forSheet1!B2:B86 to Sheet1!H2:H86 then click Update

Your chart should now look like this (my solution http://goo.gl/8qUI9):

importFeed

Exercise 2: Importing a RSS feed and getting social share counts

1. Open http://goo.gl/aai2p
2. In cell B5 enter the RSS feed url for a blog (or you can use http://feeds.feedburner.com/MASHe) and hit enter

You should end up with something like:

An aside: Spreadsheet Addiction by Patrick Burns (http://goo.gl/P6pQP) - highlights the dangers of using spreadsheets for analytics. Particular issues include the ambiguity of a cell being a value or a formula. For example, if I sort cells on the value in the Twitter count column all the data is lost because cells are sorted as values but actually contain formula which get broken.

How it works

In cell A11 is the formula =IF(ISBLANK(B5),,IMPORTFEED(B5,"items",FALSE)) If the feed url is not blank this fetches the RSS feed defined in B5. Results are returned in cells A11:E30. You may have noticed that column E is hidden this is because it contains the feed item description.

The social share counts are returned by a custom function written in Google Apps Script (https://script.google.com). Google Apps Script is similar to Excel Macros, written using a JavaScript syntax. If you open Tools > Script editor in your spreadsheet you can see some of the custom script powering the spreadsheet. This includes the getSharedCount formula used in cells F11:F30 which passes the post url to the SharedCount.com API and returns social share counts. The code used is:

function getSharedCount(sourceLink){
//var url = "https://mashe.hawksey.info/2012/02/oer-visualisation-project-fin-day-40-5/"
 var url = extractLink(sourceLink);
 var cache = CacheService.getPublicCache(); // using Cache service to prevent too many urlfetch
 var cached = cache.get("C"+url);
 if (cached != null) { // if value in cache return it
   //var test = cached.split(",")
   return cached.split(",");
 }
 try {
   var options =
   {
     "method" : "get",
     "contentType" : "application/json"
   };
   var response = UrlFetchApp.fetch("http://api.sharedcount.com/?url="+encodeURI(url), options);
   var data = Utilities.jsonParse(response.getContentText());
   var output = [];
   for (i in data){
     if (i == "Facebook"){
       output.push(data[i].total_count)
     } else {
       output.push(data[i]);
     }
   }
   cache.put("C"+url, output, 86400); // cache set for 1 day
   return output;
 } catch(e){
   Logger.log(e);
 }
}

For more examples of Google App Script see http://scoop.it/t/gas.

importXML

Exercise 3: Turn a page of RSS Feeds into an OPML file

1. Create a new spreadsheet
2. In cell A1 enter the text ‘Title’ and in cell B2 ‘Url’
3. Now in cell A2 enter the formula=ImportXML("http://edfuture.mooc.ca/feeds.htm","//b/a")
4. Cell B2=ImportXML("http://edfuture.mooc.ca/feeds.htm","//a[.='XML']/@href")
5. File > Publish to the web and click ‘Start publishing’, copy the link in the bottom box then ‘Close’
6. Visit http://opml-generator.appspot.com/ and paste your spreadsheet link in the box and copy the generated link into your broswer address bar

How it works

Using XPath we can identify parts of a XML (including HTML) page we want to extract. The screenshow below shows how parts of the page are identified. [I always struggle with XPath so use browser extensions to help (Scraper and XPath Helper)]. The results are pulled into the spreadsheet as live data so if the source page is updated the data in the spreadsheet will also be updated.

ImportRange

[Here’s http://goo.gl/b8FXC is a copy of the completed spreadsheet used in exercises 4, 5 and 6]

Exercise 4: Importing data from Guardian Datastore

1. Visit http://goo.gl/j6RBU and click Get the Data, then DATA: download the full spreadsheet
2. Keep this window open and create a new spreadsheet.
3. In cell A1 enter=ImportRange("0AonYZs4MzlZbdFdrRGthS3dLVzlBdWVrV2lIbzZKY0E","Times Higher Education rankings 2012!A1:D104") - notice how the key and range are entered

Lets now reshape the data so we can generate some graphs. Lets first calculate the change in rank between 2011 and 2012

1. In cell E2 enter the formula =B2-A2
2. Fill this formula for the rest of the rows (there are a couple of ways of doing this including copying cell E2, highlighting the other empty cells in Column E and pasting, or whilst E2 is active grab and drag the bottom right corner of the cell
3. Now we want to get a list of the countries included in column D. To do this in cell G2 enter the formula =UNIQUE(D2:D102)
4. Now we want to sum the rank difference per country by entering the following formula in cell H2:=SUMIF(D$2:D$102,G2,E$2:E$102)
5. Copy this value down for the remaining rows
6. Select the data range G2:H16 and Insert > Chart > Bar chart

Graph of change in top 100 world university ranking.

Is the process for producing this chart valid? Is it displaying a meaningful representation of the data? Is this chart a lie?

Important: Notice that the calculation for France has an error:

This is because on row 93 the source data doesn’t have a number value. Because we’ve used ImportRange to get the data we can’t edit it as are changes get overwritten by the importRange formula in cell A1. In our scenario we can remove the calculated value in E93 or use a formula to handle the error. Other ways around this are to flatten the imported data by copying all of it and paste as values (other solutions exist which we cover later) 

ImportData

Exercise 5: Importing CSV data from Google Maps

1. In the spreadsheet you created for exercise 4 Insert > New Sheet
2. In cell A1 of the new sheet enter the formula =FILTER(Sheet1!A:E,Sheet1!D:D="United Kingdom") to filter the data on sheet1 where column D is the United Kingdom
3. Now in column F1 enter the formula =ImportData("http://maps.google.com/maps/geo?output=csv&q="&C2) and press return
4. Copy this cell down for the remaining rows

You should now have a table that looks a little like this:

Import... and QUERY

Exercise 6: Import and QUERY

1. In the spreadsheet you created for exercise 4 Insert > New Sheet
2. Form your original sheet (Sheet1) copy the importRange formula in cell A1
3. In your new sheet (Sheet3) paste the formula you just copied inside a QUERY formula shown below
   
   =QUERY(ImportRange("0AonYZs4MzlZbdFdrRGthS3dLVzlBdWVrV2lIbzZKY0E","Times Higher Education rankings 2012!A1:D104"), "SELECT Col1, Col2, Col3, Col4, Col2-Col1 LABEL Col2-Col1 'Difference'")

You should now have a table that looks like this:

How it works

The QUERY function imports the data and using the Google query language selects columns 1 to 4 and also adds a fifth by taking the difference between Col2 and Col1, this new column is labeled as Difference. Notice that on row 93 the French entry no longer has an error, but is blank.

We could continue exercise 4 and get a summary chart using UNIQUE and SUMIF. An alternative would be to use the QUERY formula again to do this for us by:

1. In Sheet 3 enter the following formula in cell G1 =QUERY(A:E,"SELECT D, SUM(E) WHERE D <> '' GROUP BY D ORDER BY D")

This time we are setting the data source as all the data in columns A to E in Sheet3. Next we are creating a query that selects column D and a sum of column E where D is no blank and grouped by the value in column D (the country names).

A reminder that here’s a copy of the completed spreadsheet used in exercises 4, 5 and 6 http://goo.gl/b8FXC

Summary/Keypoints

Hopefully you’ve seen that Google Sheets (Spreadsheets) can be a useful tool for importing data from other sources and reshaping it to fit your needs. The last set of exercises are more advanced so don’t worry if you don’t fully understand what is happening, they are there as an indication of what is possible and hopefully inspire you to find out more.

Three points worth remembering when using import formula:

• The data is a live link - if the data source changes or disappears  your imported data will also change or disappear (this can be both an advantage and disadvantage).
• The ambiguity of a cell - because a spreadsheet cell can a value and a formula sorting values generated by formulas can lead to unforeseen problems.

• There are other ways of getting data into Google Sheets - this guide has only looked at ‘Import...’ formula for getting pulling data in. There are other ways of populating sheets using Google Forms, Google Apps Script and 3rd party services like IFTTT (for an example of this last one see ‘IFTTT: IF I do THAT on {insert social network/rss feed/other} THEN add row to Google Spreadsheet - http://goo.gl/nB0vD)

A reminder that the Google Docs version of the guide is here and contains more examples. If you get stuck leave a comment.