0

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.

  • can you share a copy of your sheet ?? – user0 Apr 24 at 12:15
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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