We have been using Google analytics as a pathway for visitors (page views) on our website but does one know that very same analytics service is employed to trace Google Spreadsheet Views. For Instance, inject a tangent snippet of tracking code in the spreadsheet and meanwhile, anyone opens the sheet it’ll automatically be registered in Google analytics.
Google Analytics implements a JavaScript fragment that may be interpolated into website templates for tracking visits. We cannot insert JavaScript inside the cells of a selection sheet instead we will use the IMAGE function combined with some apps script to enable tracking inside the spreadsheet. The guests must be registered as (event) and not as (pageviews). The spreadsheet opens won’t artificially raise your Google analytics report.
Now open any Google Spreadsheet that you simply wish to trace and attend Tools, Script Editor and copy-paste the subsequent code. This is often a custom Google Spreadsheet function which will embed the 1×1 tracking GIF image in our spreadsheet.
/**
* Track Spreadsheet views with Google Analytics
*
* @param {string} gaacount Google Analytics like UA-1234-56
* @param {string} sheet name Name of the Google spreadsheet.
* @param {string} sheet name Name of individual Google Sheet.
* @return The 1x1 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;
}
Save the code, close the app script editor window and return to the spreadsheet.
Click an empty cell and insert the subsequent formula. The cell is going to be blank. but it contains an embedded image. you’ll want to vary the background colour so it’s easy to work out which cell within the spreadsheet contains the tracking formula.
Google Spreadsheet Functions
The GOOGLE ANALYTICS () formula has 3 parameters the analytics dither spreadsheet name and therefore the sheet name. This helps us to separate the track individual sheet insides a spreadsheet.
Now open the spreadsheet during a new browser and attend Google Analytics, Real-time, overview to check the tracking is functioning or not. Sometimes it’s going to take a moment to record the visit, if we might wish to see all the visits –go to behaviour –Events-Overview and click on on the Google spreadsheet category.
The tracking will work albeit the user has enabled ad blocking which is due to Google spreadsheets like Gmail serves images through a proxy server. The downside is that you simply never know that location of the visitor up to all or any the visits show up as united states(the location of the Google servers)