Sending Data to Google Sheets via GTM

August 1, 2024 emreozcan9

Google Tag Manager does not send data to only GA4. There are numerious platforms that you can connect GTM with. Google Sheets is one of them. Sending data to Google Sheets has many advantages. You can send any data bacause this is your company’s internal database. You can send user related id, user emails so on. Normally, sending these information to GA4 would be risky in terms of privacy regulations. In this article, we are going to deep dive how to send data to Google Sheets with GTM. Lets begin.

First of all, you need to open a new Google Sheets, then go to Extensions -> Apps Script. You will see untitled project’s code editor page. Name the project whatever you want, I will do “Tutorial Project”. Then, delete the default code that begins with a function. We will insert a new code there. It is right below.

var SHEET_NAME = "YOUR SHEET NAME";

var SHEET_KEY = "YOUR SHEET KEY";


var SCRIPT_PROP = PropertiesService.getScriptProperties(); 

 

function doGet(e){

return handleResponse(e);

}

 

function doPost(e){

return handleResponse(e);

}

 

function handleResponse(e) {

var lock = LockService.getPublicLock();

lock.waitLock(30000); 

 

try {

var doc = SpreadsheetApp.openById(SHEET_KEY);

var sheet = doc.getSheetByName(SHEET_NAME);


var headRow = e.parameter.header_row || 1;

var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

var nextRow = sheet.getLastRow()+1; 

var row = [];

 

var currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');


for (i in headers){

if (headers[i] == "Date"){ 

row.push(currentDate);

} else { 

row.push(e.parameter[headers[i]]);

}

}

 
var range = sheet.getRange(nextRow, 1, 1, 1);

  range.setValue(currentDate).setNumberFormat('yyyy-MM-dd');


  for (var i = 1; i < row.length; i++) {

    range.offset(0, i).setValue(row[i]);

  }

 

sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);



function doGet() {


  var imageUrl = "https://upload.wikimedia.org/wikipedia/commons/thumb/c/c1/Google_%22G%22_logo.svg/768px-Google_%22G%22_logo.svg.png";

 

  var response = UrlFetchApp.fetch(imageUrl);

  var imageBlob = response.getBlob();

 

  return ContentService

    .createTextOutput("")

    .setMimeType(ContentService.MimeType.PNG) 

    .setContent(imageBlob);

}

} catch(e){


return ContentService

  .createTextOutput("error")

  .setMimeType(ContentService.MimeType.TEXT);


} finally { 

lock.releaseLock();

}

}

Things to consider:

Sheet name is the exact sheet name that is seen below when you open your Google Sheets document.
Sheet key is part of a URL of a Google Sheets document. It is between d/ and the consequent /. For example, the sheet key is like that for this URL: https://docs.google.com/spreadsheets/d/10h5KH_SUPH_HCTG9pH8NTGGnKCJxMxQDzMFILTm0Ffk/edit?gid=0#gid=0. Watch out the quotation marks for both sheet name and sheet key.
You can select any image on Google for imageUrl variable. Image content is not actually important. It can be logo of something or anything else. I found Google’s logo in one of the websites and copied the image url. Watch out the quotation marks here too.
MimeType should be consistent with the extension in imageUrl variable. If the url has JPG extension, then change the MimeType as .JPG.

After prepearing the code without a mistake, it is time to deploy the project. Go to Deploy->New Deployment->Select type as Web App. Add description. Give access to ANYONE. Click Deploy. Continue Authoritize and go until the end. You will get web app URL. Copy it and note it somewhere else. You will need it later.

Testing the Web App URL to See If It Works

First, lets add variable rows on our Google Sheets page. I am adding date,variable1 and variable2.

To test if the URL writes successfully on our sheet, open a new window. Add ?variable1=test&variable2=test page path at the end of the Web App Url that you copied beforehand above. Click enter and let the page load. You need to see this page:

And Google Sheets page should look like this:

If you dont see a new row in page, and if script load page says you some errors, you should try a solution. Open the web app code editor again and run it. Then you will see related errors and other tasks to complete.

Congratulations, we have completed one important step of this article. Now we are moving to GTM and doing our implementations.

First, we need decide when and what data do we want to write on Google Sheets. In this scenerio, we will use ikea.com/global website. There is “Jobs” menu in ikea’s website. I want to send some information when user clicks Jobs menu. Therefore, we need to create related trigger about it.

Then we need to create custom HTML tag. This tag will fire on Jobs pages and send our parameter values to Google Sheets. Here is the code to insert in custom HTML tag:

<script>

  (function() {

    var country = "{{Country}}";
    var device = "{{Device}}";
    var browser = "{{Browser}}";


    var baseUrl = "https://script.google.com/macros/s/Agfycby-Fds33FtR2InQfHhYPpgBuq7IUkTIt5CEpgZR4Eun0cTsj_Mh5Q-f4Z3DHD_4BZJK/exec";

    var urlWithVariables = baseUrl + "?Country=" + country + "&Device=" + device + "&Browser=" + browser;


    var img = new Image(1, 1);

    img.src = urlWithVariables;

  })();

</script>

Things to consider:

I chose sending country, device and browser variables. You need to select your own variables.
Change baseUrl with your own web app url.
urlWithVariables is making the magic here. Variable names inside quotation marks should match with column names in Google Sheets page. Also they should be same order. Therefore, I will change my Google Sheets document column names since they were variable 1 and variable 2. Date should remain same.

After doing preview mode, you will see the data on Google Sheets like below.

To sum up, this article aims to teach you a way of sending data to Google Sheets with GTM. I hope examples and given codes help you a bit. Check out also other articles to widen your web analytics perspective. See you in the next blog post!