CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Abhay
Abhay

Reputation: 845

Data manipulation with google apps script

I'm trying to pull data from google analytics, do some data manipulations to the data and paste the formatted data in google sheets. I know how to pull data and I know how to paste it in google sheets - I'm not sure how to do the manipulations I need to do (If I was using Python, I'd use the Pandas library to do what needs to be done but I'm lost with javascript/google apps script)

I've been googling to find out how to manipulate data in google apps script but I can't find anything helpful.

This is my code so far, it can pull the data and paste it into a google sheet correctly:

function updateReport() {

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

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

  var metrics = 'ga:sessions, ga:transactions, ga:transactionRevenue';
  var optionalArgs = {  
    'dimensions': 'ga:date, ga:source, ga:medium, ga:campaign',
  };

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


  if (report.rows) {

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName('Data'); 
    var firstEmptyRow = sheet.getLastRow() + 1;

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

}

The script that I shared will give a result like this:

Date     Source Medium Campaign Sessions Transactions Revenue
20190826 Facebook cpc   Brand     100       10         1,000
20190826 Facebook cpc   Generic   110       20         2,000
20190826 Google   cpc   Brand     120       30         3,000
20190826 Google   cpc   Generic   130       40         4,000
20190826 Google   cpc   Brand     140       50         5,000
20190826 Google   cpc   Generic   150       60         6,000

This is the result that i'm trying to get insted:

Date            Channel       Sessions Transactions Revenue
20190826    Facebook - Brand    100       10        1,000
20190826    Facebook - Generic  110       20        2,000
20190826    Google - Brand      260       80        8,000
20190826    Google - Generic    280      100       10,000

Using pseudocode, this is what it might look like:

if Source == 'Facebook' and Medium == 'cpc' and Campaign == 'Brand':
    return 'Facebook - Brand'

elif Source == 'Facebook' and Medium == 'cpc' and Campaign == 'Generic':
    return 'Facebook - Generic'

elif Source == 'Google' and Medium == 'cpc' and Campaign == 'Brand':
    return 'Google - Brand'

else Source == 'Google' and Medium == 'cpc' and Campaign == 'Generic':
    return 'Google - Generic'

I'd appreciate any help that I can get here!

Upvotes: 0

Views: 1896

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

To do this kind of data manipulation, you need to first proceed report.rows with JavaScript loops and condition statements before you set values into the spreadsheet.

Sample:

  if (report.rows) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Data'); 
  var bucket=[];
  //starting with the second row, since your first row are headers
  for(var i=1; i<report.rows.length;i++){
    var newRow=[];
    if((report.rows[i][1]=='Facebook'||report.rows[i][1]=='Google')&&report.rows[i][2]=='cpc'&&(report.rows[i][3]=='Brand'||report.rows[i][3]=='Generic')){
      newRow.push(report.rows[i][0]);
      newRow.push(report.rows[i][1]+' - '+report.rows[i][3]);
      for(var j=4; j<report.rows[0].length;j++){
        newRow.push(report.rows[i][j]);
      }
      var firstEmptyRow = sheet.getLastRow() + 1;      
      sheet.getRange(firstEmptyRow,1, 1, newRow.length).setValues([newRow]);
     }
    else{
      bucket.push(report.rows[i]);
     }
   }
}

Upvotes: 1

Related Questions