Best answer

Searching for multiple values in Google Sheets

  • 21 February 2021
  • 11 replies
  • 1357 views

Badge

Hi,

I have a zap where:

Trigger: New moderation tag in Insided
Action: Lookup value in Google sheets to find which slack channel to send a message to. 
Action: Send Slack message to relevant channel

The zap works perfectly with 1 value. However, when I have multiple tags, the value comes out as “value 1, value 2”. As I only have columns for “Value 1” and “Value 2”, and not “value 1, value 2”, the lookup function cannot find the appropriate rows. 

Is there a way for me to do a Lookup and Contains as opposed to Exact Matches?

Any thoughts?

Thanks

Ilan

icon

Best answer by Troy Tessalone 21 February 2021, 17:38

View original

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 +14

Hi @Ilan B 

What is your logic for determining which Tag(s) to search for?

For example, if there are 2 Tags, should the Lookup search for each individually or only the first tag or only last tag or only specific tags?

Would you send a message to each matching Tag? (Try a Looping action: https://zapier.com/apps/looping/integrations)

Depending on your requirements, look into using Formatter and Filter steps.

 

Try using an Update GSheets action that utilizes a helper GSheet with a QUERY formula: https://support.google.com/docs/answer/3093343?hl=en

Some GSearches on the topic will get you configuring the WHERE statement that uses MATCHES.

=query(A1:A, "Select A where A matches '.*Info Inspired.*' ")
Badge

Hey @Troy Tessalone,

Thanks so much for the quick response!

  1. I am looking at searching for each tag, and sending messages for Both tags. Eg. A post is tagged with “Registration” and “Security”, a slack is sent to the “Registration” channel and to the “Security” channel.
  2. I’ll have to look into the looper function. Ive never used it before, but it would appear that it could meet our needs
  3. I’m not sure how the GSheet solution will work for my requirement. Can you shed a bit more light on this, please?

Thanks in advance!

Ilan 

Userlevel 7
Badge +14

Hi @Ilan B 

Give the Looper action a try: https://zapier.com/apps/looping/integrations

Badge

Hey @Troy Tessalone,

I had a play around with the Looper action and I think I nearly have it.

My problem is that when I have tags A, B and C, it will send messages of 3 instances of A, 2 of B and 1 of C. Any ideas of how to prevent this?

Thanks again for the help!

Ilan

Userlevel 7
Badge +14

@Ilan B 

Please provide screenshots of how your steps are configured for the Looping action and following steps.

Badge

@Troy Tessalone 

 

This is the big picture

 

This is the looper set up
The google sheets set up

 

The Text action just cleans up the HTML to markup, and the Slack sends it to a corresponding channel from the google sheets lookup.

Thanks again for all your assistance!

Ilan

Badge

@Troy Tessalone 

I just had a thought as to why this is happening.

When I add the tags A, B, C, because this is an INSTANT Zap, is it possible that it is running A, then A,B, then A,B,C? Is there a way to stop this? Would the delay function help?

Thank you again for being a rockstar and helping!

Userlevel 7
Badge +14

@Ilan B

For the Looper step, try using Line Items instead of Text.

When you test the Looper step, it will show you how it parsed the data and how many loop it would do.

Be sure to check that to understand why/how it’s looping based on the provided data.

Badge

@Troy Tessalone,

I changed the Looper to Line Items instead of Text. Still having the issue. In the preview, its saying the loop is occurring only once. 

I think my above hypothesis, that it's because each time I add a moderator tag in Insided it is triggering a Zap is my issue. 

Any thoughts about preventing that?
 

Userlevel 7
Badge +14

@Ilan B 

Ah I see.

I don’t think there is going to be a way to prevent that unless the trigger step indicates the specific Tag that was added or you kept history log somewhere to reference, such as in a GSheets.

Badge

@Troy Tessalone 

Bugger. Thanks for all the assistance in any case! Will have to try tinkering around a bit more!