Pass dynamic data to Google Sheets using Google Tag Manager

Let’s say you want to use Google Sheets as a simple database to store your incoming inquiries with all details people fill in the form submission. You can’t use Google Analytics for it as it doesn’t allow personal identifiable information like email address to be stored. That’s why we’ll pass the data through Google App scripts to a Google Sheet, for free.

You can do the same with Zapier, where the coding part is hidden behind their beautiful interface, but their free version supports up to 100 tasks / month.

Let’s start by creating a simple sheet – my contact form has several fields I want to capture, like name, email, and subject. And in addition I’ll record a timestamp in GMT. So I’ll have these 4 as column titles:

table columns

Now we need the app script to do the magic. We go to Tools – Script Editor… and a new tab will open to write your script. We’ll remove the placeholder with the code below, courtesy of Martin Hawskey. It’s slightly updated, as his original version wasn’t working for me.

// 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();
}
}

We need to specify our Sheet so we copy the part between /d/ and /edit and paste it in the insert-sheet-ID-here quotes on line 5.

Google-sheets-ID

We can name our project however we like and save our script. As a next step, we select Publish – Deploy as web app, change the Who has access to the app setting to Anyone, even anonymous, and hit Deploy button.

deploy-web-app

The first time it will ask for authorization, click Continue and Allow and you’ll be presented with the web app URL:
web-app-URL

Copy it as we’ll need it to complete our setup.

Now, let’s create the variables in GTM that will pass the data we want. In my case I have 3 text fields and I’ll use their class names coming from Contact Form 7 WordPress plugin.

This is what my button looks like:

<input class="wpcf7-form-control wpcf7-text wpcf7-validates-as-required" name="your-name" size="40" type="text" value="" />

Name custom JS variable:

function() {
var inputField = document.getElementsByClassName("wpcf7-form-control wpcf7-text wpcf7-validates-as-required")[0];
return inputField.value || "";
}

Email custom JS variable:

function() {
var inputField = document.getElementsByClassName("wpcf7-form-control wpcf7-text wpcf7-email wpcf7-validates-as-required wpcf7-validates-as-email")[0];
return inputField.value || "";
}

Subject custom JS variable:

function() {
var inputField = document.getElementsByClassName("wpcf7-form-control wpcf7-text")[0];
return inputField.value || "";
}

You can quickly test them in the Console of Chrome Developer tools: custom-JS-variables-test

Timestamp custom JS variable:

function(){
var today = new Date();
var str = today.toUTCString();
return str;
}

Now we have all our variables and we’ll contruct our GET call:

https://script.google.com/macros/s/<replace with your web app URL>/exec?Name={{Name}}&Email={{Email}}&Subject={{Subject}}&Date={{Timestamp}}

Be careful to use exactly the same words as it’s case sensitive.

You can test how this works if you open the link in a new tab – you should get a success result {“result”:”success”,”row”:2} and if you check your sheet, it’s automatically populated:

Google-Sheet-populatedWe can remove the dummy data later.

Now let’s create the tag that will fire this in GTM, I’ll use a Custom Image tag. Give it a name, paste your string in the Image URL field and select your trigger (the form submission in my case):

GTM-tag
Create the tag, enter Preview and Debug mode and let’s see what happens. Go to your form, make a submission and we’ll see our tag being fired:

GTM-debug-mode-test

Also, when we check our sheet, it’s already there: Google-Sheets-final

Like magic! :)

5 Google Analytics posts you don’t want to miss in March (+ 3 extra)

Here’s again the last post of the month saved for the top 5 interesting Google Analytics related resources I found on other sites. Here are the five (and 3 extra) for March – you may want to bookmark those as they can be very useful:

Google Analytics and Google Tag Manager Debugging Tools

LunaMetrics shares a variety of built-in tools and Google Chrome Extensions. These tools enhance visibility into the mechanics of the inner workings of GA/GTM and help with the configuring and debugging as well as previewing. The list includes: the JavaScript Console, Google Tag Manager Preview Mode, Google Analytics Debugger, Google Tag Assistant, Injector, Tag Manager Injector, Code Editor for GTM, EditThisCookie.

Track Content Engagement Via GTM (Part 1 and Part 2)

In this two-part series Simo shows how to track how much time users are actually spending engaged with your content. Interaction is counted as clicks, mouse movements, keyboard presses, and scrolls. And it’s important to check both articles as in the first he uses User Timings and in the second they’re replaced with a Custom Metric.

Introducing the Google Analytics 360 Suite

Google Analytics announced the introduction of the Google Analytics 360 Suite – a new collection of products targeting enterprises. It includes Google Audience Center 360 (beta), Google Optimize 360 (beta), Google Data Studio 360 (beta), Google Tag Manager 360, Google Analytics 360 (GA Premium), and Google Attribution 360 (Adometry). Although not all products are launched yet, they promise to solve the issues to see the complete customer journey, get useful insights, enable better sharing within the organization and deliver engaging experiences.

Getting Started with Google Optimize 360

This is an overview of the new Google Optimize 360 that’s still in beta and only selected companies have access to the product. It allows for integration with Google Analytics for both targeting and analysis. You can very quickly create audiences from GA that provide personalization and targeting that would require complex integrations in other tools. It looks great, and I’m waiting to hear the price!

Instantiating & Using The Google Tag Manager dataLayer – Data Layer Best Practices Pt 1 (Part 2, Part 3, Part4)

Here’s a 4-part series of posts from LunaMetrics showing the technical best practices designed to help you successfully interoperate with the Google Tag Manager Data Layer. You’ll learn the proper way to instantiate and work with the dataLayer, pushing to the dataLayer from within Custom HTML Tags, when and how to reference the dataLayer within Custom JavaScript Variables, and how to reset or purge values in the Data Layer properly.

Mapping User Constituencies to Google Analytics Segments

This is a practical example of creating segments that map to a user constituency.

Find Related Products Purchased Together in Google Analytics

If you use Enhanced Ecommerce, don’t miss to enable this feature.

Pull More than 10k rows Unsampled using Google Analytics Sheets Add-on

I found this older post just now, but it’s great when you need to export thousands of rows in a report.

P.S. Got a Google Analytics question? Send it to me and I’ll try to answer it on the blog.

5 Google Analytics posts you don’t want to miss in February

Here’s again the last post of the month saved for the top 5 interesting Google Analytics related resources I found on other sites. Here are the five for February – you may want to bookmark those as they can be very useful:

GA Snippet And GA (GTM) Tag On The Same Page

In this article, Simo answers one of the most frequently asked questions: Can you run Google Analytics using the snippet AND using a Google Tag Manager Tag on the same page? He explains how tracking works across the Google Analytics snippet and Google Tag Manager’s Tag templates. If you do want to combine tracking across the two methods, you need to make sure that tracker settings match across the board.

Guide to Call Tracking and Analytics for Dental Offices

This article focuses on dentists but it’s equally valid for all businesses that want to track phone calls. Which is basically all of them (unless you don’t have a phone number on your site). You’ll learn why call tracking is important, what it can tell you about your incoming callers, and how to use the data from call tracking to increase your marketing ROI.

What’s Premium About Google Analytics Custom Funnels

There are tools in the Google Analytics interface to help understand behavior like the Navigation Summary and Entrance Paths, but both are limited in terms of the scope of steps and stages. Even the Behavior Flow report, showing a complex map of user navigation, really just scratches the surface of behavior analyses. Using the funnel visualization or sequences with advanced segments are also options, but a new feature in Google Analytics Premium may prove to be one of the most powerful (and easiest) tools yet – Custom Funnel Reports. It is a custom reporting template that allows you to experiment and create funnels out of nearly any user behavior and action on your site.

Introducing Autotrack for analytics.js

Google Analytics announced Autotrack for analytics.js as a new solution to the problem of tracking the majority of relevant user interactions. It attempts to leverage as many Google Analytics features as possible while requiring minimal manual implementation and gives developers a foundation for tracking data like outbound link and form tracking, URL change tracking for single page applications, declarative event tracking (clicks), and media query tracking (for responsive design).

Tracking Customer Lifetime Value In Google Analytics

Customer lifetime value (CLV or LTV) is a prediction of the value a customer will have over there lifetime with your company or brand. This article shows how to record the actual lifetime value of each and every customer using custom dimensions and custom metrics. The metric here only includes revenue that occurred during the date range selected, so if a user purchased prior to that period (or after) it won’t be included.

P.S. Got a Google Analytics question? Send it to me and I’ll try to answer it on the blog.

5 Google Analytics posts you don’t want to miss in January

Here’s again the last post of the month saved for the top 5 interesting Google Analytics related resources I found on other sites. Here are the five for January – you may want to bookmark those as they can be very useful:

Excellent Analytics Tip #27: Chase Smart Calculated Metrics!

Avinash Kaushik starts the new year with another great blog post focused on calculated metrics, how to use the feature and some of the important calculated metrics you can create, based on the user, not on the session.

New Google Analytics Feature: Browser Size

This post introduces the new dimension in Google Analytics called Browser Size. It tells you just how large the user’s browser viewport actually is and you can use it for above the fold analysis, for page scroll analysis, and even for fraud detection.

Cookie Settings And Subdomain Tracking In Universal Analytics

Simo explains why you should always add the ‘cookieDomain’ : ‘auto’ field to all your tags and trackers when working with subdomain tracking. This will ensure the Client ID is copied and passed across the domains.

Google Analytics Health Check: Is Your Configuration Broken?

Here’s a nice summary by Shanelle Mullin at ConversionXL containing an extensive list of questions and common issues related to Google Analytics tracking and Google Tag Manager. Use it as a guide to check your implementation.

How I track banner impressions using Google Tag Manager and Universal Analytics

You show banners on your own website and want to track impressions? This post will walk you step-by-step how to do it with Google Tag Manager.

P.S. Got a Google Analytics question? Send it to me and I’ll try to answer it on the blog.

Google Analytics sampling work-around with Google Sheets

Do you get a lot of traffic to your website? Maybe more than 500,000 sessions per month, or even per week? If you answered yes and you’re using the free version of GA, you know it’s a pain to run reports with any advanced segment or secondary dimension applied.

Here’s a way to automate this reporting using the Google Sheets GA add-on. If you haven’t used Google Sheets GA add-on before, please refer to this introduction.

In our scenario I’m assuming we’re having over 200,000 sessions per day, so we need to export the data on a daily basis and combine it for the month. Here’s a link to the Google Sheets template I’m using that you can save in your account by clicking File -> Make a copy.

Here’s how to populate the template, you need to fill-in the yellow fields only:

Sampled data template

  1. In the yellow fields in column A put your dates. In my example these are the individual dates of January.
  2. The View ID field should be the ID of your GA view. If you don’t know it, go to Google Analytics -> Admin -> View Settings and get your View ID. Don’t forget to add ga: in front of the number.

View settings

3. Metrics & Dimensions – you should list your metrics and dimensions in these fields, formatted in such a way that the GA API can understand them. You can refer to the list of popular dimensions and metrics in the right section or click the links below the fields to select the proper dimensions and metrics. You can pull up to 7 dimensions in one report.

4. Segment – I’m interested in traffic from mobile devices, so in the Segment field I’m building my segment. For a quick reference what’s possible with segments, check out the documentation using the link below the field.

5. Filters & Sort – if you need to use a filter you can do so here, and also sort the data.

6. Run the report – select Add-ons -> Google Analytics -> Run reports. In order to run the report you need to login to Google Sheets with the same email, that’s added as user in Google Analytics. You’ll get an upgrade notice the first time you run the report.

Now we have all the data we need. If you’re interested in summary metrics, you can see the results in tab Summary. If you need to get a list of the dimensions, check out the Collect tab. You can export it from there, clean up the headers, do a pivot table in Excel or Google Sheets and get the report you need.

Good luck!