Question

Finding Rows in Google Sheets- CONTAINS Lookup Value (rather than EQUALS Lookup Value)

  • 22 January 2021
  • 11 replies
  • 1002 views

  • Anonymous
  • 0 replies

Hi all,

 

I have a spreadsheet with the column title as “Operating (Days of the Week).” However, while some values are “Wednesday” for example, others are “Wednesday lunch” or “Tuesday, Wednesday, Friday.” My zap currently only locates the cell that contains exactly “Wednesday” although I would like it to locate all of the examples I mentioned. I was thinking filter might work- but it would need to be a filter within the action (rather than before or after that action). Is this possible? Is there anything else I could do?

 

Thank you


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

11 replies

Userlevel 7
Badge +12

I think I have a work around that could work for you @cultivatecville 

If it’s possible, I would suggest adding an additional column to the spreadsheet. One with a formula to check for if the look up column contains “Wednesday”. Assuming the spreadsheet rows are created from Zapier, you can pass formulas Dynamically by following my post here 

 
In that column you will insert the formula referenced below, which returns either TRUE or FALSE. Then when you look for the data in Zapier You can search the contains(“wednesday”) column for TRUE 



Let me know if that helps!

Hi @GetUWired ,

 

Thanks for your reply! My spreadsheet was created before Zapier integration (meaning, the rows are not created from Zapier). Would your suggestion still work?

 

Thank you

Userlevel 7
Badge +12

Absolutely! 


Just create the column and drag the formula values all the way down. I just included the dynamic content in case there were still rows getting created on the spreadsheet. 

Hi @GetUWired ,

 

I’m worried this won’t work, because the action won’t always be looking for “Wednesday.” The value it will be looking for depends on an input value from a previous action (date/time). So, that means I would have to make a true/false column for each day of the week, right? Is there a simpler way to do this rather than adding a column for each day of the week?

 

Thank you

Userlevel 7
Badge +12

Correct. 

I wouldn’t call this simpler, but IF your spreadsheet is short enough… You could try to pull ALL of the rows with the Get Many Spreadsheet Rows action and then trying to search for soft matches using a code block. 

Can you describe in more detail your Zap? 

@GetUWired, sure! 

Trigger #1 is an incoming message (text message) from SimpleTexting, with the date on which the text is sent formatted as 2021-01-20T19:11:38-05:00

Trigger #2 converts the date into day format (ex. Wednesday)

Trigger #3 finds all rows with on a google sheet that match “Wednesday” under column “Operating (Days of the Week)

Trigger #4 sends a text message through the SimpleTexting program based on rows found from trigger #3

 

If you need me to clarify anything or have any more questions, please let me know!

Thank you

Userlevel 7
Badge +12

Hmmm… I think the simplest way would be to create columns for each day of the week and then before the search action, you would need a Formatter by Zapier> Utilities > Look up table (see attached) action to select which column to search.

Then from there, you can pass the column searching for TRUE 



I think to build something more concise would involve creating a custom search action with Google Sheets or pulling back all the rows (If sheet is under 20 rows) and filtering the results with a code block. 

@GetUWired Thank you so much. This is all very helpful.

Would you mind explaining what a custom search action is in Google Sheets? And what a code block is? Also, how I could use these for my project?

Thank you

Userlevel 7
Badge +11

Hi @cultivatecville!

The alternative solution that @GetUWired mentioned is not something that can be easily demonstrated, as it’s quite advanced. If you’re interested in hiring someone to help you with something like that, you can check out the Hire a Pro forum or take a look at our Certified Zapier Experts to see if there’s anyone who fits the budget you’d have in mind for this :)

Userlevel 7
Badge +12

Hi @cultivatecville 

Back again this time with a solution that can be implemented with no code! But would require modifying your zap and google sheet. 

I’ve posted it in the tips and inspiration section. Please have a look and feel free to ask any questions you need! 

 

Userlevel 7
Badge +12

Hi @cultivatecville!

 

Were you able to take a look at the great post that @GetUWired shared? If so, did it help you to get to a solution?