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. — Marios, Feb 04 '21 at 16:28
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. — Digital Farmer, Feb 04 '21 at 16:36
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. — Marios, Feb 04 '21 at 16:37
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? — Digital Farmer, Feb 04 '21 at 16:40
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?](https://stackoverflow.com/questions/11947590/sheet-getrange1-1-1-12-what-does-the-numbers-in-bracket-specify) — Marios, Feb 04 '21 at 16:42

score 1 · Accepted Answer · answered Feb 04 '21 at 16:43

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 Answers1

Explanation: