I am using this code to generate Analytics reports on a Google spreadsheet.

The problem is that it overwrites data every time on execution.

What should I change, so that data gets append? So that the same spreadsheet is updated every day?

  const report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,
      options);

  if (report.rows) {
  var prop = PropertiesService.getScriptProperties();
  var spreadsheetId = prop.getProperty("spreadsheetId");
  var spreadsheet;
  
  if (spreadsheetId) {
    spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  } else {
    spreadsheet = SpreadsheetApp.create('Google Analytics Report');
    prop.setProperty("spreadsheetId", spreadsheet.getId());
  }
  var sheet = spreadsheet.getActiveSheet();
  sheet.clear();  // Clear sheet.

  // Append the headers.
  const headers = report.columnHeaders.map((columnHeader) => {
    return columnHeader.name;
  });
  sheet.appendRow(headers);

  // Append the results.
  sheet.getRange(2, 1, report.rows.length, headers.length).setValues(report.rows);

  Logger.log('Report spreadsheet created: %s', spreadsheet.getUrl());
} else {
  Logger.log('No rows returned.');
}
}

CodePudding user response：

You have to replace the first parameter (that is 2) in getRange method with report.getLastRow() to obtain the first row empty to append the new data.

CodePudding user response：

Use the appendRows_() utility function. You can replace the lines starting at sheet.clear() with this:

appendRows_(sheet, report.rows);