How to Debug your Google Analytics Tagging with Google Sheets


Estimated reading time: 11 minutes

Debugging Google Analytics tracking implementation can be quite the challenge. There are just so many processing points which are out of control and seem to work in ways different to what you would expect (see Lost Google Analytics Events)In this article, I will show you an approach which helps me to troubleshoot complex tracking implementations in a quick and easy fashion.

My number one Google Analytics debugger implementation tool is the Google Analytics Debugger Google Chrome extension. I find it even more useful than the Google Tag Manager container preview mode. 

So the correct tagging solution selection heavily depends on the incoming data (Data Layer), as this data contains vital values for the tracking. If certain pages have some values that are missing or incorrect, Google Analytics will reject the hit and consequently leave data missing in GA! On top of that, this problem only appears in certain situations. How can you achieve the "data confidence nirvana" if your tagging sometimes randomly skips a few data points!? Unfortunately, instances like this are not uncommon and this is why we need to debug our Google Analytics tagging implementation.


To properly understand and solve the problem there is a technique in which you can record all hits which are being sent to GA together with their context information, such as Data Layer, browser information, timestamp, and anything else which may be required. You can then transfer this information into a Google Sheet and with this information, you can put your developer hat on and start digging into what may have gone wrong - is there a problem with tagging or has something strange surfaced in the Data Layer? 

Saving Google Analytics Hits to a Google Sheet Document 

I am going to show you how you can copy each hit (request to Google Analytics Data Collection Endpoint) to a Google Sheet without having an impact on the tagging. You want to end up with a Sheet that looks something like what is pictured below. Looks pretty cool, right?

GA hits Google Sheets Mozilla Firefox 2018 08 23 11 45 22

So to begin to configure a sheet like this you'll firstly need to set up Google Sheet document which will capture the data. Navigate to Google Drive ( and create a new Google Sheet document. As a name, I usually follow the template: GA Hits:

Next, create the following columns in the document;

  • Date
  • Request
  • DataLayer
  • GA Client ID
  • User-Agent

The Date column is a request timestamp, the Request column will have a string with the raw data being delivered to Google Analytics (in Measurement Protocol format), the DataLayer column will show a JSON representation of the Data Layer at the moment when the hit was sent, the GA Client ID column is a Google Analytics browser cookie to help you identify a customer, the User-Agent describes the browser the user has used to access website.

You can add other columns for additional pieces of data you would like to collect (such as Page URL, Page Title, etc - whatever information is most important to you).

Once the above is done, we need to configure the Google Sheet document to accept our data. Click on Tools --> Script Editor and if you know JavaScript, please feel free to modify to suit your needs, if you don't just copy and paste it as is;

   Copyright 2011 Martin Hawksey
   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   See the License for the specific language governing permissions and
   limitations under the License.


// Usage
//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";
//  2. Run > setup
//  3. Publish > Deploy as web app 
//    - enter Project Version name and click 'Save New Version' 
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously) 
//  4. Copy the 'Current web app URL' and post this in your form/script action 
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);

function doPost(e){
  return handleResponse(e);

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1]
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        //row.push(new Date());
        row.push(' ' + new Date().toISOString());
      } else { // else use header name to get data
        var headerAtIndexI = headers[i]; //to improve readability in blog engine
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
  } finally { //release lock

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());


The above is based on the Google App Script to insert data to a google spreadsheet work of Martin Hawksey.

Next, in the Script Editor click on Save icon.

Untitled 4.0.21 2018 08 23 10 30 20

Click on a Select function text, choose 'setup' and run the code by clicking on the play button.

Untitled 4.0.21 2018 08 23 10 32 33

Authorise the project, if required and deploy this as a web app. Click on Publish and pick 'Deploy as web app'

Untitled 4.0.21 2018 08 23 10 34 21 

When prompted, click 'Create' or 'Update' and copy the web app URL. We will need it in the future.

How to debug <a href= google analytics implementation - 1" width="456" height="279">

Ok, so the document is ready to receive debug data, let's send them.

We are going to inject our code into the Google Analytics library immediately before it is ready to send data to Google Analytics' servers. 

I will show you how to create and use a Custom Task in Google Tag Manager, but the Google Analytics plugin can do the job as well. 

Using Google Tag Manager to create a Custom Task for GA Tags.

If you are not using Google Tag Manager (GTM) yet, I strongly encourage you to - this will really make your life easier. 

In Google Tag Manager, navigate to the container used on the website, create a new workspace and name it accordingly. For example;

  • Workspace name: [DEBUG] Mirroring all hits to Google Sheet
  • Workspace description:
    Saving all Google Analytics hits to the Google Sheet document as per

In Google Tag Manager navigate to Variables and create a new variable named: JS - GA Custom Task - Log hit to Google Sheet with type Custom JavaScript Variable.

Now copy-paste the following code:

function() {
  return function(model) {
    var globalSendTaskName = '_' + model.get('trackingId') + '_sendHitTask';
    // Hook the sendHitTask - grab an original reference to a function
    var originalSendTask = window[globalSendTaskName] = window[globalSendTaskName] || model.get('sendHitTask');
    model.set('sendHitTask', function(sendModel) { //overwrite sendHitTask with our code
      var hitPayload = sendModel.get('hitPayload');     
      //if(hitPayload.indexOf('&ti=') !== -1) { //Capture only Transaction hits
      if(hitPayload.indexOf('&t=timing') === -1) { //SkipTtimings hits

        //In the line below, replace YOUR-WEB_APP_URL with the Web App URL (provided to you when you published your Google Sheet web app)
        var baseUrl = YOUR_WEB_APP_URL;//'';
        var collectPayLoad = 'Date='+(new Date().toISOString());	//saving date (note, this is client-based time - be aware of timezones)
        collectPayLoad += '&Request=' + encodeURIComponent(sendModel.get('hitPayload'));	//save full request
        collectPayLoad += '&GAClientID=' + encodeURIComponent(sendModel.get('clientId'));	//save GA browser id/cookie
        collectPayLoad += '&User-Agent=' + encodeURIComponent(navigator.userAgent);			//save User-Agent
        //add your fields collection here

        //save dataLayer
        if(JSON && typeof JSON.stringify === 'function') {
          //JSON.stringify will fail if there are circular references in your dataLayer. 
          //to simplify the demo, I am not 
          var dlAsJSON = JSON.stringify(window.IRXDataLayer);
          collectPayLoad += '&DataLayer='+encodeURIComponent(dlAsJSON); //Data Layer as JSON. 
        var collectUrl = baseUrl +'?'+ collectPayLoad;
        //Deliver data to our Google Sheet
        //In your real project you may consider to use navigator.sendBeacon delivery method
        var myImage = new Image();
        myImage.src = collectUrl;        

      //Execute original

Source code:

Replace the YOUR_WEB_APP_URL with the value we saved earlier (web app URL) and save the variable.

The last thing is to use this variable as a Custom Task for each Google Analytics (Universal Analytics) tag by updating each of them or updating all the Google Analytics settings variables.

Add new Field named customTask with the value of {{JS - GA Custom Task - Log hit to G Sheet}}. Please see screenshot below.

Google Analytics Debugger - 2

Save the tag and preview the container by clicking on the Preview button in GTM and navigating to the website in the same browser.

Never publish the GTM container without testing/previewing your changes.

You should be seeing something like this in your Google Sheet document immediately after page with the updated tracking that was loaded in the browser.

 GA hits Google Sheets Mozilla Firefox 2018 08 23 11 45 22

Google Analytics Real-Time reports are very useful for debugging tagging solution but they are showing a limited set of data ( only transactional, custom dimensions, etc) and work with a bit of a delay. This technique works much faster. Normally I see data in the Google Sheet before it appears in GA or is available in the reports.

Exactly the same approach can be used to validate all or some of the hits against Google Analytics data collection debug endpoint.

You will also find it useful to understand if you are hitting the limits for the Google Analytics hit payload size (see Google Analytics Limits and Quotas) in your Enhanced Ecommerce hits.

So what have we covered? 

Today I've taken you through one of the best ways to test and debug your Google Analytics tagging. Using this method should allow you to get to the bottom of any tagging problems you may be having quickly and efficiently. 

If you have any questions or are looking for assistance with any tagging or debugging issues get in touch today. Internetrix has a diverse team of digital analytics experts with years of experience utilising both Google Tag Manager and various Analytics platforms.