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!

Answer 1

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.

Related

• Refresh data retrieved by a custom function in Google Sheet
• get sheet names not work properly or not auto update after use loop to copy to new sheets