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<sheets.length ; i++) {
var name = sheets[i].getName();
var rege = /[0-9]+/;
if((!name.search(rege))) { out.push(sheets[i].getName()); }
}
for (var i=0 ; i<out.length ; i++) {
if(i<out.length-1) {
out_scalar = out_scalar + "'" + out[i] + "'!A5:K100; ";
} else {
out_scalar = out_scalar + "'" + out[i] + "'!A5:K100 ";
}
}
return "{ " + out_scalar + " }"
}