How to keep the hyperlink while using =QUERY formula in google spreadsheet?

Question

I wrote a =QUERY formula in Google spreadsheet. However I would like to copy not only the values of the cells but also the embedded links from the range of cells I am performing the query on. This is what I wrote:

=QUERY('Tab'!6:1963,"select C where (E='Major' and D >= now())")

There must be a way to tell the query to bring the URL as well along the content of the cells.

Gypsum

Answer 1

The query function only supports certain data types:

Supported data types are string, number, boolean, date, datetime and timeofday.

It doesn't handle other things one might embed into a spreadsheet, such as images or hyperlinks. (Hyperlinks are coerced to strings.) After all, the query language is not something Sheets-specific, it has its own data models that interact with Sheets only to an extent.

A solution is to use filter instead of query, if possible. It can do many of the things that query does. For example,

=QUERY(Tab!6:1963,"select C where (E='Major' and D >= now())")

can be replaced by

=filter(Tab!C6:C1963, (Tab!E6:E1963="Major") * (Tab!D6:D1963 >= now()))

which will return the links as expected. (And even images inserted with =image() if you got them.) The multiplication operator is logical and in the filter formula.

Jonquil

Answer 2

I know this is three years later, but I ran into this issue and my query would have converted to a complicated nest of FILTER and SORT functions. So I ended up doing something like this: ARRAYFORMULA(VLOOKUP(QUERY('Tab'!6:1963,"select C where (E='Major' and D >= now())"),C:C,1,FALSE)) Which worked.

Mountain

Answer 3

Often this question comes into play with IMPORTRANGE. And Google's official answer does not really help (i.e. QUERY only works with strings, etc.). It is possible to give the filter range as an imported range, too, then it works:

=FILTER(IMPORTRANGE("XXX","Data!A1:A),
IMPORTRANGE("XXX","Data!B1:B")>0)

where column A is the data you want to import and column B is the filter

Retort