09 March 2016

How to Track Google Spreadsheet Views with Google Analytics

You have been using Google Analytics to track visitors (or page views) on your website but did you know that the same analytics service can also be used for tracking views inside Google Spreadsheets. You insert a little snippet of tracking code inside your spreadsheet and when someone opens the sheet, that visit will be recorded permanently in your Google Analytics account.


Google Analytics provides a JavaScript snippet that can be inserted into web templates for tracking visits. You cannot insert JavaScript inside the cells of a Google Spreadsheet but we can use the IMAGE function combined with some Apps Script to enable tracking inside spreadsheet. The visit is recorded as an “event” and not a “page view” and thus your spreadsheet opens will not artificially inflate your Google Analytics reports.

To get started, go to your Google Analytics dashboard and make a note of the Google Analytics tracking ID which is a string like UA-12345-67. This web tutorial explains how you can locate the ID inside your Analytics dashboard.

Now open any Google Spreadsheet that you wish to track and go to Tools, Script Editor and copy-paste the following code. This is a custom Google Spreadsheet function that will embed the 1×1 tracking GIF image in our spreadsheets.

 * Track Spreadsheet views with Google Analytics
 * @param {string} gaaccount Google Analytics Account like UA-1234-56.
 * @param {string} spreadsheet Name of the Google Spreadsheet.
 * @param {string} sheetname Name of individual Google Sheet.
 * @return The 1x1 tracking GIF image
 * @customfunction

function GOOGLEANALYTICS(gaaccount, spreadsheet, sheetname) {
  * Written by Amit Agarwal 
  * Web: www.ctrlq.org 
  * Email: amit@labnol.org 
  var imageURL = [
    "&tid=" + gaaccount,
    "&cid=" + Utilities.getUuid(),
    "&z="   + Math.round(Date.now() / 1000).toString(),
    "&ec="  + encodeURIComponent("Google Spreadsheets"),
    "&ea="  + encodeURIComponent(spreadsheet || "Spreadsheet"),
    "&el="  + encodeURIComponent(sheetname || "Sheet")
  return imageURL;


Save the code, close the Apps Script editor window and return to the spreadsheet.

Click an empty cell and insert the following formula. The cell will be blank but it contains an embedded image. You may want to change the background color so it is easy to figure out which cell in the spreadsheet contains the tracking formula.

Google Formula

The GOOGLEANALYTICS() formula takes 3 parameters – the analytics ID, the spreadsheet name and the sheet name. This helps if you would like to separately track individual sheets inside a spreadsheet.

Now open the spreadsheet in a new browser window and go to Google Analytics, Real Time, Overview to test if the tracking is working. It may sometimes take a minute to record the visit. If you would like to see all the visits, go to Behavior – Events – Overview and click on the Google Spreadsheets category.


Also see: Track Gmail Messages with Google Analytics

The tracking would work even if the user has enabled ad blocking and that’s because Google Spreadsheets, like Gmail, serves images through a proxy server. The downside is that you will never know the location of the visitor since all visits will be show up as United States (the location of Google servers).

The story, How to Track Google Spreadsheet Views with Google Analytics, was originally published at Digital Inspiration by Amit Agarwal on 09/03/2016 under Google Analytics, Google Docs, Internet.

08 March 2016

How to Make eBooks with Google Docs

Adobe PDF may be the most popular document format on the web but there’s a reason why eBook lovers prefer the ePub format over PDF. PDF documents have a static layout with fixed page breaks but the layout of an ePUB document is “responsive” meaning it will automatically adjust for different screen sizes.

You can read an ePub book on your desktop or inside the Google Play Books app on your Android phone and they’ll look good. Also, since ePub files are essentially HTML5 documents under the hood, the text size and font family can be easily adjusted for an improved reading experience.

Download ePUB ebooks

How to Create your ePUB eBooks

Adobe Stanza and Calibre are popular desktop software for  creating ePub ebooks but now can now easily make one in the browser using Google Docs itself. Just write a document in Google Document, or upload an existing Microsoft Word file to your Google Drive, go to File menu and choose download as EPUB publication.

Here’s an ePUB version of this article, generated with Google Docs.

You get an ePub file that you can copy to your mobile phone or, better still, upload the ePub to the Google Books website and access the ebook from anywhere. You can even read it in the browser. To upload an eBook to Google Books, go here and click the Upload Files button. The files are only visible to you.

ePub files are readable on iPad, Nook and Google Play books. Amazon Kindle doesn’t support the ePub format but there’s an easy way to convert ePub into MOBI format that is supported by Kindle.

The story, How to Make eBooks with Google Docs, was originally published at Digital Inspiration by Amit Agarwal on 08/03/2016 under Convert, Epub, Google Docs, Internet.