Track Google Sheet Views With Analytics
We have been using google analytics as pathway for visitors (page views) on our website but do you know that same analytics service is used to track Google Spreadsheet Views. for example insert a little snippet of tracking code inside spreadsheet and when anyone opens the sheet it will automatically be recorded in google analytics.
Google analytics provides a javascript snippet which can be inserted into web templates for tracking visits. We cannot insert javascript inside the cells of a spread sheet instead we can use IMAGE function combined with some apps script to enable tracking inside the spreadsheet. The visitors must be recorded as “event” and not as “page view”.The spreadsheet opens will not artificially raise your google analytics report.
How can I get the list view in New Google Spreadsheet
To start go to google analytics dashboard and note down the id which is having a string like UA-12345-67.This explains how you have to place the ID inside your analytics dashboard.
For example open any google spreadsheet that you like 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 1×1 tracking GIF image
* @customfunction
*/
function GOOGLEANALYTICS(gaaccount, spreadsheet, sheetname) {
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;
}
Track google spreadsheet
save the code and close the apps script editor window then return to spreadsheet.
click on empty cell and insert the following formula.The cell may be blank but it is having an embedded image.If we want to change the background color so it is easy to figure out which cell in the spreadsheet contains the tracking formula.
Google spreadsheet functions
The GOOGLE ANALYTICS() formula is having 3 parameters the analytics id,the spreadsheet name and the sheet name.This helps us to separate track individual sheets inside a spreadsheet.
Now open the spreadsheet in a new browser and go to google analytics,Real time,overview to test the tracking is working or not. Sometimes it may take a minute to record the visit, if we would like to see all the visits –go to behavior –Events-Overview and click on the google spreadsheet category.
The tracking will work even if the user has enabled ad blocking and that is because of google spreadsheets, like gmail serves images through a proxy server.The downside is that you never know that location of the visitor up to all the visits show up as united states(the location of the google servers).
This is what about tracking google spreadsheets views with google analytics.
Drop your suggestions about this topic in the below comment session.
You May Also Like: Gmail Tips and Tricks
Article Reference and Image Source: Labnol
