Google-sheets – Why does this Sheets Query Work

formulasgoogle sheetsgoogle-sheets-query

I've been trying to learn the Query function in Sheets, and cannot understand why the Query below works. To me, it looks like there would need to be one more " to close the SELECT clause, but it seems that the last " in the IF statement does the trick.

Can someone explain what I'm missing here?

=query(Listings,"SELECT A, D, G, N, O, P 

WHERE G < "&B3&"

AND N < "&B4&"

AND P < "&B5&"

"&if(B2="All Towns","ORDER BY A"," and D = '"&B2&"' ORDER BY A")

,1)

Best Answer

Maybe there is a missing space before ORDER BY A in the second IF parameter

instead of

if(B2="All Towns","ORDER BY A"," and D = '"&B2&"' ORDER BY A")

it might have to be

if(B2="All Towns"," ORDER BY A"," and D = '"&B2&"' ORDER BY A")
                   ^
                   ^
                   ^

besides that, there is no need of an extra " because the second parameter of QUERY is a text value and the formula included as the second parameter returns a text value.

Related Solutions

Google Sheets – Query Select Where is Not Null vs <>” Not Filtering Properly

the query only grabs the headers.

No, it grabs some rows, too. Scroll down... way, way down. There they are. Here's why you are hitting this.

Some of your columns are completely empty. These are interpreted as string by default. Hence, is not null is always true for these, and you get the whole table.
Since you order by B (ascending by default), the huge number of empty rows ends up on top, and leads you to conclude that the query returns nothing.

Unfortunately, there seem to be no way to declare the type of an empty column. This, combined with the "no nulls in string column" behavior, leads to people being driven crazy by not null.

To keep your sanity, either

make sure every column has at least one nonempty cell, with some value of the type you expect in that column.
or, use the less powerful but more straightforward filter command when it suffices for the task. Note that the output of filter can be fed into a query, to combine the best features of both.

Google-sheets – Defining a custom order in a Google Sheet query

You can add a sorting column without having it appear on the sheet, within the query command. Example:

=query({A2:AB, arrayformula(hlookup(E2:E, {"str1","str2","str3",""; 1,2,3,4}, 2, false))}, "select Col3, Col1, Col2 where Col2 = 'New York' order by Col4")

Explanation

arrayformula(hlookup(E2:E, {"str1","str2","str3",""; 1,2,3,4}, 2, false))

creates a column with the order you want. This column gets appended to the queried array using array notation:

{A2:AB, arrayformula(...)}

This new array can now be queried. However, since it's a new array and not something placed in the sheet, one cannot use letters A,B,C... to refer to columns. Instead, one has to write Col1, Col2, etc:

"select Col3, Col1, Col2 where Col2 = 'New York' order by Col4"

Error handling

What if some values in the table are not listed in your order, e.g., null values? Add iferror clause to sort them to the bottom:

arrayformula(iferror(hlookup(E2:E, {"str1","str2","str3",""; 1,2,3,4}, 2, false), 1000)

This gives the sorting order 1000 to the values not listed.

Helper column alternative

If having to switch to Col1 notation is too much of a price to pay, add the helper column

=arrayformula(hlookup(E2:E, {"str1","str2","str3",""; 1,2,3,4}, 2, false))

to the sheet; it would be column AC for you. It can be made hidden so it doesn't get in the way. Then query normally and sort by AC.

Lazy alternative

If there are not too many options, you can just repeat the command and join the results using array notation:

= {query(..., "... E='string1'"); query(..., "... E='string2'"); ...}

Like this:

={ QUERY('Raw Data'!A2:AB,"SELECT E, A, C, D, Y, R, S WHERE S = 'New York' AND E = 'string1'"); QUERY('Raw Data'!A2:AB,"SELECT E, A, C, D, Y, R, S WHERE S = 'New York' AND E = 'string2'") }

You'll need to avoid header rows when doing this, because they would be repeated.