Google sheet importrange and query not working

Question

I have a problem with the following, I have a sign in/out sheet created that will be used in a main office, I would like staff to be able to search the sheet for names without having access to the sheet, so in a new sheet I have created the following formula

=query(importrange("https://docs.google.com/spreadsheets/d/1UKOGokeckL8QIBreKe253l7CfZ3Ra7QrLyWBGav_Cdw/edit#gid=0","Sign IN/OUT!A3:E"), "SELECT * WHERE 1=1 "&"AND LOWER('Sign IN/OUT!A:A') LIKE LOWER(""%" &JOIN("%"") AND LOWER('Sign IN/OUT!A:A') LIKE LOWER(""%", SPLIT(B1," "))&"%"")",1)

This is only showing the first row from the imported data.

Any help on getting the formula corrected would be of great help. I am able to use the following code within the same workbook to accomplish the search

=query('Sign IN/OUT'!A3:E, "SELECT * WHERE 1=1 "&"AND LOWER(A) LIKE LOWER(""%" &JOIN("%"") AND LOWER(A) LIKE LOWER(""%", SPLIT(B1," "))&"%"")",1)

so I feel like this should work, what am I missing?

Thanks,

We need to use Col1, Col2, Col3, etc. when we are not referencing a range in our sheet. In your case, you are referencing data returned by the IMPORTRANGE() function. — JohnA

player0 player0 · Accepted Answer · 2021-05-25T17:54:42

try:

=QUERY({IMPORTRANGE("1UKOGokeckL8QIBreKe253l7CfZ3Ra7QrLyWBGav_Cdw", "Sign IN/OUT!A3:E")}, 
 "where 1=1 
    and lower(Col1) like LOWER(""%" &JOIN("%"") 
    and lower(Col1) like LOWER(""%", SPLIT(B1, " "))&"%"")", 1)

Google sheet importrange and query not working

1 Answers