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:
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.
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.
The first time it will ask for authorization, click Continue and Allow and you’ll be presented with the 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: 
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:
We 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):
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:
Also, when we check our sheet, it’s already there: 
Like magic! 🙂
Margarita Evtimova
Margarita Evtimova is a Google Analytics fan, who also loves SEO, conversion optimization, online marketing and social media. You can follow her on Twitter and LinkedIn.
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?
Thanks, Nathan. It really depends what service you’re using for collecting the information – is it a web form or something else?
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.
Zapier definitely offers this option – webhook to MySQL or SQL Server. There should be also alternative ways to do it but I haven’t researched this more.
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.
Pingback: Pixel Implementation QA with Google Speadsheets – Brettrics | Just like Metrics
Any ideas how I would use that script to update existing records on my sheet?
Unfortunately no, I was just removing the duplicates relying on the timestamp for the last / most recent one.
Thanks Maggie, were you able to automate that somehow?
There are some sample script and add-ons but I haven’t used them myself, so I can’t recommend anything specific.
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!
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?
Hi Tal, can you please check the settings of the script – does it say that anyone has access to the app?
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”}}
Hi Jeremy, did you insert your sheet ID in the beginning of the script as described above?
I replaced the action script and when testing the code it gave me that error
Sorry, but nobody else encountered this error and I don’t know what else to suggest. It’s on line 34 and there’s the reference to the sheet key. If you replaced it correctly it should work.
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!
Wonderful script!!!
Worked for me without using the doPost.
Thanks a ton!
Hi,
It didn’t give you problem with Google drive
I can’t get the success approval from the URL
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.
What step do you get this on? Have you tried any of these: https://productforums.google.com/forum/#!topic/drive/GGGF5AzKwdE
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 ?
I believe I’ve found the problem. In the space where she says:
in
https://script.google.com/macros/s//exec?Name={{Name}}&Email={{Email}}&Subject={{Subject}}&Date={{Timestamp}}
Only add that part of the URL from the web app URL. I tried entering the full URL in that space and got this error too.
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
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.
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 🙂
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
Pingback: Lead Validator | Wpromote Blog
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
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.
Hi Vass, probably you can look for another JS method to capture the name.
Hello, unfortunately I get the messe “Script Function not found: DoGet” when testing the Script. Could you please help me?
Kind Regards
Jens
Hi, have you tried searching for the error on Stack Overflow and trying something like the suggestion here: https://stackoverflow.com/questions/22617138/google-apps-script-function-not-found-doget ?
Sign in with Google temporarily disabled for this app
This app has not been verified yet by Google in order to use Google Sign In.
Is there any other way to get the data in Google Sheet.
Is this still working? I tried to apply it, but I’ve got an error message.
Same verification error.
Google has upgraded or leveled up its security concerning scripts, and sometimes with minimal changes you could move forward.
I hope this script gets an update to don’t face the block from Google.
Hey @Maggie,
Thanks for this, this is really handy, is there a way for us to extract data into GTM and do some checking with the data upon fetching it in. Pretty much want to use G.Sheets like a DB.
Cheers,
Pravin