1

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

Errors shown

Errors shown

3
  • pls, share a copy of your sheet and copy of your script
    – user0
    Mar 14, 2019 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? Mar 18, 2019 at 16:04
  • it would... just paste the URL and allow sharing
    – user0
    Mar 18, 2019 at 18:03

1 Answer 1

3

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')
  );
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.