Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

  • Home
  • Get Help
  • How Do I
  • How to search a "Lookup" value for a specific string of words found in a Google Sheet cell.

Hi there,

I am trying to create an action to send a gmail email reminder (not relevant for this question), whenever a Clio Calendar Event is about to start within 24 hours, whenever Zapier determines that “one” of the attendees associated with the Clio Calendar event is one of the named attorneys listed in Column A of a Google Sheet.

The problem is that there are usually at least two attendees per Clio event: the client and the attorney, and Clio puts them together into a single value: Attendees: Joe Schmo, Mary Foe

Joe Schmo is an attorney for this example. All attorneys and their email addresses are listed in a Google sheet. Attorney Name = Column A. Attorney Email Address = Column B. Thus:

Column A              Column B

Attorney Name   Attorney Email

Joe Schmo           joes@abc.com

Curly Schmo        curlys@abc.com

Larry Schmo         larrys@abc.com

My problem is this: How do I successfully “Lookup” and find “Joe Schmo” in the Google Sheet, when my “Lookup Value” in Clio event is not “Joe Schmo,” but is “Joe Schmo, Mary Foe.” Obviously that is not a match.

I’m trying to figure out how the Zap can determine that “Attendees: Joe Schmo, Mary Foe”  (the “Lookup Value”) actually includes/contains just the name, “Joe Schmo”, so that it finds there is a match in the Google Sheet Column A.

Right now, if I put Column A as the Lookup Column in the sheet (“Joe Schmo”), and then for the Lookup Value, I choose the Clio “Attendees: Joe Schmo, Mary Foe” value, and run the test, the test fails because the Zapier lookup is looking for a value in the Lookup column that is exactly “Joe Schmo, Mary Foe.” It is NOT looking only to see if one of the names (Joe Schmo) is in the lookup column. This is what I want it to do.

I want to set up the Google Sheets Lookup with a formula of some sort so that the zap will search for the Column A name (Joe Schmo) within the entire Lookup VAlue from Clio (e.g. Joe Schmo, Mary Foe), and upon finding that the Clio Attendees at least “contains” the attorney’s name, then Zap will consider it a scucess and will allow me to proceed with sending an email reminder to Joe Schmo at joes@abc.com.

I’ve tried Contain(), Search(), ISNUMBER (SEARCH” formulas, not knowing if any of these actually are supposed to work, and not even knowing if I am setting them up correctly. Apparently not, because they are not working the way that I’m trying to use them.

Any ideas? 

Thank you.

Bert

 

 

 

 

Hi @Accesslegalcare 

Try this…

Use the action step Formatter > Utilities > Text to Line Items: https://zapier.com/help/create/format/create-line-items-in-zaps

Then you can use the Looping app to handle the line items (aka array): https://zapier.com/apps/looping/integrations

Ok, thank you. That tells me that I can do it, but my tech knowledge is limiting me.

Would I be able to hire you to set up this zap for me by chance?

If not, I will try to slush through it and understand it all. 

Thank you for giving me the answer! I really appreciate it! That was fast.

Bert

Terms & Conditions