Script not returning Cell Display Values?

[Clive Gray]

I'm trying to run a script which saves the display values of an IF statement as a log to a separate worksheet. Rather than returning the display values I'm getting entries like  [Ljava.lang.Object;@643e733b.

Here is the code...

Any help greatly appreciated

Many thanks

[CBMServices Web]

The getValues method returns a 2 dimensional array. (i.e. a table with rows and columns.)

You are putting it in another array which now makes it a 3 dimensional array. 

The the appendRow method is expecting a 1 dimensional array. So it treats what is inside that first array as an object instead. This is why it gives you what it did.

Not knowing what that named range looks like, I can not help you further. But hopefully this gives you an idea on how to address the problem. 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ccab939d-0c3e-42c1-ac6b-d8ff26dc943bn%40googlegroups.com.

[Clive Gray]

Hi, thank you very much for your replay.

The named range extends across three specific cells (F5:H5) in the source worksheet (WORKSHEET_NAME_1). These three cells show the output from three columns of data after each column has been filtered (first two columns) or data validated using a drop down (third column). The selections that are shown in the range F5:H5 are based on a hidden column A which indentifies the selected combination of the three columns as unique.

Each of the cells in the range F5:H5 has a similar formula which varies only in column number:

F5 formula: =IF(SUBTOTAL(3,WORKSHEET_NAME_1!$F$7:$F$323)>1,"COLUMN_NAME_1",VLOOKUP(1,$A$1:$F$322,6,FALSE))

G5 formula: =IF(SUBTOTAL(3,WORKSHEET_NAME_1!$F$7:$F$323)>1,"COLUMN_NAME_2",VLOOKUP(1,$A$1:$G$323,7,FALSE))

H5 formula: =IF(SUBTOTAL(3,WORKSHEET_NAME_1!$F$7:$F$323)>1,"COLUMN_NAME_3",VLOOKUP(1,$A$1:$H$323,8,FALSE)) 

I am looking to copy and paste the output of the filtered/data validated columns whihc appears in F5:H5 to a log which is a separate destination worksheet.

Are you able to help further based on the above...?

[cbmserv...@gmail.com]

I see so it is just one column of data that you want then. So that makes it easier to convert it to something appendRow can use.

 

Change the statement that says:

 

Var data = [… sourceVals];

 

To this:

 

var data = sourceRange.map(function(r){return r[0];);

 

that should do the trick to change array from 2 dimensions to 1 which is want you need to output.

 

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of Clive Gray
Sent: Monday, June 12, 2023 12:01 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: Re: [Apps-Script] Script not returning Cell Display Values?

 

Hi, thank you very much for your replay.

 

The named range extends across three specific cells (F5:H5) in the source worksheet (WORKSHEET_NAME_1). These three cells show the output from three columns of data after each column has been filtered (first two columns) or data validated using a drop down (third column). The selections that are shown in the range F5:H5 are based on a hidden column A which indentifies the selected combination of the three columns as unique.

 

Each of the cells in the range F5:H5 has a similar formula which varies only in column number:

F5 formula: =IF(SUBTOTAL(3,WORKSHEET_NAME_1!$F$7:$F$323)>1,"COLUMN_NAME_1",VLOOKUP(1,$A$1:$F$322,6,FALSE))

G5 formula: =IF(SUBTOTAL(3,WORKSHEET_NAME_1!$F$7:$F$323)>1,"COLUMN_NAME_2",VLOOKUP(1,$A$1:$G$323,7,FALSE))

H5 formula: =IF(SUBTOTAL(3,WORKSHEET_NAME_1!$F$7:$F$323)>1,"COLUMN_NAME_3",VLOOKUP(1,$A$1:$H$323,8,FALSE)) 

 

I am looking to copy and paste the output of the filtered/data validated columns whihc appears in F5:H5 to a log which is a separate destination worksheet.

 

Are you able to help further based on the above...?

On Monday, 12 June 2023 at 17:34:15 UTC+1 CBMServices Web wrote:

The getValues method returns a 2 dimensional array. (i.e. a table with rows and columns.)

 

You are putting it in another array which now makes it a 3 dimensional array. 

 

The the appendRow method is expecting a 1 dimensional array. So it treats what is inside that first array as an object instead. This is why it gives you what it did.

 

Not knowing what that named range looks like, I can not help you further. But hopefully this gives you an idea on how to address the problem. 

 

On Mon., Jun. 12, 2023, 5:13 a.m. Clive Gray, <cliveg...@gmail.com> wrote:

I'm trying to run a script which saves the display values of an IF statement as a log to a separate worksheet. Rather than returning the display values I'm getting entries like  [Ljava.lang.Object;@643e733b.

 

Here is the code...

 

Any help greatly appreciated

 

Many thanks

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ccab939d-0c3e-42c1-ac6b-d8ff26dc943bn%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/531d93d0-3255-492b-84d5-2a9d9f2c5078n%40googlegroups.com.

[Clive Gray]

Hi, thanks again...I'm getting a syntax error with that statement?

[cbmserv...@gmail.com]

Yeah missing curly bracket..

 

var data = sourceRange.map(function(r){return r[0];});

 

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/93a52086-3645-4ff0-a6d5-0f2191085b5dn%40googlegroups.com.

[Clive Gray]

It runs with this error?

TypeError: sourceRange.map is not a function

Looking back at your reply with the revised script statement, and to clarify, the data is in THREE columns and I'm trying to save the filtered output from those three columns which appears in a three cells range...not one column? 

[cbmserv...@gmail.com]

Ok you will need to show me the rest of the code again.

 

Map is an array method, so sourceRange must be defined as an array to work correctly.

 

Can you share a sample spreadsheet or something with the software?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/edbbfb9e-d867-4881-a3b6-8655ad29e2d8n%40googlegroups.com.