How Do I Use Google Apps Script To Modify Data That I Pull From Google Analytics?

29 December 2019 - 1 answer

I'm using google apps script to pull data from google analytics and put it in a report in google sheets. For this specific report, the data pulled is very large and I want to modify it before I put it in the google sheet. I need your help to figure out how that can be done using javascript/google apps script.

This is my current code:

function testReport() {

  var profileId = XXXXXXX;
  var tableId = 'ga:' + profileId;

  var startDate = 'yesterday';
  var endDate = 'yesterday';  

  var metrics = 'ga:sessions';
  var optionalArgs = {  
    'dimensions': 'ga:landingPagePath, ga:date',
    'filters': 'ga:sessions>0',
  };

  var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metrics, optionalArgs);

  if (report.rows) {

    var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/FILLER_TEXT/edit#gid=0');
    var sheet = spreadsheet.getSheetByName('Data'); 
    var firstEmptyRow = sheet.getLastRow() + 1;

    sheet.getRange(firstEmptyRow,1, report.rows.length, report.rows[0].length).setValues(report.rows);
  }

}

report.rows is an object that is in this format:

[[/,20191228,100],[/locationOne,20191228,10],[/locationTwo,20191228,1],[/locationOne?s=a,20191228,10]]

This will create a google sheet that looks like this:

Landing Page        Date    Sessions
/                 20191228    100
/locationOne      20191228     10
/locationTwo      20191228      1
/locationOne?s=a  20191228     10

However, there are a lot of landing pages on my website so I need to condense this data before pasting it in the google sheet. Ideally, I want to do two things:

Create a new column called 'Web Section' based on Landing Page values. For example, if the landing page contains 'One' then the value in the new column should be 'Location One'. If the landing page contains 'Two' then the value should be 'Location Two'. If the value is not specified, all data should fall under 'Others'
Do a group by on the data so that the data is grouped by the columns 'Web Section' and 'Date' and shows the sum of sessions

Based on this, I should get a new table that looks like this:

Web Section      Date    Sessions
Location One   20191228     20
Location Two   20191228      1
Others         20191228    100

Thank you for helping me out with this!

Answer

You want to achieve the following situation.

From

[["/",20191228,100],["/locationOne",20191228,10],["/locationTwo",20191228,1],["/locationOne?s=a",20191228,10],["/locationOne",20191229,10]]

Web Section      Date    Sessions
Location One   20191228     20
Location One   20191229     10
Location Two   20191228      1
Others         20191228    100

You want to modify location to Location.
If the date is different, you want to separate the date.

You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

The flow of this modified script is as follows.

Retrieve the value as report with Analytics.Data.Ga.get().
Create object. This is used for calculating "Sessions".
Create array. This is used for putting to Spreadsheet.
Put the array to Spreadsheet.

Modified script:

When your script is modified, it becomes as follows.

From:

var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/FILLER_TEXT/edit#gid=0');
var sheet = spreadsheet.getSheetByName('Data'); 
var firstEmptyRow = sheet.getLastRow() + 1;

sheet.getRange(firstEmptyRow,1, report.rows.length, report.rows[0].length).setValues(report.rows);

To:

// Please set the key words for using as "Web Section".
var searchValues = ["One", "Two"];

// Create object.
var object = report.rows.reduce(function(o, e) {
  var idx = -1;
  var check = searchValues.some(function(f, j) {
    if (e[0].indexOf(f) != -1) {
      idx = j;
      return true;
    }
    return false;
  });
  if (check) {
    var s = searchValues[idx];
    var key = e[0].replace(/\//g, "").split(s).shift().replace(/^[a-z]/g, function(f) {return f.toUpperCase()}) + " " + s +  "_" + e[1];
    o[key] = key in o ? o[key] + Number(e[2]) : Number(e[2]);
  } else {
    var others = "Others_" + e[1];
    o[others] = others in o ? o[others] + Number(e[2]) : Number(e[2]);
  }
  return o;
}, {});

// Create array.
var array = Object.keys(object).map(function(e) {return e.split("_").concat(object[e])});
array.sort(function(a, b) {return (a[0] < b[0] ? -1 : 1)});

// Put array to Spreadsheet.
var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/FILLER_TEXT/edit#gid=0');
var sheet = spreadsheet.getSheetByName('Data'); 
var firstEmptyRow = sheet.getLastRow() + 1;
sheet.getRange(firstEmptyRow,1, array.length, array[0].length).setValues(array);

For checking script:

var report = {rows: [["/",20191228,100],["/locationOne",20191228,10],["/locationTwo",20191228,1],["/locationOne?s=a",20191228,10],["/locationOne",20191229,10]]};

// Please set the key words for using as "Web Section".
var searchValues = ["One", "Two"];

// Create object.
var object = report.rows.reduce(function(o, e) {
  var idx = -1;
  var check = searchValues.some(function(f, j) {
    if (e[0].indexOf(f) != -1) {
      idx = j;
      return true;
    }
    return false;
  });
  if (check) {
    var s = searchValues[idx];
    var key = e[0].replace(/\//g, "").split(s).shift().replace(/^[a-z]/g, function(f) {return f.toUpperCase()}) + " " + s +  "_" + e[1];
    o[key] = key in o ? o[key] + Number(e[2]) : Number(e[2]);
  } else {
    var others = "Others_" + e[1];
    o[others] = others in o ? o[others] + Number(e[2]) : Number(e[2]);
  }
  return o;
}, {});

// Create array.
var array = Object.keys(object).map(function(e) {return e.split("_").concat(object[e])});
array.sort(function(a, b) {return (a[0] < b[0] ? -1 : 1)});
	
console.log(array);

Note:

In this modified script, in order to search the key words for using as "Web Section", I used var searchValues = ["One", "Two"];. Because I'm not sure the patterns of locationOne, locationTwo. So in this modified script, at first, please set this.
Unfortunately, I'm not sure whether 20191228 and 100 of ["/",20191228,100] are the number or the string. So I used Number(e[2]) in the modified script.
If you don't want to sort the array, please remove array.sort(function(a, b) {return (a[0] < b[0] ? -1 : 1)});.

References:

If I misunderstood your question and this was not the direction you want, I apologize. At that time, can you provide more sample values and output you expect? By this, I would like to confirm it.

Added:

You want to use One, Two as the search values.
You want to use First Output, Second Output as the name which is used for putting the Spreadsheet.

About your additional question, I could understand like above. If my understanding is correct, how about the following sample script?

// Please set the key words and names for using as "Web Section".
var searchValues = {
  search: ["One", "Two"],
  name:   ["First Output", "Second Output"]
};

// Create object.
var object = report.rows.reduce(function(o, e) {
  var idx = -1;
  var check = searchValues.search.some(function(f, j) {
    if (e[0].indexOf(f) != -1) {
      idx = j;
      return true;
    }
    return false;
  });
  if (check) {
    var key = searchValues.name[idx] +  "_" + e[1];
    o[key] = key in o ? o[key] + Number(e[2]) : Number(e[2]);
  } else {
    var others = "Others_" + e[1];
    o[others] = others in o ? o[others] + Number(e[2]) : Number(e[2]);
  }
  return o;
}, {});

// Create array.
var array = Object.keys(object).map(function(e) {return e.split("_").concat(object[e])});
array.sort(function(a, b) {return (a[0] < b[0] ? -1 : 1)});

// Put array to Spreadsheet.
var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/FILLER_TEXT/edit#gid=0');
var sheet = spreadsheet.getSheetByName('Data'); 
var firstEmptyRow = sheet.getLastRow() + 1;
sheet.getRange(firstEmptyRow,1, array.length, array[0].length).setValues(array);

In this sample script, please correspond the index of search and the index of name in searchValues.
In above case, the values searched with One and Two use the name of First Output and Second Output, respectively.

source: stackoverflow.com

← Previous Post