Search code examples
google-apps-scriptgoogle-analyticsgoogle-analytics-apigoogle-analytics-4

Using Google Analytics to track impressions of shpreadsheet+appscript as UI and BE instead of a website

I want to use apps script as the backend of a spreadhsheet (as the UI. Both serve as a complete website sort of) and track the impressions or click on the spreadsheet buttons.

I want to call GA4 from apps script for that.

Googling it shows Using Google Apps Script to fetch data from your Google Analytics account but I want the other way arround..

Also, I don't have a website html (just the spreadsheet as an UI). So this guide isn't exactly what I need.

I have an apps-script that I want to impression count in Google-Analytics.

I have created a new GA account + data stream.

Copied it's measurement id and put it in my app-script code:

/**
 * Function to track all (internal/external) usage
 * @param {string} page tracked
 */
function sendGA(page) {
  var cid = Utilities.base64EncodeWebSafe(Utilities.computeDigest(
      Utilities.DigestAlgorithm.SHA_256,
      ScriptApp.getService().getUrl()));
  try {
    var data = {
      'v': '1',
      'tid': 'G-9S9...',
      'z': Math.floor(Math.random()*10E7),
      't':'pageview',
      'dl': SpreadsheetApp.getActiveSpreadsheet().getUrl() + '/' + page,
      'cid': cid
    };
    var payload = Object.keys(data).map(
      function(key) {
        return encodeURIComponent(key) + '=' + encodeURIComponent(data[key]);
      }
    ).join('&');
    var options = {
      'method' : 'POST',
      'payload' : payload
    };
    UrlFetchApp.fetch('http://www.google-analytics.com/collect', options);
  } catch (err) {
    Logger.log(err);
  }
}

enter image description here

and

enter image description here

These were my installation steps, have I missed anything?

The measurement id goes in here? 'tid': 'G-9S9...',

Solution

  • Based on articles I'm seeing (they may be outdated), tid makes use of tracking ID (e.g. UA-1234-1) that is generated when ticking "Create a Universal Analytics property" when creating the property under Show advanced options.

    I'm seeing similar links that tracks the views instead and is using the pattern UA-xxxx-x in tid. You are using GA4 so it provides measurement ID instead. Try universal analytics if it works and provide the tracking id on the tid.

    sample

    References: