Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe
Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe

There was a time when you could enable Google Analytics tracking in what was Google Docs and is now Google Drive. Sadly the feature was removed and Google now recommend “embed them in your web pages, and then use Analytics to track the pages in which they're embedded”. For someone who has a number of Google Sheet templates this isn’t entirely convenient and workable. I can embed a link to a template in a site and attach an event to track the number of times clicked, but given these templates can easily be copied and recopied there’s no way to monitor use.

Part of the problem is that the Google Analytics predominantly relies on some embedded JavaScript to communicate when a page has been viewed.  Given the increasing range of interactions Google Analytics also provides a Measurement Protocol for developers to send tracking data in other ways using a HTTP POST or GET request. Without going too deep into the technical side this actual opens a way for including tracking in Google Documents, Sheets and Forms by using Google Apps Script.

Apps Script includes both triggers like onOpen and a URL Fetch service which would allow you to send data to the Measurement Protocol (and this little gist gives you the code to do it). Before you go implementing this in all your projects there are two issues to be aware of:

  1. URL Fetch calls are quotaed by fetches per day and overall runtime (current Google Apps Script Quotas).
  2. URLFetch Service requires authorisation before it can run. This means it cannot send data unless the user has given permission. So if you are viewing a Sheet template Google Analytics will only be pinged after you’ve File > Make a copy and authorised it.

Authorization for Google Services

Throwing up a beacon instead

All is not lost. Recently I came across the Google Analytics Beacon:

Sometimes it is impossible to embed the JavaScript tracking code provided by Google Analytics: the host page does not allow arbitrary JavaScript, and there is no Google Analytics integration. However, not all is lost! If you can embed a simple image (pixel tracker), then you can beacon data to Google Analytics.

This project by Googler Ilya Grigorik means if you can embed an image a Google App Engine service has been configured to make a hit against the Measurement Protocol for you. For this to work when you view the page the image needs to be served from the App Engine service. There are also limitations to this approach in that visitor and referral data is lost.

In Google Drive it’s easy for us to Insert > Image in various applications including Documents and Presentations and even specify these as ‘by URL’. Unfortunately these applications also create copies of the inserted image rather than using the image specified by URL. An anomaly to this is Google Sheets. Sheets permits Insert > Image and a cell function IMAGE. In both these cases the image is served from the URL you specify meaning we can track Google Sheets*.

*New Sheets appears to serve Insert > Image in the same way as Documents and Presentations but the IMAGE formula method outlined below still works.

Using Insert > Image

Using the GA Beacon Setup Instructions will walk you through creating a Google Analytics account and making an image URL like


Remember to use your own tracking ID. The ‘sheets’ and ‘UNIQUE_ID’ can also be whatever you like.

Using Insert > Image and selecting ‘By URL’ you can add a GA Beacon to a sheet (you can check you are collecting data by logging into Google Analytics and looking at Real-Time reporting). Now every time the Sheet is opened and the image is viewable the visit will be counted in Google Analytics. The url for the image is fixed so even if a copy is made of the spreadsheet as long as the image isn’t deleted you will get tracking data. Remember this way won’t work for New Sheets but the next method does and in my opinion is better.

Using IMAGE formula better tracking information

Using the IMAGE formula would work in exactly the same way using the same image url as above. There is something else we can do. Because it’s a formula the image url could have an identifier that is in some way calculated. As Google Apps Script permits creating your own custom formula there is even scope to use this as part of the calculation.  For example, using little Apps Script will include the sheet key and locale in the image url (to include this you your own project open you Sheet and then Tools > Script editor and paste the code in):

function getGABeacon(tid){
  var id = SpreadsheetApp.getActiveSpreadsheet().getId();
  var locale = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale();
  return 'https://ga-beacon.appspot.com/'+tid+'/sheets/'+id+'/'+locale;

In the Sheet we can then use the cell formula like =image(getGABeacon("UA-48225260-1"))

Image with GA Beacon

You can see this better in-situ in this Google Sheet which you are free to File > Make a copy to see how it works. As an added bonus the Apps Script methods used in this example don’t require authorisation so tracking data (limited to view count) is recorded for anyone opening the Sheet.

Note: GA Beacon recently defaulted to using a SVG image which is not compatible with the old version of Google Sheets. The tracking still appears to work but a #N/A error is returned. To get around this I requested an option to add ?gif to the beacon url is added. To use this you can add +'?gif' to the script or use the formula =image(getGABeacon("UA-48225260-1")&"?gif"). Also remember new Sheets has heavy formula caching so if using this technique for tracking templates you should add a reference to a cell you know will be edited to get the actual spreadsheet ID e.g. =image(getGABeacon("UA-48225260-1",A1))

This is what the result looks like in Google Analytics Real-Time.

Google Analytics Real-Time

So there you go I can now track views of my Google Sheets by including an image in a cell!

1 Comment

Flickr Tag Error: Call to display photo '4333665594' failed.

Error state follows:

  • stat: fail
  • code: 95
  • message: SSL is required
I recently approached Mozilla Foundation chair, Mitchell Baker. There were numerous reasons for approaching Mitchell some of which might be clearer in the future. One of the main reflections I had writing text was the importance of the web browser.

As learning technologists there is a danger of forgetting what the browser has become. We take it for granted that for many of  our learning designs the web browser in various forms will feature. Advances in browser performance, standards and, in particular executing local code (JavaScript), now mean the browser is increasingly becoming the operating system of the web. You can test this by disabling JavaScript in your browser and seeing how far you get with some of your favourite web applications.

To illustrate how powerful the web browser is becoming you can checkout some of the Mozilla projects mentioned below or watch this demo of the appear.in service which allows people to convene small meetings (up to 8 people) with video, voice and chat without the need for logins or additional browser plugins on both desktop and mobile.

So don’t forget the browser (and don’t let your IT services forget the browser either ;)

Share this post on:
| | |
Posted in Web Apps on by .


Screenshot from TAGSExplorerGiven the number researchers who ask me about access to historic Twitter data who end up disappointed to hear free access to search results are limited to the last 7 days I’m sure they will be pleased to hear about the Twitter Data Grants:

we’re introducing a pilot project we’re calling Twitter Data Grants, through which we’ll give a handful of research institutions access to our public and historical data.

This was an area I’d hoped the Library of Congress who’d have solved long ago given they were gifted the data in April 2010. Unfortunately despite the announcement in Jan 2013 that access was weeks away nothing has appeared.

It’s worth stressing that Twitter’s initial pilot will be limited to a small number of proposals, but those who do get access will have the opportunity to “collaborate with Twitter engineers and researchers”. This isn’t the first time Twitter have opened data to researchers having made data available for a Jisc funded project to analyse the London Riot and while I except Twitter end up with a handful of elite researchers/institutions hopefully the pilot will be extended.

Proposals for this pilot need to be in by 15 March. A link is included in the Introducing Twitter Data Grants page.

Share this post on:
| | |
Posted in Data, Research, Twitter on by .


Mobile - TTRSS themeAfter my last post on ‘Thieving Feedly’, which got a lovely follow up on The Digital River, I mentioned that I’d uninstalled the Feedly Android app. That left a hole in my feed consumption ways which I think I’ve now filled. Below is a screenshot of how I’m now consuming my feeds. Those from the Google Reader good old days might recognise it. Yep if you can’t replace Google Reader then the best solution for me is to recreate it and this post outlines how I did it.

...continue reading



Happy New Year to you too Simon! Having worked with Twitter and Google Maps API I was aware that their terms are becoming increasingly restrictive making the environment for 3rd party services for doing this increasingly difficult. There is a solution for doing using a modification of my Twitter Archiving Google Spreadsheet (TAGS) project (the guerrilla approach so to speak). The result for #rhizo14 is here (only viewable in non-mobile app versions of Google Maps and not the current preview version) and this post outlines how it was done. ...continue reading


Gravitational collapse is the inward fall of a body due to the influence of its own gravity. [Ref]

The BBC recently ran another week of Stargazing Live where Professor Brian Cox and Dara O Briain try to educate us about the stars, the galaxies, the universes and everything else. I wasn’t intending to watch it but found myself hypnotically drawn in as the hosts and guests dangled some of the known and unknowns before us.

Perhaps whimsically I started think about the parallels between the universe and the internet. The ever expanding mass of stuff, 95% of which is made of ‘dark matter’ (the unknown entity that makes the equations balance). I’ve even done some of my own stargazing exploring the ds106 galaxy. The image below from this shows how the nebula of the interconnected blog posts, comments and tweets which form part of this open course.

ds106 Galaxy

Recently I’ve been seeing a number of cracks in the internet as external forces pull and push it into a shape it shouldn’t be. There are headline events like NSA hacking, ISP filtering/blocking, neutrality. In some ways equally, if not more dangerous, are the multitude or minor events, the things that don’t get headlined, the changes that go almost unnoticed. ...continue reading

Share this post on:
| | |
Posted in Web Literacy on by .


Mike Caulfield has posted an interesting reflection:

Identity is now maintained on our phones. ... Our portal is our app screen. Our network isn’t Facebook or Google or Twitter. It’s the phone address book that is the union of those three imports. And on the phone we stop dreaming about “If only there was a service that integrated functions of Twitter, Gmail, and Snapchat!” Because there is a service that integrates that — your phone’s notifications screen ...continue reading


One of the themes I expect to see for 2010 is more collaborative real-time interaction web applications.

was my bold prediction at the beginning of 2010 (who would want to be a futurologist ;). Mike Caulfield recently highlighted how the WebRTC specification, which permits real-time communication (audio, video, chat, screen interaction) in your browser with a bit of JavaScript and opens the opportunity for educators to more easily breakaway from learning designs created to fit traditional webinar platforms. As Mike highlights: ...continue reading

Share this post on:
| | |
Posted in Infrastructure on by .

1 Comment

It’s been a while since I’ve done a review post but as this year has been a blur if for no one else I wanted to look at my posts from 2013. Rather than a look at everything I wanted to extract some themes and for this first review I wanted to look back at some of the work I’ve done around Google Apps Script and Google Spreadsheets/Sheets. ...continue reading


Last week I was at the MOOC Research Initiative Conference in Texas. The organisers (George Siemens, Amy Collier, Tanya Joosten and others) should stand up and take a bow for a fantastic event. I’m still digesting my thoughts from the event which was full of extremes and at times completely surreal. I leave Arlington with memories of going from being baked at 28C to –10C icestorms; memories of trekking down the freeway with some of the best minds in EdTech trying to make it back to our hotel, spending time with people I’ve long respected including a couple of hours in the company of Martin Weller and Jim Groom the result of which was: ...continue reading