Google Sheets Tracking with Google Tag Manager

Did you know that you can use Google Sheets as a tracking tool for website analytics?

With just a little help from Google Tag Manager, Google Sheets can track your most essential website interactions.

Google Sheets is an awesome cloud-based spreadsheet app that most people typically use to organize data.

It is a fantastic tool for collecting, analyzing, and automating your data.

You can even export your Google Analytics data to Google Sheets to build dashboards that are easy to manipulate with formulas etc.

Also, most marketers don’t know this, but you can use Google Sheets as a lightweight tracking solution for your website!

One that makes it easy for you to log errors or store data outside of systems like Google Analytics.

In this guide, I will teach you how to use Google Sheets as a tracking tool by sending data over from Google Tag Manager.

Some of what we will cover in this post:

Let’s dive in.

Pull Data into Google Sheets from Google Tag Manager Using Google AppScript

Prepare Your Google Sheet

We’ll start with a plain Google Sheet that we can fill dynamically with data from Google Tag Manager. This Sheet will be our database, with each row representing a single tracked event.

The columns will define event data collected by Google Tag Manager. In this case, those columns are Timestamp, Category, Action, and Label.

Google Sheets plain sheet with columns labeled Timestamp, Category, Action, and Label

For this tutorial, we will be using an event Tag that registers Add to Cart Clicks. From Google Tag Manager, this information gets sent to Google Analytics for tracking and recording.

We can send this same information to our Sheet. Google Tag Manager can send data to multiple tools at the same time, so we just need to integrate this function with Google Sheets to automate the process.

Create Your Script in Your Sheet’s Script Editor

Unfortunately, there is no Tag template or default integration that sends data from Google Tag Manager to a Google Sheet. Instead, we need to use a tool called Google Scripts. This is available in the topbar under Tools and labeled as Script editor.

Google Sheet with Tools dropdown and Script editor option highlighted

When you click on Script editor, your browser will open a new tab for Google Scripts with a mostly-blank script field.

Google Scripts new project with placeholder script

We won’t need the placeholder script, so delete it and copy the following CODE:

// Usage
// 1. Enter sheet name where data is to be written below
// 1. Enter sheet name and key where data is to be written below
var SHEET_NAME = "Sheet1";
var SHEET_KEY = "insert-sheet-ID-here";

// 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) {
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(SHEET_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());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// 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}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}

Paste the code into the script field. This script will log our event from Google Tag Manager and send it to our Sheet, so let’s name it EventLogger.

Google Scripts project titled EventLogger with script for pulling data from Google Tag Manager to Google Sheets

Publish and Deploy Your Script

There are just two easy edits that you need to make in order for this script to send your Google Tag Manager data to the correct Sheet. These will be on lines 4 and 5 of your code, which are var SHEET_NAME = “Sheet1”; and var SHEET_KEY = “insert-sheet-id-here”;.

Enlarged view of script var SHEET_NAME = “Sheet1”; and var SHEET_KEY = “insert-sheet-ID-here”;

In your Google Sheet, check the bottom of the spreadsheet that you want to use for tracking to find the sheet name. In this case, the default name is Sheet1. Because this matches our code, we don’t need to change this line.

If your sheet has a different name, you will need to edit the code so that the text inside the quotes matches your sheet name exactly. You will also need to remember to edit the script if you ever change the sheet name in the future.

Google Sheet with sheet name at bottom toolbar highlighted

Second, we need to insert the sheet ID into our code so that Google Tag Manager knows where to send its data. The sheet ID is the segment of the Sheet’s URL between the d/ and the next forward slash ( / ).

Find this portion of the URL and copy it. Then, paste it in place of the insert-sheet-id-here between the quotation marks in your script. 

Google Sheet URL with sheet ID segment highlighted

Once your script accurately reflects your Sheet’s name and ID, click the save icon in the topbar.

Google Scripts project with sheet name and sheet ID script highlighted, save icon highlighted

Next, we will publish this script so that it will carry out its function. Under Publish, click Deploy as web app.

Google Scripts project with Publish and Deploy as web app highlighted

Here’s the important part: the app needs to be open to everybody. Make sure that you select Anyone, even anonymous from the Who has access to the app dropdown options.

Don’t worry about your data being “public.” Only you know the URL, so you will functionally be the only person who can access the app. Click Deploy

Google Scripts Deploy as web app popup with access permissions and Deploy button highlighted

Because you are deploying the app from a Google account, Google will ask you to Review Permissions before it deploys your script.

Google Scripts Authorization required popup with Review Permissions button highlighted

 Google will let you know that your script app will have access to your spreadsheets. This is fine, so click Allow.

Google permissions with Allow button highlighted

Next, we get a URL for our newly-deployed web app. Copy this to test it in your browser.

Google Scripts Deploy as web app popup with app URL highlighted

Add Static Query Parameters to Your URL

If you test this URL out in a new tab as-is, you will receive a response that the script was executed correctly.

Web app URL pasted in browser with success message highlighted

However, things won’t look quite right in your Google Sheet if you used the URL with no modifications. You will see a new line for a datapoint with a timestamp, but the category, action, and label are all listed as undefined.

Google Sheet with new data line highlighted, with Category, Action, and Label listed as undefined

Why did we get such unhelpful data? Well, we didn’t include any query parameters in the URL. Without these parameters, we haven’t defined which data we wanted to pull from Google Tag Manager.

To solve this, you can add a query string by putting a question mark ( ? ) and then key value pairs for whatever you have defined in your spreadsheet. The Timestamp property is filled automatically, but we need to include the Category, Action, and Label properties in our query parameters.

For each property, we’ll type the property’s name in the sheet, an equals sign, and “test[property]”. We will separate each property with an ampersand ( & ). This means that our whole query string for this event will look like this: ?Category=testcategory&action=testaction&label=testlabel.

Google Sheet URL with new query string highlighted

After you add this query string to your URL, execute it to be presented with a new success message.

Web app success message

Back in our Google Sheet, we will see that our row has been filled with test values exactly as we described them in our query.

Google Sheet with new line of data with values testcategory, testaction, and testlabel

Add Dynamic Query Parameters to Your URL to Pull Your Data

This is where things get awesome. Our web app URL can pull information from Google Tag Manager in the same way that Google Analytics does. To see how we can get the same data, let’s open our event Tag in Google Tag Manager to see how it identifies its data.

In the Tag configuration, we can see that the Category is Clicks. The Action and Label are filled dynamically with {{Click Text}} and {{Click URL}} respectively.

Google Tag Manager event with Category, Action, and Label fields highlighted

Create Your Google Sheet Tag in GTM

Let’s recreate these configurations in a new Tag for our event. But this time, instead of sending the data to Google Analytics, we will send it to our Sheet.

Create a new Tag, and name it something that tells you that it will send Add to Cart Click event data to our Google Sheet—for example, Sheets – Event – Add to Cart Click. For the Tag Type, select Custom Image. We’re not really using an image, but a Custom Image Tag’s only parameter is a URL. Instead of an image URL,  we will enter our script URL.

Filling Query String in Image URL

When we input our script URL, we also need to remember to include our query string. However, this query string will help us get real data from our event Tag using the configurations from the Google Analytics Tag. Therefore, our new query string is ?Category=Clicks&Action={{Click Text}}&Label={{Click URL}}.

Add a Trigger

We’ll also leave the box checked for Enable Cache Busting.

Google Tag Manager event with Tag Type set to Custom Image and Image URL edited to include dynamic query parameters

Because we are tracking the same event and just sending the same data to a new place, we can simply use the same trigger for this Tag that we use for our Google Analytics event. Click Save.

Google Tag Manager event with trigger set to click - Add to Cart and Save button highlighted

Testing

Finally, let’s try our new Tag out to see how it works. Refresh your Container, refresh your web page, and trigger your event. I always click the Add to Cart button with the Command (⌘) or CTRL key pressed so that it opens in a new tab. This prevents us from losing our events in Google Tag Manager’s preview and debug console.

Demo shop page with Add to cart button highlighted

In our preview console, we’ll see that our Tag has fired and our event has been sent to Google Sheets. 

Demo shop page with Sheets - Event - Add to Cart Click in Google Tag Manager

If we check our Sheet, the Timestamp, Category, and Action fields all look great. The problem is the Label cell, which is empty. 

Google Sheet new data row with Timestamp, Category, and Action columns dynamically filled and empty Label column

We can troubleshoot this by returning to our web page and looking at the Tag in our GTM preview and debug console. When we scroll down through the Summary of the Tag data, we can see that the query string on the URL doesn’t have a Label.

Demo shop with URL in Google Tag Manager highlighted

Click on the event that triggers our Tag and click the Variables tab. Here, we can see  that our Click URL value is empty. This means that there was nothing to populate the Label value in our Sheet.

Demo shop with gtm.click, Variables, and Click URL highlighted

This is because our Add to Cart Click event is not actually a URL, but rather a JavaScript call to the shop platform. The user is not really redirected in terms of a link. This means there is no URL associated with the trigger.

We can get around this by collecting the Page URL instead, which gives us valuable information about which product a user added to their cart. Go back to your Tag configuration, and in the query string for your script URL, replace {{Click URL}} with {{Page URL}}.

Google Tag Manager event with Image URL edited to include Page URL as the dynamic Label field

Repeat the test process to see your Sheet correctly populated with Timestamp, Category, Action, and Label data for your event. You now have a tracking and data storage system built into Google Sheets!

Google Sheet with new data row, with all columns filled dynamically from Google Tag Manager

I hope you can see just how useful the Google Sheets app is as a tracking tool when used with Google Tag Manager. If you want to recreate data outside of Google Analytics, track data not supported by Google Analytics, or calculate data inside your tracking tool, Google Sheets can be your new best friend.

Summary

So there you have it! This is how you send data from Google Tag Manager to a Google Sheet, where you can store, organize, and analyze the dataset. Google Sheets tracking with Google Tag Manager is very practical and user-friendly, and now you can set up Google Sheets as a tracking tool for your own website.

What are your favorite tricks for Google Sheets? Are there other functions and features that you’d like us to cover in a tutorial? Let us know in the comments!

Julian Juenemann
About the author: Julian Juenemann

Julian started and grew venture-backed startups with his unique 'data first' approach to Online Marketing. He then founded MeasureSchool.com to help marketers, like him, the data-driven way of digital marketing.

guest
4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Martin
Martin
1 year ago

Hi Julian. Let say I create a spreadsheet in Google Sheet and I can have all the information of my order dynamically populated in the spreadsheet (1 order info by row).

Basically, what I want to track is the value ($), type of sale and the affiliate name.

Is it possible to make GTM listen to this spreadsheet and fire a tag/event when a new row is created (new order)?

Mickael
Mickael
7 months ago

Hi Julian,
Thanks for this video.
I used it for analysing my form completion it’s a really powerfull tool. Therefore, I have noticed that this works only on google chrome browser and on desktop only. (I tried with chrome on mobile, and on safari desktop…)

Do you have an idea/recommendation to help fixing this issue ?

Thank you so much 🙂