How do I write a script correctly for GSheets that copy and pastes different ranges without errors?

Question

I have a script that works well to copy and paste a range from example Sheet1 range: A1:C200 to Sheet2 range: A1:C200. My problem is my rows constantly vary from 200 - 3000 rows depending on the data I have imported daily into my spreadsheet and at the moment I'm constantly having to manually write each range.

Is it possible to write a script that would adapt to a variable range of rows each time and copy an entire range from columns A,B,C to the last row of available data like the range formula: A1:C? See script example.

Original Script: (works)

function copy() {  
  var OriginalRange=SpreadsheetApp.openById('Spreadsheet ID').getSheetByName('Sheet1').getRange("A1:C200");
  var DestinationRange=SpreadsheetApp.openById('Spreadsheet ID').getSheetByName('Sheet2').getRange("A1:C200");
DestinationRange.setValues(OriginalRange.getValues());

}

Ideal variable range Script: (exception: does not match the number of rows errors)

function copy() {  
  var OriginalRange=SpreadsheetApp.openById('Spreadsheet ID').getSheetByName('Sheet1').getRange("A1:C");
  var DestinationRange=SpreadsheetApp.openById('Spreadsheet ID').getSheetByName('Sheet2').getRange("A1:C");
DestinationRange.setValues(OriginalRange.getValues());

}

[This answer](https://stackoverflow.com/a/17637159/1907765) has a pretty neat way of getting the last row from a given column. From there all you need to do is grab the reference of that row and use it to get the range you want to select. — Lou, Dec 09 '20 at 09:08

score 1 · Accepted Answer · answered Dec 09 '20 at 09:10

You just need getLastRow() to get the last row with content. You just need to restructure the code a little bit:

function copy() {  
  var OriginalSheet = SpreadsheetApp.openById('Spreadsheet ID').getSheetByName('Sheet1')
  var OriginalRange=OriginalSheet.getRange("A1:C"+OriginalSheet.getLastRow());
  var DestinationSheet = SpreadsheetApp.openById('Spreadsheet ID').getSheetByName('Sheet2')
  var DestinationRange=DestinationSheet.getRange("A1:C"+OriginalSheet.getLastRow());
  DestinationRange.setValues(OriginalRange.getValues());
}

Careful:

This approach will only work if in the range the last row with content is indeed within the columns A:C.

How do I write a script correctly for GSheets that copy and pastes different ranges without errors?

1 Answers1