How could I call a custom function within a QUERY formula on Google Sheets?

Question

Would it be something like this?

=query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/fsdgsgsdhdshldhsdhgvs/edit","Reference Info!A2:J"),"select * where Col2 matches '"&fileName()&"'")

Here is the fileName() function:

//Since I'm not sure fileName() will run as the recalculations happen, I've added this one
function onOpen(){
fileName();
}

function fileName() {
  return SpreadsheetApp.getActiveSpreadsheet().getName();
}

Thanks for any help!

score 1 · Accepted Answer · answered Aug 06 '22 at 19:45

Your formula is fine, but it doesn't make sense to have

function onOpen(){
fileName();
}

as custom functions are executed when the spreadsheet is opened and when the custom function arguments change.

How could I call a custom function within a QUERY formula on Google Sheets?

1 Answers1