Copying data script is not working anymore

Question

I have been using a script in my google sheet that I found here:

copy data from one sheet to another in google app script and append a row, one small issue

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Copy");
  var pasteSheet = ss.getSheetByName("Paste");

  // get source range
  var source = copySheet.getRange(2,2,12,2);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2);

  // copy values to destination range
  source.copyTo(destination);

  // clear source values
  source.clearContent();
}

When I am running it now it gives me an error:

The coordinates of the target range are outside the dimensions of the sheet.

which wasn't coming up before. Does anyone have an idea why it is doing this?

Answer 1

Issue:

The error is in this line:

var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2);

pasteSheet.getLastRow() returns a row number which is at the very bottom of your sheet. Then you consider 12 rows range but the pasteSheet does not have 12 rows after the last row with content.

Solutions:

Add more rows in the pasteSheet:

or you can use insertRowsAfter:

pasteSheet.insertRowsAfter(pasteSheet.getLastRow(), 12)

Code snippet:

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Copy");
  var pasteSheet = ss.getSheetByName("Paste");

  // get source range
  var source = copySheet.getRange(2,2,12,2);
  // insert rows to make sure you have enough space
  pasteSheet.insertRowsAfter(pasteSheet.getLastRow(), 12)
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2);
   
  // copy values to destination range
  source.copyTo(destination);

  // clear source values
  source.clearContent();
}