Google Sheets Tracking with Google Tag Manager

Last Modified on April 22, 2024

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.

This guide is based on a blog post from Google Analytics Notes.

And in this guide, I will teach you how to perform Google Sheets tracking with Google Tag Manager by sending data over from Google Tag Manager.

🚨 Note: If you’re new to Google Tag Manager, we recommend starting out with our Google Tag Manager tutorial.

Some of what we will cover in this post:

• Preparing your Google Sheet for Tracking
• Creating your AppScript
• Add query parameters to your URL
• Setting up your GTM Tag

Let’s dive in.

How to Set Up Google Sheets Tracking with Google Tag Manager

Here are the summarized steps:

• Preparing the Google Sheet
• Creating script in Sheet’s Script Editor (Script provided)
• Publishing and deploying the script
• Adding static query parameters to your URL
• Adding dynamic query parameters to your URL
• Creating Google Sheet tag in GTM
• Filling query string in image URL
• Adding a trigger in GTM

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.

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.

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

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.

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”;.

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.

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. 

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

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

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. 

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

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

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

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.

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.

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.

After you add this query string to your URL, execute it to be presented with a new 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.

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.

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.

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.

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.

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

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

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.

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.

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}}.

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!

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.

FAQ

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.

If you’re using popups, tracking interactions with them is essential to making sure they’re actually doing the job.

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!