Use a dynamic notation into a query function

Question

I need to create a generic formula to get a value from a query but I do not know exactly where the range of data to be informed is. I can not name the range because we have many tabs and the tabs are doubled as needed.

I used the MATCH function to know what the data range is but it does not work with the QUERY command, which will be used for the filters I need.

Something like this:

=query(B78:BV129;"select B, C")//- worked

=query("B"&MATCH("Ini";B:B;0)&":"&"BV"&MATCH("End";B:B;0);"select B, C")     

The spreadsheet didn't recognize B or C or Col1 or Col2 or whatever parameter

Another way:

Script:

 function getRange(rr){
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().captura.getRange(rr);
  return range;
 }

=query(getRange("B78:BV129");"select Col1, Col2")

Didn't work with the same error

Using only script with loop to identify the range was very slow and became unfeasible.

Has anyone ever needed to use the query command this way? Any workarounds?

Answer 1

• Use INDIRECT:

  =query(INDIRECT("B"&MATCH("Ini";B:B;0)&":"&"BV"&MATCH("End";B:B;0));"select B,Z....
  

• Script should return values:

  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(rr);// captura?
  return range.getValues(); } //added getValues