Trying to append rows what am I not grasping?

Question

Summary: I need to get a range of values from one sheet, then set them into the first empty row of the second sheet. If that row is full, then append to the next.

I can get the script to set values into their corresponding cells, but not append; and I've been able to append a single cell value into an empty cell, but not the entire range. What am I not comprehending?

The goal: Basically I'm taking user input (lets say race data) and saving it to another sheet for referencing later. (possibly to average race times by weight and height or something)

Research: I'm a bit new to this, but I've also done a good bit of research, such as ben collins free introductory course and other questions from here (just to name a couple):

[Get Range] SpreadsheetApp: getRange and setValues
[Copy Range] How to copy row ranges in Google docs?

Code Grab my data:

const data1 = sheet1.getRange("A2:F2").getValues(); //grab data

Sets the range where I want it, but doesn't append:

sheet2.getRange("A2:F2").setValues(data1) //gets range and sets the value

So I've tried:

function r () {sheet2.getRange(newRow,1,1,data1.length).setValues(data1)};
return (r);

and even added:

sheet2.appendRow(data1);

(newRow is essentially getLastRow) The above gives me a row of set values, then appends this in the following row: [Ljava.lang.Object;@f2a6014 -which in my research I've found what it is, but I don't understand why it happens.

I know I'm overlooking something or arranging something incorrectly but I cant figure out what it is that I'm not understanding.

Perhaps you are thinking that sheet1 in sheet1.getRange("A2:F2").getValues(); is the name of a sheet. It's not. It's a variable in the program that has to be defined before you can use it. If you understood this, please understand I'm not trying to talk down to you but I can't see why your having any difficulties with this. — Cooper
No worries, I should have mentioned that I had the sheet values defined and added that bit of code to the question. I had a lot of my stuff separated by comments (and study material) so I left those bits out. — Merune Ellelcy

Cooper Cooper · Accepted Answer · 2021-04-08T23:30:14

Using your first two example I wrote this script and it works:

function copydata() {
  const ss=SpreadsheetApp.getActive();
  const sheet1=ss.getSheetByName('Sheet1');
  const sheet2=ss.getSheetByName('Sheet2');
  const data1 = sheet1.getRange("A2:F2").getValues();
  sheet2.getRange("A2:F2").setValues(data1);
}

And this works also assuming that you realize that data1 is only a single row but it's still a two dimensional array so you have to use data1[0] to extract the single dimensional array from the two dimensional array

function copydatatofrom() {
  const ss=SpreadsheetApp.getActive();
  const sheet1=ss.getSheetByName('Sheet1');
  const sheet2=ss.getSheetByName('Sheet2');
  const data1 = sheet1.getRange("A2:F2").getValues();
  sheet2.getRange("A2:F2").setValues(data1);
  sheet2.appendRow(data1[0]);
}

Trying to append rows what am I not grasping?

1 Answers