Consolidate data between 2 spreadsheets and delete data in source sheet with Google Apps Scripts

Question

I've been browsing different forums & stackoverflow to find the solution to the problem in my script. Let me explain:

I have 2 distinct spreadsheets each containing one sheet. I want to consolidate the data from "Spreadsheet1 - Sheet 1" to "Spreadsheet2 - Data". My logic was that data from Spreadsheet1 are copied to Spreadsheet2 (this works perfectly). However, I want also my script to delete the data found in Spreadsheet1 and that is where I'm blocked. I'm trying to use the method "deleteRows()" but this seems not to work properly.

Here's my script:

function Consolidate() {
   var source = SpreadsheetApp.openById('Spreadsheet1_unique_id');
   var sourcesheet = source.getSheetByName('Sheet1');

   var target = SpreadsheetApp.openById('Spreadsheet2_unique_id')
   var targetsheet = target.getSheetByName('Data');
   var targetrange = targetsheet.getRange(targetsheet.getLastRow(), 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
   var rangeValues = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).getValues();
   targetrange.setValues(rangeValues);
   sourcesheet.deleteRows(2, sourcesheet.getLastRow());
}

The debugging feature with my Google Apps Script indicates "Those rows are out of bounds". What does that mean in this case?

Through my searches, I understood that the second parameter that I defined within deleteRows() doesn't represent a range of rows but just one row. Knowing that I tried to find a way to get the right thing. However every attempt was concluded by a non-working script. Did I get that correctly or I'm just mistaken?

Does someone has an idea on how to reach the expected result?

Thanks a lot for your help!

Answer 1

I ran a few tests in a test spreadsheet and by simply adding a -1 (since you start a line 2) It should resolve your issue.

function Consolidate() {
var source = SpreadsheetApp.openById('Spreadsheet1_unique_id');
var sourcesheet = source.getSheetByName('Sheet1');

var target = SpreadsheetApp.openById('Spreadsheet2_unique_id')
var targetsheet = target.getSheetByName('Data');
var targetrange = targetsheet.getRange(targetsheet.getLastRow(), 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
var rangeValues = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).getValues();
targetrange.setValues(rangeValues);
sourcesheet.deleteRows(2, sourcesheet.getLastRow()-1);
}

Hope this helps. If any question I will answer in the comments.