1
=QUERY({
     importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0","SUN!A3:AB5001");
     importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew","SEA!A3:AB5001");
     importrange("14oCp6aM-2Yo5uaRhXHxWJQ2RY1TLPn-G0HUmfXAwE3g","BUR!A3:AB5001")
}, "select * where Col26 contains 'Yes'",0)

I am at a loss on how to get this function to operate. I have a similar use case with importing from two sheets with 22 columns. Error it throws here is "In Array literal, an array literal was missing in one or more rows".

2 Answers 2

1

As Martín explained, chances are that this is an importrange() authorization issue. In order to grant the necessary permission to importrange(), you need to unwrap it so that the import gets initiated from outside the query(), like this:

=importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0", "A1")

If the new formula cell displays #REF! and a red corner flag, click it to authorize the import. Repeat that with all the imports. After that, your existing formulas that use importrange() should start working as well. You can delete the new formula cells after that.

In the event none of the imports have been authorized, this pattern may be of use:

=lambda( 
  range1, range2, range3, statement, 
  { 
    query(range1, statement, 0); 
    query(range2, statement, 0); 
    query(range3, statement, 0) 
  } 
)( 
  importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0", "SUN!A3:AB"), 
  importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew", "SEA!A3:AB"),
  importrange("14oCp6aM-2Yo5uaRhXHxWJQ2RY1TLPn-G0HUmfXAwE3g", "BUR!A3:AB"), 
  "where Col26 contains 'Yes' "
)
1

Try a simple IMPORTRANGE without the query for each one of your sheets. The most probable thing that is happening is that some of them wasn't linked before. Accept the prompt to link each one of the sheets and try the QUERY again

1
  • This was it. Thank you! Here I was doublechecking parenthesis and commas 1000x Jan 20, 2023 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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