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.

Let's say you want to start pasting from row 12 and you want to paste 5 rows of data but the target sheet has only 14 rows. 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. In other words, the data in the target sheet will expand automatically to 17. You don't need to do anything. — soMario
Thanks @Marios , but the problem is that I need to define some value of total lines, in case I use ss.getMaxRows()+1-12, but this option I always need to calculate +1-NumberRow, it was this calculation that I didn't want to need to do every time, but I'm not finding an option, only ss.getLastRow() and ss.getMaxRows() add more lines than necessary. — Brondby IF
why do you need a value of total lines? and what do you mean by total lines? data.length gives the total number of lines to be added. — soMario
In var range = ss.getRange(12,1,ss.getMaxRows()+1-12,1); is there any option that is not necessary to put ss.getMaxRows()+1-12 to define how many total lines will be copied? — Brondby IF
BUT you have it already in your script data.length is the total number of line to be copied. Please read Sheet.getRange(1,1,1,12) what does the numbers in bracket specify? — soMario

soMario soMario · Accepted Answer · 2021-02-04T16:43:29

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)

Copy column values from one spreadsheet to another spreadsheet, adding only the necessary rows

1 Answers

Explanation: