Google Sheets Tracking Using Google Tag Manager

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

With the help of Google Tag Manager, Google Sheets can track your most important website interactions.

Google Sheets is a powerful cloud-based spreadsheet app that most people use to organize data.

It’s great for collecting, analyzing, and automating data.

You can also export your Google Analytics data to Google Sheets to create customizable dashboards using formulas and more.

Many marketers don’t realize that Google Sheets can be used as a lightweight tracking solution for your website!

It’s a simple way to log errors or store data outside of tools like Google Analytics.

This guide, inspired by a blog post from Google Analytics Notes, will show you how to track data using Google Sheets and Google Tag Manager, by sending data from Google Tag Manager directly to your Google Sheets.

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

Import Data into Google Sheets from Google Tag Manager Using Google Apps Script

Set Up Your Google Sheet

We'll begin with a blank Google Sheet that will be populated dynamically with data from Google Tag Manager. This sheet will serve as our database, with each row representing a tracked event.

The columns will outline the event data collected by Google Tag Manager: Timestamp, Category, Action, and Label.

In this tutorial, we'll use an event tag that tracks Add to Cart clicks. Typically, this information is sent to Google Analytics for tracking and recording.

However, we can also send this data to our Google Sheet. Since Google Tag Manager can send data to multiple tools simultaneously, we just need to integrate this function with Google Sheets to automate the process.

Create Your Script in the Sheet’s Script Editor

There isn’t a default Tag template or integration to send data directly from Google Tag Manager to a Google Sheet. Instead, we’ll use Google Apps Script. You can find this tool under the top menu by selecting Tools and then Script Editor.

When you click on Script Editor, a new tab will open with Google Apps Script, displaying 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

You only need to make two simple changes to the script to ensure it sends your Google Tag Manager data to the correct sheet. These changes 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 to find the sheet name you want to use for tracking. In this case, the default name is "Sheet1," which matches our code, so no changes are needed here.

If your sheet has a different name, you'll need to update the code so that the text inside the quotes matches your sheet name exactly. Remember to adjust the script if you change the sheet name in the future.

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

Locate this part of the URL, copy it, and then paste it in place of insert-sheet-id-here within the quotation marks in your script.

Next, we need to publish the script to enable its functionality. Go to Publish and select Deploy as web app.

Here’s a crucial step: the app needs to be accessible to everyone. Make sure to 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’ll be the only one with practical access to the app. Click Deploy to finish.

Since you're deploying the app from a Google account, Google will prompt you to Review Permissions before completing the deployment of your script.

Google will inform you that the script app will have access to your spreadsheets. This is expected, 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 the URL in a new tab, you should receive a response indicating that the script executed correctly.

However, if you use the URL without modifications, your Google Sheet might not display correctly. You’ll see a new line for a data point with a timestamp, but the category, action, and label fields will appear as undefined.

Why did we get incomplete data? It’s because we didn’t include any query parameters in the URL. Without these parameters, we haven't specified which data to pull from Google Tag Manager.

To fix this, you need to add a query string by inserting a question mark (`?`) followed by key-value pairs for the data you want to include. The Timestamp property is filled automatically, but you’ll need to add the Category, Action, and Label properties to your query parameters.

For each property, write the property’s name in the sheet, an equals sign, and test[property]. Separate each property with an ampersand (`&`). So, your query string for this event will look like this: ?Category=testcategory&Action=testaction&Label=testlabel.

After adding the query string to your URL, execute it to see a new success message.

In your Google Sheet, you’ll see that the row has been populated with the test values exactly as specified in your query.

Add Dynamic Query Parameters to Your URL to Pull Your Data

Here’s where it gets exciting. Our web app URL can retrieve information from Google Tag Manager just like Google Analytics does. To understand how to get the same data, let’s open our event Tag in Google Tag Manager to see how it identifies the data.

In the Tag configuration, we can see that the Category is set to "Clicks," while the Action and Label are dynamically populated with {{Click Text}} and {{Click URL}}, respectively.

Create Your Google Sheet Tag in GTM

Let’s set up a new Tag for our event that sends data to our Google Sheet instead of Google Analytics.

Create a new Tag and name it something descriptive, like "Sheets – Event – Add to Cart Click." For the Tag Type, select "Custom Image." While we’re not using an actual image, the Custom Image Tag’s sole parameter is a URL. Instead of an image URL, enter your script URL.

Filling Query String in Image URL

When entering your script URL, don’t forget to include your query string. This query string will allow us to retrieve real data from our event Tag based on the configurations from the Google Analytics Tag. So, your new query string should be: ?Category=Clicks&Action={{Click Text}}&Label={{Click URL}}.

Add a Trigger

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

Since we’re tracking the same event and sending the data to a new destination, you can use the same trigger for this Tag that you use for your Google Analytics event. Click Save to finish.

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 the preview console, you’ll see that our Tag has fired and the event data has been sent to Google Sheets.

If we check our Sheet, the Timestamp, Category, and Action fields all appear as expected. However, the Label cell is empty.

To troubleshoot this, go back to your web page and check the Tag in the GTM preview and debug console. Scroll down through the Tag data summary and you'll see that the query string in the URL is missing the Label.

Click on the event that triggers our Tag and go to the Variables tab. Here, you’ll see that the Click URL value is empty. This indicates that there was no data 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 verify that your Sheet is correctly populated with Timestamp, Category, Action, and Label data for your event. You now have a functional tracking and data storage system integrated with Google Sheets!

I hope you can see how valuable Google Sheets can be as a tracking tool when used with Google Tag Manager.

Whether you want to recreate data outside of Google Analytics, track information not supported by Google Analytics, or perform calculations within your tracking tool, Google Sheets can become your new best friend.

FAQ

Can I export data from Google Analytics to Google Sheets for tracking?

Yes, you can export data from Google Analytics to Google Sheets to build dashboards and perform further analysis. By integrating Google Analytics with Google Sheets, you can automate the process of exporting data and create dynamic reports and visualizations using formulas and functions in Google Sheets.

Can Google Sheets be used as an alternative to Google Analytics for tracking?

Google Sheets can be used as a lightweight tracking solution for your website, especially for tracking data not supported by Google Analytics or for custom calculations and data manipulations. While it may not provide the same level of comprehensive analytics as Google Analytics, it can serve as an additional tool for specific tracking needs.

Can Google Sheets be used for error logging or data storage outside of Google Analytics?

Yes, Google Sheets can be used for error logging or storing data outside of systems like Google Analytics. By integrating Google Sheets with Google Tag Manager, you can log errors or track specific data points not captured by Google Analytics. This offers flexibility for tracking and managing data based on your specific needs.

Summary

So there you have it! This is how you can send data from Google Tag Manager to a Google Sheet, allowing you to store, organize, and analyze your dataset.

Using Google Sheets for tracking with Google Tag Manager is both 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 crucial to ensure they’re performing effectively.

What are your favorite Google Sheets tricks? Are there other functions or features you'd like us to explore in a tutorial? Let us know in the comments!