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! 🙂

is a Google Analytics fan, who also loves SEO, conversion optimization, online marketing and social media. You can follow her on Twitter and LinkedIn.

33 thoughts on “Pass dynamic data to Google Sheets using Google Tag Manager

  1. To start, this tutorial is amazing, I have been looking for something like this for a while now. If I wanted this same process to go to a different location, say a database or email system rather than a Google Sheet. What would I change?

      • Hey,
        Its actually GTM. I would be creating variables like you did, passing dynamic values to them based on dataLayer values or scraping the HTML, then hopefully sending it to the location that I want. I already know how to get the data that I want into the variables. But I don’t know how to change the location of where it goes.

          • Thanks! I have researched the Zapier option and it doesn’t connect to my application. I thought there might be an easy way to modify the code you provided to send the data to a different location.

  2. Pingback: Pixel Implementation QA with Google Speadsheets – Brettrics | Just like Metrics

  3. This is the coolest use of GTM ever. I send nightclub bookings via email to my client, and every month I’d have to slog through all the email entries and put them into sheets so they could run their revenue against the bookings to figure out RO. (They won’t let me see the books!)

    What’s super cool is you can attach your google sheets to data studio and give the hostess access to the real time feed of bookings coming in. Much more user friendly presentation!

  4. Hey!

    Great post! Work for me, almost perfectly..

    Could there be any reason that the integration with GTM could work only locally?

    I’m getting everything I want, but when I’m changing my IP address to another country (via VON services) – I see that the Tags are fired, but my sheet isn’t getting any information..

    Do you have any idea what could cause that?

  5. Thank for the info, the action script is giving me a message of:
    {“result”:”error”,”error”:{“message”:”Bad value”,”name”:”Exception”,”fileName”:”Code”,”lineNumber”:34,”stack”:”\tat Code:34 (handleResponse)\n\tat Code:21 (doGet)\n”}}

  6. Hi Margarita,

    Thank you for sharing this tool, it has been working great for me and is exactly what I needed!

    Is there a way I can go one step further and have an auto-trigger post an update to a Slack channel every time a new row is added to the Google Sheet. I know Zapier is one option but was wondering if there is a way to do it with Google App scripts? It should be possible in theory, given that Slack will generate a webhook URL to post to a specific channel, but I’m new to scripts so not sure how to begin.

    Thanks!

  7. Does anyone know why a keep getting this and not success?
    Sorry, unable to open the file at this time.

    Please check the address and try again.

    Get stuff done with Google Drive

    Apps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more.

    Learn more at drive.google.com/start/apps.

      • Hi Margarita!

        First of all thank you for this amazing post. However, I’m also getting the exact same issue. It happens when i try to publish /Deploy it as web app. The URL gives me :

        Sorry, unable to open the file at this time.

        “Please check the address and try again.

        Get stuff done with Google Drive

        Apps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more.

        Learn more at drive.google.com/start/apps.”

        I tried the steps that you suggested.

        Any idea why this is happening? Is this a 2018 issue or something ?

      • Hi Margarita,

        Your script is amazing and I have been using it for months now, without any issue.
        Just in the past few days, I have tried to apply the same script to other spreadsheets and I got the error above.
        Interestingly enough, the very first script I implemented months ago is still working fine!

        Maybe you can’t use it more than once?

        Thanks in advance

        Jack

  8. Hi Margarita,

    Thanks a lot for this script.
    Is it possible to trigger the tag even if the form has not been sent, just if the field is filled in ?

    • Hi Jerome, yes, it should be. You’ll need to modify the trigger and instead on form submission send the information when the visitor has left the field. I haven’t tested it so can’t provide detailed instructions.

  9. Great tutorial – thanks a lot!

    Regarding to the getRange issue – i went through this as well – the problem was in the second variable in the code – in different languages than in English are sheets named different – so it was necessary to change the SHEET_NAME value (to “List 1” in my case). Maybe it could help someone to save some time 🙂

  10. Great post! It works.
    But I fixed last column, because on your script in range on first row, the header, Date not found instead Timestamp works fine.

    So, I can use for my CRM but I cannot to extract PII only on GA, but via Sheet and GTM should I extract anythings? It’s stranged for policy politics, isn’t it?

    Thanks 😉
    cheers
    Marcus

  11. Pingback: Lead Validator | Wpromote Blog

  12. Margarita,

    Thank you for this amazing article – what a great tool!

    My issue is that I can only get the spreadsheet to populate when I interact with the website myself, not when other people are firing the Google Tag Manager tags.

    Is this a permissions issue? When I deploy the script in Google Script Editor, it says the app is not verified, so I have to run it as an unverified app in order for it to work.

    Thank you for any insight you can provide!

    Liam

  13. Hi,
    First of all, thank you for such an amazing article.

    However, I am having some problems where the names are returned as undefined on some browsers.

    For example, it works fine for google chrome, but all firefox browsers returns “undefined” values.

Leave a Reply

Your email address will not be published. Required fields are marked *