Make API request to google analytics from Google Sheets using Google Apps Script

I have a workflow that adds a new row to a google sheet every time the lifecycle property changes in the CRM.

The output includes the Google CID, Persona, Hubspot Customer Id and new Lifecycle into a google sheet.

It looks like this – CRM 2 Google Analytics Google Sheet

There is a settings sheet that controls the google analytics tracking id, and the custom dimension index for the dimensions I’m trying to send.

This is the Google Apps Script that I’m using

  function sendToGoogleAnalytics() {
 
 //  select the data
 
 var spreadSheet = SpreadsheetApp.openById('16Bn6Q9C0mBNy6ic1UO-30cWX5YXTeWkpyrbIt7SO1nw');
 var sheet = spreadSheet.getSheetByName('Lifecycle');
 sheet.activate();
 
 var setting = spreadSheet.getSheetByName('Settings');
 
 // getting settings
 
 var ua = setting.getRange(2,2).getValue();
 var hidcdi = 'cd'+setting.getRange(3,2).getValue();
 var lifecyclecdi = 'cd'+setting.getRange(4,2).getValue();
 var personcdi = 'cd'+setting.getRange(5,2).getValue();
 
 
 //getting values of the last row
 var newData = sheet.getLastRow();
 var gcid = sheet.getRange(newData,1).getValue();
 var persona = sheet.getRange(newData,2).getValue();
 var hubid = sheet.getRange(newData,3).getValue();
 var lifecyclestage = sheet.getRange(newData,4).getValue();

//cache busting

var z = Math.random()*(1000000-1000)+1000;
 
//preparing the api call with options and the query parameters
var event ='&ec=Lifecycle&ea=Change&el='+lifecyclestage;
var parameters = 'v=1&tid='+ua+'&t=event&'+'&cid='+gcid+'&'+hidcdi+'='+hubid+'&'+lifecyclecdi+'='+lifecyclestage+'&'+personcdi+'='+persona+event+'&ni=1&aip=1'+'&z='+z;

    

 var options = {
    'method': 'post',
     'payload': parameters
   };
//    
  
  
    var response = UrlFetchApp.fetch('https://www.google-analytics.com/collect',options);
    
    var responsecode = response.getResponseCode();
    if (responsecode >= 200 && responsecode < 300) {
     UrlFetchApp.fetch('https://www.google-analytics.com/collect',options);
     Logger.log(responsecode);
      Logger.log(response);
     return response;
     } else {   
    Logger.log(responsecode);
    Logger.log(response);
    return response;
 }
  }

As you can see that I also want to add it as an non-interaction event.

This gets the last row, assign the values of those values to variables and then creates variables. It sends via the post method which is the preference. This is all based on Google Analytics Measurement Protocol Parameter Reference Guide and Sending Measured Protocol Hits to Google Analytics.

The response code I get is 200 and the response is some GIF89a with some boxes I can’t read

I’ve checked through the debugger and all the variables are populating properly. And the tracking code id is correct (the one in that doc is an example, but I’m trying it on a real one).

However, I don’t see the event come up in Google Analytics itself.

I’ve tried this as a GET and as a POST, and I get the same response, but it doesn’t show up in Google Analytics.

What am I doing wrong here?

Answer

In the end I couldn’t get this to work directly. So I had to add another step. I believe it had something to do with the User Agent not being properly recorded and I couldn’t pull that in. What I did was that I sent it to Google Tag Manager Server Side, and then sent it from there. When I tried this it worked.

Advertisement

Answer