Google Apps Script append data to new spreadsheet

Question

I have a Spreadsheet "X" that collects data that I would like to backup from row 2 to end of sheet once a day into another existing Spreadsheet "Y" appending it to its end. I too would like to delete existing entries in Spreadsheet "X" starting from row 2 . Essentially the Spreadsheet "X" will be the accumulative spreadsheet and considered my backup. My spreadsheet does do most, except it adds to the top of Spreadsheet "Y", and it doesn't append, it just overwrites, so I was looking at the CopyTo or AppendRow functions and was hoping for some advise how to correct.

function myFunction() {

  function getDate(){
  var d = new Date();
  var dateofDay = new Date(d.getTime());
  return Utilities.formatDate(dateofDay, "GMT+2", "MM-dd-yyyy hh:mmm:ss z");
  }

  //get the date from current Spreadsheet
  var ss = SpreadsheetApp.getActiveSheet();
  var dataRange = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn());
  var myData = dataRange.getValues();
  //Open new Spreadsheet & paste the data
newSS = SpreadsheetApp.openById("abcdefg");
newSS.getActiveSheet().getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).setValues(myData);
  //Clear the original sheet except header row
  ss.deleteRows(2, ss.getLastRow()-1);

}

Answer 1

just change the range of source spreadsheet to

var dataRange = ss.getRange(2, 1, ss.getLastRow(), ss.getLastColumn());

then change range of destination spreadsheet to

newSS.getActiveSheet().getRange(newSS.getLastRow()+1, 1, ss.getLastRow(), ss.getLastColumn()).setValues(myData);

here is working code

function myFunction() {

 function getDate(){
 var d = new Date();
 var dateofDay = new Date(d.getTime());
 return Utilities.formatDate(dateofDay, "GMT+2", "MM-dd-yyyy hh:mmm:ss z");
 }

 //get the date from current Spreadsheet
 var ss = SpreadsheetApp.getActiveSheet();
 var dataRange = ss.getRange(2, 1, ss.getLastRow(), ss.getLastColumn());
 var myData = dataRange.getValues();
 //Open new Spreadsheet & paste the data
newSS = SpreadsheetApp.openById("1Y2KsVsNaq_-3FvFhx1J8ac5uthl4Wk0kg9hKkVp-Q8Y");
Logger.log(newSS.getLastRow());
newSS.getActiveSheet().getRange(newSS.getLastRow()+1, 1, ss.getLastRow(), ss.getLastColumn()).setValues(myData);
//Clear the original sheet except header row
ss.deleteRows(2, ss.getLastRow()-1);
}

Answer 2

Please insert following code

newSS.getActiveSheet().insertRows(1, myData.length);

before this line:

newSS.getActiveSheet().getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).setValues(myData);

Hope to help you.

Answer 3

This is because in the second spreadsheet, you are setting values for the range [1,1] to [lastrow,lastcolumn]in this line of code.

newSS.getActiveSheet().getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).setValues(myData);

Instead you have to take the last row of that sheet and add rows from lastrow+1.

For more reference check here. Hope that helps!