I want to copy form submissions over to a different sheet so that the copied data can be edited without affecting the original submissions.

I have the following code:

function copy2(){
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("from");
  var tracker =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("to");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, col).getValues();

  tracker.appendRow([null,row[0]]);

Using null in appendRow helps you move the info over to the next column. However, it doesn't quite work with the row[0] array. If I remove the null it works fine, but I want the info copied on a column different that the first one.

I want to copy form submissions over to a different sheet so that the copied data can be edited without affecting the original submissions.

I have the following code:

function copy2(){
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("from");
  var tracker =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("to");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, col).getValues();

  tracker.appendRow([null,row[0]]);

Using null in appendRow helps you move the info over to the next column. However, it doesn't quite work with the row[0] array. If I remove the null it works fine, but I want the info copied on a column different that the first one.

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Sep 28, 2020 at 11:44 Marios 27.4k99 gold badges3939 silver badges5858 bronze badges asked Sep 28, 2020 at 11:13 Diego FDiego F 4344 bronze badges 1

• 2 Related: stackoverflow./questions/31582248/… – TheMaster Commented Sep 28, 2020 at 11:54

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 4

Why Ljava.lang.Object?

Because you are using the older Rhino runtime that was written in Java. Hence when something unexpected happens you get a glimpse of the infrastructure GAS is built upon. Now, the java.lang.object is a base class in Java from which other objects, including arrays, are derived.

Since the appendRow method signature's only parameter accepts a one-dimensional array of values, your row[0], which contains an array (see what getvalues method returns), made it to the sheet as a string tag indicating that this was an object at runtime.

What to do in Rhino?

All solutions depend on taking [ null ] as your base array and using concat to append the rest of the first row, something like this: [ null ].concat(row[0]). You can also use push with a simple for loop for better performance.

What to do in V8⁰?

As the other answer mentioned, your best bet is the spread syntax. You can also do a push(...row[0]) to avoid concatenation of arrays (since you immediately use and discard the copy resulting from [ null, ...row[0] ]).

---

⁰ See official docs on how to migrate to V8 to take advantage of new language features and improved speed.

Explanation:

• The approach of using null is clearly a workaround and not a futureproof solution. Namely, if you want to start pasting from column 4 you would have to do [null,null,null,...row[0]] which is not the proper way to do it in my opinion.
• I would advice you to get rid of appendRow and null since you want to paste the data from the second column onwards. Therefore, use setValues() instead.

Replace:

tracker.appendRow([null,row[0]]);

with:

tracker.getRange(tracker.getLastRow()+1,2,1,row[0].length).setValues(row);

---

Complete Solution:

function copy2(){
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("from");
  var tracker =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("to");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, col).getValues(); 
  tracker.getRange(tracker.getLastRow()+1,2,1,row[0].length).setValues(row);
}

The row variable contains an array so you should use the spread operator with appendRow

Replace:

tracker.appendRow([null,row[0]]);

with:

tracker.appendRow([null,...row[0]]);

Make sure your project is enabled for Chrome V8 runtime.