0

I have a custom function written in Google Script for Sheets named sheetnames(). It returns the names of individual sheets inside a worksheet. When used in a regular cell-like =sheetnames(), it correctly outputs as expected.

example:

=sheetnames() 
{ '20'!A5:J100; '18'!A5:J100; '56'!A5:J100 }

...this is the exact expected behavior and output of the script.

I would like to use it to populate the location of a QUERY statement. I have the QUERY statement

=QUERY( { '20'!A5:J100; '18'!A5:J100; '56'!A5:J100 }, "SELECT * WHERE Col1 IS NOT null AND Col3 IS null ORDER BY Col1, Col9, Col8", 1)

currently. It also works as expected, returning correct results.

Problem is when I use

=QUERY ( sheetnames(), "SELECT * WHERE Col1 IS NOT null AND Col3 IS null ORDER BY Col1, Col9, Col8", 1) 

the statement errors out with #VALUE!

(hover text: ERROR Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col9)

and hovering over the input bar gives

"Unknown function: sheetnames".

What gives? I am thinking the error is because the function is not returning anything to the QUERY function. I can use the custom function in the sheet, I can use the custom function in another builtin function (it works when doing something like =concatenate("test ", sheetnames()) without error), but I can't get it to work when used in the QUERY function!

Yes, the function is somewhat necessary as the sheet names will change over time, and no, I would prefer to do this with an automatic function because the people that are going to be maintaining the sheet over time are not technologically adept enough to be counted on to change the values.

Ideas?

function sheetnames() { var out = new Array(); var out_scalar = ""; var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for (var i=0 ; i

for (var i=0 ; i

return "{ " + out_scalar + " }" }

Errors shown

Errors shown

  • pls, share a copy of your sheet and copy of your script – user0 Mar 14 at 23:41
  • Judging by the lack of responses, I do not think that I am sharing my sheets correctly. Would copying the URL work? – user9105932 Mar 18 at 16:04
  • it would... just paste the URL and allow sharing – user0 Mar 18 at 18:03

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.