Skip to main content

How to Track Google Spreadsheet Views with Google Analytics

By
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.
track-google-spreadsheets.png
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 = [
    "https://ssl.google-analytics.com/collect?v=1&t=event",
    "&tid=" + gaaccount,
    "&cid=" + Utilities.getUuid(),
    "&z="   + Math.round(Date.now() / 1000).toString(),
    "&ec="  + encodeURIComponent("Google Spreadsheets"),
    "&ea="  + encodeURIComponent(spreadsheet || "Spreadsheet"),
    "&el="  + encodeURIComponent(sheetname || "Sheet")
  ].join("");
  
  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.
google-analytics-report.png
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).

Comments

Post a Comment

Popular posts from this blog

How to Secure Your Wireless (Wi-Fi) Home Network

By
Wireless Networking (Wi-Fi) has made it so easy for anyone to use Internet on your computer, mobile phones, tablets and other wireless devices anywhere in the house without the clutter of cables. With traditional wired networks, it is extremely difficult for someone to steal your bandwidth but the big problem with wireless signals is that others can access the Internet using your broadband connection even while they are in a neighboring building or sitting in a car that’s parked outside your apartment. This practice, also known as piggybacking, is bad for three reasons: It will increase your monthly Internet bill especially when you have to pay per byte of data transfer. It will decrease your Internet access speed since you are now sharing the same internet connection with other users. It can create a security hazard* as others may hack your computers and access your personal files through your own wireless network. [*] What do the bad guys use  – There ha...
Post a Comment
Read more

Twitter Guide: How To Do Everything With Twitter

By
The first tweet was published almost 8 years ago and Twitter has come a long way since then. There’s a whole ecosystem of apps and services available now that allow us to use Twitter in more ways than ever before. This guide curates the best tools that will help you get the most out of Twitter. 1.  Nuzzel  – This works as an intelligent filter for Twitter and helps you discover the most popular news stories shared across your Twitter network. All signal, no noise. 2.  Flipboard  – Connect your Twitter account to Flipboard and it will create a beautiful magazine for all your Twitter feeds that you can flip through on the web (example), mobile and Windows 10 devices. 3.  LifeOnTwitter  – Get interesting statistics and facts about your Twitter account (example). 4.  TallTweets  – It lets you send tweets of any length by slicing your long tweet into multiple 140-character tweets (sample) and sending them ...
Post a Comment
Read more

Make International Phone Calls from your Mobile even without the Internet

By Anonymous
While apps like Skype or Viber let you make cheap (or even free) international phone calls, you need to connected to WiFi for these apps to work. Meet an alternate that offers similar features but needs no Internet. H o w do you make international calls from your mobile phone? Mobile carriers often charge exorbitant rates for international phone calls but you can Internet based services like Skype or Google Hangouts and call any landline or cell phone number in the world for a low per minute fee. All you need is a mobile phone connected to a Wi-Fi hotspot and some credit balance in your account for making the phone call. You can use these VoIP apps when travelling overseas as well and make significant savings for both domestic and international calls. Now consider a scenario where you have a mobile phone but there’s no Wi-Fi around and the 3G/4G services are either slow or unavailable. Would you still be able to place calls through any of these apps? The answer is obviously in ...
Post a Comment
Read more