How to utilize Google Apps Script to get Google Analytics data

How to utilize Google Apps Script to get Google Analytics data

Google Apps Script is a great scripting platform that has a wide range of uses. One of the coolest features is one that it allows communicating with other Google products, where Google Analytics is our point of interest now.

To take a look at the Google Analytics integration first?

It is also possible to take advantage of the Google Analytics integration “made by AutoROICalc” that is currently on offer. In this way, the user is allowed to select a metric-dimension combination that is available for the imports and select a date range or add the configuration to scheduled imports.

Yep, go ahead!

However, the “Google Apps Script way” will address more advanced users who love the freedom of custom data processing.

The first step is to create a new Apps Script project with a new “gs” file and we are ready to do the scripting.

Let´s start with a simple configuration:

The sample configuration below represents getting the number of sessions with the source/medium dimension of a specific date.

const config = {
  // Google Analytics
  gaId: "ga:XXXXXXXXX",   // The id of the view to query the data from e.g ga:123456.
  gaDate: "YYYY-MM-DD",   // Report date in YYYY-MM-DD format.
  // Query arguments
  gaMetrics: ['ga:sessions'],
  gaOptions: {
    dimensions: 'ga:sourceMedium',
    samplingLevel: 'HIGHER_PRECISION'
  },
  // AutoROICalc
  apiUrl: "https://autoroicalc.com/api/auto-roi-calc/v1/add-records/",
  apiUser: "<YOUR_API_USERNAME>",
  apiPass: "<YOUR_API_PASSWORD>"
};
  • gaId is your id of the Google Analytics view (profile)
  • gaDate is the date to get the data for
  • apiUser and apiPass are your AutoROICalc API credentials which can be found in the Integrations section

Eager to make it work and get the data

Let´s fetch the Google Analytics report using:

// Fetch the report
let report = Analytics.Data.Ga.get(
  config.gaId,
  config.gaDate,
  config.gaDate,
  config.gaMetrics, 
  config.gaOptions
);

We can “give up” if there are no report rows – the data:

// Give up if no data
if ( !report.rows ) {
  return;
}

The following part is about composing the Records for AutoROICalc:

// Compose AutoROICalc Records
let records = [];
// Loop over the results and push to records array
for (let row of report.rows) {
  let record = {
    'date': config.gaDate,
    'time': "00:00:00",
    'type': config.gaMetrics[0],
    'activity': "closed",
    'desc': "Testing script.google.com",
    'source': [row[0]],
    'value': row[1]
  };
  records.push(record);
}

And finally, POST the Records making an HTTP request:

// Make a request
let auth = config.apiUser + ":" + config.apiPass;
let options = {
  "method": "post",
  "headers": {
    "Authorization": "Basic " + Utilities.base64Encode(auth)
  },
  "payload": JSON.stringify(records)
};
let response = UrlFetchApp.fetch(config.apiUrl, options);

// Log the result
console.log(response.getContentText());

What´s next?

Getting the Google Analytics report data is the point of interest. Then the user is free to make any updates or modifications to handle the retrieved data as desired. It does not have to be the AutoROICalc as the target.

Also don´t forget to take a look at the Google Script project Triggers, the Time-Driven triggers. In this way, it´s possible to automate the reporting/data processing on your own.

All together

This is the whole sample script that could be used as your starting point:

/**
 * The script configuration values.
 * @const {Object} config
 */
const config = {

  // Google Analytics
  gaId: "ga:XXXXXXXXX",   // The id of the view to query the data from e.g ga:123456.
  gaDate: "YYYY-MM-DD",   // Report date in YYYY-MM-DD format.
  // Query arguments
  gaMetrics: ['ga:sessions'],
  gaOptions: {
    dimensions: 'ga:sourceMedium',
    samplingLevel: 'HIGHER_PRECISION'
  },
  // AutoROICalc
  apiUrl: "https://autoroicalc.com/api/auto-roi-calc/v1/add-records/",
  apiUser: "<YOUR_API_USERNAME>",
  apiPass: "<YOUR_API_PASSWORD>"
};

/**
 * Runs the script.
 * @function run
 * @returns {void}
 */
function run() {
  // Fetch the report
  let report = Analytics.Data.Ga.get(
    config.gaId,
    config.gaDate,
    config.gaDate,
    config.gaMetrics, 
    config.gaOptions
  );

  // Give up if no data
  if ( !report.rows ) {
    return;
  }
  
  // Compose AutoROICalc Records
  let records = [];
  // Loop over the results and push to records array
  for (let row of report.rows) {
    let record = {
      'date': config.gaDate,
      'time': "00:00:00",
      'type': config.gaMetrics[0],
      'activity': "closed",
      'desc': "Testing script.google.com",
      'source': [row[0]],
      'value': row[1]
    };
    records.push(record);
  }
  
  // Make a request
  let auth = config.apiUser + ":" + config.apiPass;
  let options = {
    "method": "post",
    "headers": {
      "Authorization": "Basic " + Utilities.base64Encode(auth)
    },
    "payload": JSON.stringify(records)
  };
  let response = UrlFetchApp.fetch(config.apiUrl, options);

  // Log the result
  console.log(response.getContentText());
}

CareFree Data Collection, Enrichment and Reporting

  • Record and Report Crucial Business Activities.
  • From Operations to Strategies.
  • Any Business Model.
  • Open for Automation.
  • Business Intelligence Made Easy
  • Comfy Sharing & E-mail Reporting.
  • Set Once, Enjoy Forever!