Question

Finding multiple values in google sheets

  • 19 December 2020
  • 14 replies
  • 1020 views

Hello, 

 

Need some help here. 

 

I have some data in a Google spreadsheet with columns as below

Deal ID Deal Name Name Email Address Service Code Status
12345 ABCD John john@abc.com AA-BB Open
12345 ABCD Tina Tina@abc.com AA-BB Open
12345 XYZ Wendy Wendy@abc.com XX-YY Open

 

I would like to Trigger: When Hubspot Deal Property “Customer Name” Changes

  • For Deal ID 12345, I have added John & Tina as property values in “Customer Name” & Saved.
    • Step: Zap Triggers 
    • Output: Gives me data as below:
      • Deal ID- 12345
      • Customer Name: John; Tina
    • I have added formatted to create line items as below : 
      • 1. John
      • 2. Tina

        I have  created line items assuming i can use them separately for Lookup in Gsheets. I am not sure if this is technically not right. If its not right what is the right way to split values which can be searched or lookup
         
  • I would like to search Google spreadsheet for deal ID - 12345 & supporting value as John for first time. If i found this I would like to mark status as closed and send an email to John. 
  • I would like to search Google spreadsheet for deal ID - 12345 & supporting value as Tina for the second time. If i found this I would like to mark status as closed and send an email to Tina. 

Problem: Each time I am trying to look up for the line item John or Tina seperately in spreadsheet they look up for John, Tina  as combined search ending up in No result. 

 

Question:

  • How can I
    • Search John for the first time, Get the result and take an action [Change status to Closed & send an email]
    • As a next step, again run a search for Tina, Get the result and take an action [Change status to Closed & send an email]

How can i achieve this? Appreciate your responses.

 

Edit: I do not want to send email to Wendy who is in third row.


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

14 replies

Userlevel 7
Badge +14

Clarifying questions:

  1. Will there always be 2 names to search?
  2. What if there is only 1 name to search? (John, instead of John, Tina)
  3. What if there is more than 2 names to search (John, Tina, Bob)

Clarifying questions:

  1. Will there always be 2 names to search?
  2. What if there is only 1 name to search? (John, instead of John, Tina)
  3. What if there is more than 2 names to search (John, Tina, Bob)

Hi Troy, 

Thank you for the prompt response. 

Yes, there can be one name or two names or can be 50 Names as well.
Depends on how many customers I want to select, mark as closed 

If one name to search, then it should search only one name → Mark closed→ Email

If more than two names, then it should search two names separately → mark closes→ send two seperate emails.

Same applies for 3 or 4 or any number. Workflow expected to repeat. 

If a name is not selected then, it should not search for that.
 

Userlevel 7
Badge +14

If a name is not selected then, it should not search for that.

Would need to add a Filter step to check for this condition: https://zapier.com/apps/filter/help

Userlevel 7
Badge +14

If one name to search, then it should search only one name → Mark closed→ Email

If more than two names, then it should search two names separately → mark closes→ send two seperate emails.

Same applies for 3 or 4 or any number. Workflow expected to repeat. 

 

Would need to create a looping Zap to iterate thru each option 1+.

Here’s one way to create a looping Zap.

There are other more technical ways to create looping Zaps using code and webhooks.

Hi Troy, 

 

Thank you for your reply. I am using looping zap for emailing.  

But my main question here is how can I split the names John & Tina separately and lookup for a row? 

 

Any suggestions on that? 

Userlevel 7
Badge +14

With the names split into line items (aka an array), each needs to be passed individually to a looping Zap that performs the GSheet lookup and subsequent steps to email if applicable.

So for John and Tina, that would mean each goes thru the looping Zap separately, meaning 2 looping Zaps run.

The Zap logic needs to increment the index of the line items (array) from 0 to 1, and so on.

 

Name Line Items (0 is the first item, 1 is the second item)

0: John

1: Tina

Userlevel 7
Badge +14

One low tech way to do this is to use another GSheet or GSheet tab with the GSheet - Create Rows action which would send each line item as a row.

That could then trigger another Zap with the trigger being GSheet - New Row to do the GSheet - Lookup Row, etc.

 

Interesting!

Does that mean I would need to create 10 Looping Zaps if I have 10 names? 


I believe this is handy. Testing out now.

@Troy Tessalone Just wondering if you can assist on below: 

The code provided here worked well for first time loop. However, when the loop repeated the second time it halted below error

 

 

Any changes needed on the code here? Just FYI, after step 3, there is step 4 with filter.

I do not think it is impacting because it is stopped at step 3 itself.  

 

Any help is appreciated. 

Userlevel 7
Badge +14

Sorry, since I did not write the code I’m unable to troubleshoot.

Userlevel 7
Badge +14

Looks like no value was passed in to the GSheet Lookup Row step

 

Userlevel 4
Badge +4

@rprconnect what are you putting in to your names and email values in the first code step?

Userlevel 7
Badge +10

@rprconnect 
Just checking in to see if you still need help with this?