CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jim55
Jim55

Reputation: 47

Google sheet importrange and query not working

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,

Upvotes: 1

Views: 589

Answers (1)

player0
player0

Reputation: 1

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)

Upvotes: 2

Related Questions