Tracking Google Sheet views with Google Analytics using GA Beacon (and a Google Apps Script Measurement Protocol snippet)

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.

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

https://ga-beacon.appspot.com/UA-XXXXX-X/sheets/UNIQUE_ID

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”))

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.

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