Output API data to a csv file

Question

I have Frankenstined bits of code together found on here and on various Google tutorials. My goal is to get data from the Google Analytics API and output it to a file in my Google Drive "my_data.csv".

I am able to get my data from the GA API in a variable "results". The default output of results variable is an object (I think?) and looks like this:

Logger.log(results);

dataTable={cols=[{id=ga:year, label=ga:year, type=string}, {id=ga:month, label=ga:month, type=string}, {id=ga:medium, label=ga:medium, type=string}, {id=ga:source, label=ga:source, type=string}, {id=ga:campaign, label=ga:campaign, type=string}, {id=ga:goal1Completions, label=ga:goal1Completions, type=number}], rows=[{c=[{v=2015}, {v=06}, {v=(none)}, {v=(direct)}, {v=(not set)}, {v=117}]}, {c=[{v=2015}, {v=06}, {v=(not set)}, {v=(not set)}, {v=(not set)}, {v=0}]}, {c=[{v=2015}, {v=06}, {v=(not set)}, {v=ET}, {v=DL}, {v=0}]}, {c=[{v=2015}, {v=06}, {v=(not set)}, {v=a1e88567-3904-4375-a14a-cff82d7613d2}, {v=wda}, {v=0}]},

Or I can change the output 'output':'dataTable' in the API call. The results then look like:

Logger.log(results);

rows=[[2015, 06, (none), (direct), (not set), 117], [2015, 06, (not set), (not set), (not set), 0], [2015, 06, (not set), ET, DL, 0], [2015, 06, (not set), a1e88567-3904-4375-a14a-cff82d7613d2, wda, 0], [2015, 06, (not set), google_plus, DL, 0], [2015, 06, Display, Facebook, Facebook, 0], [2015, 06, Display, GOOGLE, ReTargeting, 0], [2015, 06, ET, Email, SearchTopStories, 1], [2015, 06, affiliate, cj, 1651317, 0],

So there are two ways I can ouput the data. Currently I do not know which is "best" wrt to getting my data into a csv. For now I'm trying with the dataTable structure.

Here is my code:

// set some global vars
var profile = 'ga:XXXXXXXX'; // my actual id added here
var my_csv = 'my_csv';

//trigger function runs everything else
function getToIt() {

    var results = getReportDataForProfile(profile);
    convertRangeToCsvFile_(results, "my_data");
}

// get GA data
function getReportDataForProfile(profile) {

  var startDate = getLastNdays(1);
  var endDate = getLastNdays(0);

  var optArgs = {
    'dimensions': 'ga:year,ga:month,ga:medium,ga:source,ga:campaign', // Comma separated list of dimensions.
    'start-index': '1',
    'max-results': '10',
    'output':'dataTable'
    //'samplingLevel': 'higher-precision'
  };

  // Make a request to the API.
  var results = Analytics.Data.Ga.get( // mcf for multi channel api, Ga for core
      profile,                    // Table id (format ga:xxxxxx).
      startDate,                  // Start-date (format yyyy-MM-dd).
      endDate,                    // End-date (format yyyy-MM-dd).
    'ga:goal1Completions', // Comma seperated list of metrics.
      optArgs);

  Logger.log(results);

  if (results.getRows()) {
    return results;
  }
}

// child function for setting date range
function getLastNdays(nDaysAgo) {
  var today = new Date();
  var before = new Date();
  before.setDate(today.getDate() - nDaysAgo);
  return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}

// convert results to csv
function convertRangeToCsvFile_(data, file_name) {

  Logger.log("data here:" + data);
  var csv = google.visualization.dataTableToCsv(data);  

  //output to drive
  DriveApp.createFile(file_name, csv);
  return csvFile;
}

The error message I receive just now is

ReferenceError: "google" is not defined

Looks like my issue is right here:

var csv = google.visualization.dataTableToCsv(data); 

How do I create the variable csv? I'm not sure how to import the google.visualization module given my environment is GAS. I'm not even sure that it's the right approach.

Answer 1

Here is a very basic 2d array to csv converter. Also you can't use the JSAPI library on the server side it will not work.

function arrayToCsv(data){
var csv = ""
var eol = '\r\n';
  for(var i = 0; i<data.length;i++){
     csv += data[i].join();
     csv += eol;
    }
  return csv;
}


function t(){
var data = [[1,2,3],[4,5,6],[7,8,9]];
 data.unshift(["title1","title2","title3"]); // add my header array;
  var newcsv  = arrayToCsv(data);
  Logger.log(newcsv);
  var backToData = Utilities.parseCsv(newcsv);
  Logger.log(backToData);


}