Dynamically reference DATA in Query in Google Sheets

Question

I have a query in a Google Sheet structured like so:

=QUERY({IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")},"SELECT * WHERE Col17='"&ParamProjectKey&"' ORDER BY Col3, Col4 ASC")

This query uses three data arrays using IMPORTRANGE. Each Range comes from an external sheet, and each sheet has a named range called "DATA_Expenses".

The formula works nicely, and I get the results displayed as expected and desired.

What I would like to do is change this section:

{IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")}

I would like it to reference a named range in the current sheet, so that the formula now looks like:

=QUERY(QUERY_DATA_RANGE,"SELECT * WHERE Col17='"&ParamProjectKey&"' ORDER BY Col3, Col4 ASC")

I have been able to create a named cell reference that includes the IMPORTRANGE values exactly as per the original formula, but I can't figure out how to get the QUERY to evaluate it. It seems that the content of QUERY_DATA_RANGE is being passed in as a string, rather than as an evaluated array (if that makes sense). I tried using ArrayFormula to solve this - but with no luck so far.

Answer 1

Google Sheets only has one function that "evaluates" a text value, INDIRECT. It converts a text value into a cell / range reference, unfortunately there isn't something similar for arrays.

Google Apps Script can't evaluate text text values as formulas / arrays either.

One alternative is to add a a formula to calculate the array

={IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")}

Let say that the above formula is added on A1 from to a new sheet called myArray. Then you could name the resulting data range as QUERY_DATA_RANGE and use this name as the first argument of QUERY.

Reference

• Using arrays in Google Sheets