appendRow returns [Ljava.lang.Object;@ instead of values

user_14354387 0
Viewed 1134

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.

google-apps-script google-sheets javascript
3 Answers

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 V80?

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] ]).

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

user_114076950

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.

user_5121270

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);
}
user_112252910
Related