Reputation: 135
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);
}
Upvotes: 0
Views: 9916
Reputation: 507
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.
Upvotes: 1
Reputation: 394
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);
}
Upvotes: 2
Reputation: 4907
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!
Upvotes: 0