google-apps-scriptgoogle-sheets

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


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());
}

Solution

  • 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.

    From:
    var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
    
    To:
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    

    And

    From:
    sheet.getRange(2, 1, results.getRows().length, headerNames.length)
        .setValues(results.getRows());
    
    To:
    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.