Google Tag Manager is a free tool provided by Google that allows you to add and manage marketing and analytics tags on your website without requiring any coding knowledge. With GTM, you can easily add tags from various tools like Google Analytics, Facebook Pixel, and many others. But that's not all. You can also use GTM to send data from your website to other tools like Google Sheets.
Why we need to send data to Google sheets
There are many reasons why you might want to pass dynamic data from your website to Google Sheets. Here are a few examples:Collecting form submissions: You can use GTM to track form submissions on your website and send the data to Google Sheets. This makes it easy to analyze the data and create reports.
Tracking e-commerce transactions: If you run an e-commerce website, you can use GTM to track transactions and send the data to Google Sheets. This makes it easy to analyze your sales data and identify trends.
Tracking user behavior: You can use GTM to track user behavior on your website and send the data to Google Sheets. This makes it easy to analyze how users interact with your website and identify areas for improvement.
I have a client website where my client wants to track the users (Name, Email ID, User ID etc.) who have landed on some specific pages, signed up on website but could not purchase, so that he can use them for remarketing.
I have divided this complete setup in 2 parts.
1. USED ID
2. Google Sheets Using Google Tag Manager
2. Google Sheets Using Google Tag Manager
So if you want to pull out the user registration data to google sheets. This can be achieved through Google tag manager.
1. First you need to create a new google excel on Google drive excel and name it (Drive link: https://drive.google.com/). I want to track Name, Email_ID and User_ID, so I name them accordingly.
2. go to Tools – Script Editor… and a new tab will open to write your script. Remove the placeholder with the code below.
// 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();
}
}
3. Channge the SHEET_NAME and SHEET_KEY with your key of your sheet.
4. As a next step, select Publish – Deploy as new developement, select the setting button and choose web app change the Who has access to Anyone and hit Deploy button, A popup window will open and ask you to allow it authroize.
5. The developement has sucessfully installed.
6. Validate by copy the URL and try to enter the values into it like below in picture. by hitting that URL the values are succefully added into excel sheet.
This is your line of code, Repalce {{ }}, with your filed data and hit try !!
https://script.google.com/macros/s/7. Now, let’s create the variables in GTM that will pass the data we want./exec?Name={{Name}}&Email={{Email}}&Subject={{Subject}}&Date={{Timestamp}}
