Google-sheets – How to concatenate string in select clause of a google sheet query

google sheetsgoogle-sheets-query

How can I concatenate a string in a select clause of google sheet query function ?

ex: =QUERY(DATAS!B:F; "SELECT B,C,D,'=image('&E&')',F WHERE upper(F) "&J4&" upper('"&N4&"') LIMIT 50";1)

Here I want to get E column as =image(E) in the sheet.

Anyone with an idea ?

Thanks :d

Best Answer

Idea is to wrap your query with another one to get URLs of images and use ARRAYFORMULA(IMAGE()) on output.

={
QUERY(
   QUERY(DATAS!B:F; "SELECT B,C,D,E,F WHERE upper(F) "&J4&" upper('"&N4&"') LIMIT 50";1);
   "SELECT COL1, COL2, COL3"),
ARRAYFORMULA(IMAGE(QUERY(
   QUERY(DATAS!B:F; "SELECT B,C,D,E,F WHERE upper(F) "&J4&" upper('"&N4&"') LIMIT 50";1);
   "SELECT COL4"))),
QUERY(
   QUERY(DATAS!B:F; "SELECT B,C,D,E,F WHERE upper(F) "&J4&" upper('"&N4&"') LIMIT 50";1);
   "SELECT COL1, COL2, COL4")
}

Related Solutions

Google-sheets – Trying to query a range of names in Google Spreadsheets, query is failing with the space in the names

Enclose the string value (John Doe) between single quotes ('John Doe'). The resulting formula is:

=QUERY('QA Import'!A1:H, "select A where G = '"&B2&"'")

References

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.