google apps script - Copy column values from one spreadsheet to another spreadsheet, adding only the necessary rows

Question

Sheet 1 / Sheet 2

I use this formula to copy values from one column to another spreadsheet column:

function SheetToSheet() {
  var sss = SpreadsheetApp.openById('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
  var ss = sss.getSheetByName('Sheet 1');
  var range = ss.getRange(12,1,ss.getMaxRows()+1-12,1);
  var data = range.getValues();
  var tss = SpreadsheetApp.openById('BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
  var ts = tss.getSheetByName('Sheet 2');
  ts.getRange(12,1, data.length, data[0].length).setValues(data);
}

In this case where I want to collect the values from the line 12, use ss.getMaxRows()+1-12in case it is necessary to add rows in the other spreadsheet, it will add literally only as many rows as necessary.

Result:
Add Row 11, 12, 13, 14 and 15
Add Values in Row 12, 13, 14 and 15

If I use getlastrow(), several more unnecessary lines are added:

If I use the getMaxRows() without calculating +1-NumberOfRows, it always copies more lines than necessary too:

Is there a fixed model that is not always necessary to place the calc +1-NumberOfRows? I am afraid to forget to change the value for the calculation and to add erroneous amounts of lines.

question from:https://stackoverflow.com/questions/66049629/copy-column-values-from-one-spreadsheet-to-another-spreadsheet-adding-only-the

Answer 1

Explanation:

The setValues function will add the absolute necessary number of rows so the data can fit in. It won't drop an error nor add more rows than needed.

Let's say you want to start pasting from row 1 in the target sheet and you want to paste 10 rows of data but the target sheet has only 6 rows. The setValues function will create more rows so your data can fit in exactly.

Minimal Reproducible Example:

Sheet1

Let's say you have 10 rows of data in Sheet1:

Sheet2

Sheet2 has only 6 rows available:

if you run this code:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet1');
  const sh2 = ss.getSheetByName('Sheet2');
  const data = sh1.getRange('A1:A10').getValues();
  sh2.getRange(1,1,data.length,data[0].length).setValues(data);
}

setValues will do the job and add the absolute necessary rows needed to fit the data in.

Sheet2 (after the script is executed)