QUERY() ignores query when combined with array

Question

Please consider the following Google Spreadsheet Formula:

=QUERY(
    ARRAYFORMULA({
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!A1:A"),
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SB1:SB"),
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SA1:SA"),
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!CY1:GP")
    }),
    "SELECT * WHERE Col2 <> 0 AND Col1 IS NOT NULL",1)

The Query expression won't work. I've tried to modify everything with it, but it always yields the same results. Is this caused by the interaction between QUERY() and ARRAYFORMULA, or by {}?

In other cases I would prefer doing this by importing the whole range and selecting the Cols as i please, but keep in mind that the last imported range adds up to 96 Columns, so this solution grows unpractical pretty fast.

Any ideas how to fix this?

Answer 1

Ok, so I've figured it out somehow by adding an extra array {}. No idea why this is working, but hey...

=QUERY({
    ARRAYFORMULA({
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!A1:A"),
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SB1:SB"),
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SA1:SA"),
            IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!CY1:GP")
    })},
    "SELECT * WHERE Col2 <> 0 AND Col1 IS NOT NULL",1)

Answer 2

For this case you don't need to use ARRAYFORMULA at all. Your curly braces array-ify the IMPORTRANGE results, which QUERY can parse.

You should be able to write your formula as:

=QUERY({
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!A1:A"),
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SB1:SB"),
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SA1:SA"),
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!CY1:GP")
},
"SELECT * WHERE Col2 <> 0 AND Col1 IS NOT NULL",1)

Note that plain commas will push the results out in to new columns.

To have the results in a single column or columns as you normally expect to see when using QUERY, substitute semicolons in place of the commas within the curly braces:

=QUERY({
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!A1:A");
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SB1:SB");
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!SA1:SA");
        IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!CY1:GP")
},
"SELECT * WHERE Col2 <> 0 AND Col1 IS NOT NULL",1)

Generic sample that other people can use (kindly hosted by someone else)

Wide-format - note commas

=QUERY(
{IMPORTRANGE("1UDEdV70XTxw7fCFna6wSS7XYkZsSKm5rVgZL3uCsD4k","Sheet1!A1:C"),
IMPORTRANGE("1w5ILzQcW5xokFVFBpcYVumjKXzuLdII0iEjX8P52WiA","Sheet1!A1:C"),
IMPORTRANGE("153DPunGmabPPfLg9wx7VDOZc9GxZTc-sUlkSHk8DDK0","Sheet1!A1:C")},
"Select *",1)

Narrow-format - note semi-colons

=QUERY(
{IMPORTRANGE("1UDEdV70XTxw7fCFna6wSS7XYkZsSKm5rVgZL3uCsD4k","Sheet1!A1:C");
IMPORTRANGE("1w5ILzQcW5xokFVFBpcYVumjKXzuLdII0iEjX8P52WiA","Sheet1!A1:C");
IMPORTRANGE("153DPunGmabPPfLg9wx7VDOZc9GxZTc-sUlkSHk8DDK0","Sheet1!A1:C")},
"Select *",1)

Remember to use each of these IMPORTRANGE samples individually and authorise before trying to embed them in the QUERY.