0

Thanks to everybody.

I've got a Google spreadsheet containing 7 sheets. I'm trying to move data in the last sheet from cells A1:D1 to a new row appended to the bottom of the same sheet.

Here is the snippet of code I'm using:

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[6];
  var src = sheet.getRange('PaycheckHistory!A1:D1').getValues();
  sheet.appendRow([src]);

After I run the code, on the tab "PaycheckHistory", in a new row appended to the bottom of the sheet, I get the following: "[Ljava.lang.Object;@3e0d05f9"

Can anybody tell me (a) What is this error, (b) What does this mean, and (c) How do I fix this or perform my goal, which is to "move data in the last sheet from cells A1:D1 to a new row appended to the bottom of the same sheet."

Thanks, all!

6/22/18 IDK if this will help, but cannot find an "update this question" button on the screen showing my original post. I DID, however, find an "edit" button, and I'm using that now.

Here's the complete code that I'm using: (apologies if the code is not formatted properly)

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Tasks')
     .addItem('createCopy','createCopy')
      .addToUi();
}

function createCopy() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[6];
  var src = sheet.getRange('PaycheckHistory!A1:D1').getValues();
  sheet.appendRow(src[0]);

  SpreadsheetApp.flush();

  var myValue =

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("O58").getValue(); var destinationFolder = DriveApp.getFolderById("1errc7- 2sM**********ZPKJIxjZOTRWo");

DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).makeCopy(myValue,destinationFolder);

}

Here's the problem I'm getting: The first part of the function (ABOVE "SpreadsheetApp.flush();") moves data on sheet 6 of the spreadsheet.The next part of the function (BELOW "SpreadsheetApp.flush();") copies the entire spreadsheet, puts the copy in a specified folder, and renames the copy.

I want this function to do more things on other tabs/sheets in the spreadsheet file. I can write additional code to do this, but when I add the new code to the existing function above, the new code does nothing. Simply using "SpreadsheetApp.flush();" does not work.

user3279926
  • 59
  • 2
  • 12

1 Answers1

0

Answer to (a)

Value retrieved by getValues() is 2 dimensional array. When it sees the usage of appendRow(rowContents), rowContents is 1 dimensional array. In your script, when this sheet.appendRow([src]) is run, the 3 dimensional array is used. So such string is put.

Answer to (b)

About [Ljava.lang.Object;@, you can see the information at the following threads.

Answer to (c)

In order to avoid such string, in the case of your script, please modify as follows.

From : 
sheet.appendRow([src]);
To : 
sheet.appendRow(src[0]);

Reference :

If I misunderstand your question, I'm sorry.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Tanaike - Thank you for a wonderful solution! The only problem that I have is that the other parts of the script, that worked prior to adding your code, now no longer work. Only your code performs as intended. How can I get everything to work togrther? (Note: the other pieces of code act on different sheets or sometimes on the entire spreadsheet (All 7 tabs). – user3279926 Jun 18 '18 at 00:54
  • OOPS - Used wrong word - not "only problem I have", actually "most immediate problem that I have"... – user3279926 Jun 18 '18 at 00:55
  • @user3279926 I'm sorry for the inconvenience. I couldn't understand ``the other parts of the script``. I'm really sorry for my poor English skill. – Tanaike Jun 18 '18 at 01:00
  • I separated each "clump" of code with a "SpreadsheetApp.flush();" statement. That made everything work properly. If I need to copy and paste values a second time, but with a different cell range on a different sheet, how can I do that? – user3279926 Jun 20 '18 at 01:43
  • @user3279926 Unfortunately, I'm sorry I couldn't find "clump" and "SpreadsheetApp.flush();" in your question. And I cannot understand your latest situation. Can you provide your latest script? Because my answer is for your current question. I'm really sorry for my poor skill. – Tanaike Jun 20 '18 at 02:28
  • function createCopy() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[6]; var src = sheet.getRange('PaycheckHistory!A1:D1').getValues(); sheet.appendRow(src[0]); SpreadsheetApp.flush(); var myValue = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("O58").getValue(); var destinationFolder = DriveApp.getFolderById("1errc7-2sM**********ZPKJIxjZOTRWo"); DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).makeCopy(myValue,destinationFolder); } – user3279926 Jun 20 '18 at 03:32
  • Tanaike, IDK how to post my code other than to put it in a comment. – user3279926 Jun 20 '18 at 03:33
  • One thought - in the line: "SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("O58").getValue(); var destinationFolder = DriveApp.getFolderById("1errc7-2sM**********ZPKJIxjZOTRWo");" should I somehow identify that cell O58 is on sheet 0? IOf this is the problem, then how would I do this? – user3279926 Jun 20 '18 at 03:34
  • @user3279926 Could you please add the script by updating your question? Because when the script is written in the comment, it is difficult for users including me to read. When you update your question, in order to understand your situation, please also add the explanation about the detail information of your current situation. By this, it will help users including me think of your solution. – Tanaike Jun 20 '18 at 04:19
  • Tanaike, I have tried to do as you ask, but the format that Stack Overflow usses seems to be very difficult to use. If I need to, I will post my complete code as a new question. If I do this, I will edit this question so that you are aware of what I'm doing. – user3279926 Jun 22 '18 at 18:49
  • @user3279926 I'm really sorry my answer was not useful for you. – Tanaike Jun 22 '18 at 22:51