Using custom function in QUERY in Google Sheets results in "unknown function:"

Question

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 + " }"
}

Answer 1

The main problem is that sheetnames function returns a string while the QUERY's first argument should be a range reference or an array of values.

Another problem, is that Google Sheets errors aren't very clear, but regarding this we can only give feedback to Google. For this use Google Feedback.

The solution is to replace your sheetnames custom function for another that returns an array of values. For this, use JavaScript array methods like concat.

Example

function myCustomFunction(){
  var spreadsheet = SpreadsheetApp.getActiveSpreasheet();
  return [].concat(
    spreadsheet.getRange('20!A5:J100'), 
    spreadsheet.getRange('18!A5:J100'), 
    spreadsheet.getRange('56!A5:J100')
  );
}