CODEWITHSUNDEEP
google-apps-script
David Sheffield
David Sheffield

Reputation: 3

range.copyTo not working as excpected Apps Script

When i use the range.copyTo() method to copy ranges from one spreadsheet to another, i usually will use sheet.getLastRow()+1 when im specifing the destination range. Example:

 var oldRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());

 var newRange = destSheet.getRange(destSheet.getLastRow()+1, 1, 1, sheet.getLastColumn());

If the destSheet does not have any additional or "blank" rows at the bottom, the range.copyTo() method does not copy the data and it does not throw an error message. If I use this same logic and use .getValues() and .setValues() , the new rows will automatically get added to my destSheet.

I was expecting range.copyTo() to create additional rows as well if needed. Is this acting as excepcted or is this a bug?

Upvotes: 0

Views: 1789

Answers (1)

Serge insas
Serge insas

Reputation: 46794

If you add a .setValue('') to your newRange statement you'll see an error message AND more rows will be automatically added to the destination sheet AND data will be copied. Note that you shouldn't define the whole dest.range, only the top left cell is relevant as said in the docs

like this :

  var newRange = sh.getRange(ss.getLastRow()+1, 1).setValue('');
  oldRange.copyTo(newRange)

Anyway, this is not really the best workaround because of the error message (We're sorry. The server encountered an error. Please press "OK" to refresh the sheet.). I guess one can reasonably consider this is a bug and you could raise an issue on this here

EDIT : there is actually a nice workaround that I didn't think of immediately : you can use insertRowAfter(ss.getLastRow()) before the copyTo() and everything works fine. example :

  var oldRange = sh.getRange(1, 1, 1, ss.getLastColumn());
  var newRange = sh.getRange(ss.getLastRow()+1, 1);
  ss.insertRowAfter(ss.getLastRow())
  oldRange.copyTo(newRange)

Of course, if more than 1 row is needed repeat as necessary like in this example :

function copyToTest() {
  var oldRange = sh.getRange(1, 1, 5, ss.getLastColumn());// 5 rows in this example
  var newRange = sh.getRange(ss.getLastRow()+1, 1);
  for(n=0;n<oldRange.getLastRow();++n){ss.insertRowAfter(ss.getLastRow())}
  oldRange.copyTo(newRange)
}

Upvotes: 3

Related Questions