4

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.

Marios
  • 26,333
  • 8
  • 32
  • 52
Diego F
  • 43
  • 4

3 Answers3

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

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
  • I want to thank you for providing me with such a detailed and insightful response. Not only was I able to solve my issue, I was also able to understand the reasoning behind it. Knowledge is power, thanks for sharing :) – Diego F Sep 29 '20 at 05:13
  • @DiegoF - thank you, glad to be of help. I know that it can be confusing to see `java.lang.object` to be outputted when you accidentally try to save an array or object to a cell if one is not aware of what is under the hood. Small suggestion - ditch the editor and use a local IDE with `@types/google-apps-script` package installed - you will soon notice the number of errors of this kind went down significantly :) – Oleg Valter is with Ukraine Sep 29 '20 at 13:55
3

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.

Amit Agarwal
  • 10,910
  • 1
  • 32
  • 43
  • Niiice, this got rid of the annoying object error, thanks for the clarifications! It is interesting to note that this approach is not necessarily the most practical though, as mentioned by Marios, since we'd have to add more 'null's to copy on other columns. Thank you sir! – Diego F Sep 29 '20 at 05:06
3

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);
}
Marios
  • 26,333
  • 8
  • 32
  • 52
  • This did the trick!! Smooth and sophisticated!! I agree, it is more functional and practical to only have to modify the column parameter within the getRange() statement. Thanks SO much for taking the time to help me out, I really appreciate it!! – Diego F Sep 29 '20 at 05:00