How to write Google Analytics API data to a new row, rather than a new sheet

1
votes

I am trying to alter the below script to write Google Analytics data to a new row in an existing sheet (the only sheet within my Google Sheet), but am not having any success. I am new to Google Apps Scripting, so any help is much appreciated.

I have tried replacing "var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();" with "var sheet = SpreadsheetApp.getActiveSpreadsheet().appendRow();", but am getting errors.

function outputToSpreadsheet(results) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();

  // Print the headers.
  var headerNames = [];
  for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {
    headerNames.push(header.getName());
  }
  sheet.getRange(1, 1, 1, headerNames.length)
      .setValues([headerNames]);

  // Print the rows of data.
  sheet.getRange(2, 1, results.getRows().length, headerNames.length)
      .setValues(results.getRows());
}
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes
  • You want to put the value to the new row in the existing sheet in a Spreadsheet.
  • There is only one sheet in the Spreadsheet.

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

Modified script:

Please modify 2 parts as follows.

var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

And

sheet.getRange(2, 1, results.getRows().length, headerNames.length)
    .setValues(results.getRows());

sheet.getRange(sheet.getLastRow() + 1, 1, results.getRows().length, headerNames.length)
    .setValues(results.getRows());

References:

If I misunderstood your question and this didn't work, I apologize.