Skip to main content
Best answer

How to automate matching participants from Excel with customer files using Zapier


Hi everyone.

 

I'm trying to set up automation in my company but I'm getting stuck, so I'm here to ask for help from anyone who can answer my questions :visage_légèrement_souriant:

 

We'll be talking here about automation on ZAPIER, probably boosted by AI.

 

The situation is as follows: at trade shows, I retrieve an excel file containing the participants, with a company in one column and the position held in a second column.

I'd like to make a match with my customer files to find out if I already have someone with this buddy in this company. 

And then check if I have the first and last name, or use tools to retrieve this information. 

 

I'd like to point out that we use ZAPIER to automate, and that I have access to several tools afterwards (I'm learning a bit more about make and I'm just starting to get to grips with zapier, with which I'm struggling a bit).

 

Basically, I was going for a Google Drive module with my two databases (salon and client) and I wanted to give them to an AI in order to, firstly, create a semantic match in the job titles and then a match between the lines in the files.

 

I'm already stuck at this stage because I haven't managed to set it up in this way, so I think I need to take another reflexive approach, perhaps involving reading line by line, matching line by line with the other file and then repeating the loop.

 

Translated with DeepL.com (free version)

Best answer by SamBBest answer by SamB

Thanks for getting back to me ​@JeremMS. Totally makes sense to trigger from the file being uploaded to Google Drive. For that I’d recommend using the New File in Folder (Google Drive) trigger as you can set that to look for new files within a specific folder. From there I think you’d want to look at using the following actions:

  1. Depending on the sharing settings for the file you may need to use a Add File Sharing Preference action so that the next step will be able to access the file:
    ca3392faea2c0c8d76e9a61923f55daa.png
     
  2. If the Google Drive trigger doesn’t output a CSV version of the Excel file you’ll want to use a Utilities > Import CSV File (Formatter) action to import a CSV version by automatically creating the export link using this format: https://docs.google.com/spreadsheets/export?id=[Map the ID field from the trigger here]&exportFormat=csv

    For example:
    ce899be874b494687f3456bd09dcf7d1.png
     
  3. That will output the data from each row as a separate line item:
    0cf2d2bd59c7d36d9159d567cd7c1aa8.png
     
  4. You can then pass those line items over to a Create Loop from Line Items (Looping by Zapier) action and add the different line items for each column of the spreadsheet:991817e39d02a4ec3b21ccaa87292e4c.png

    It’s important to note that when testing in the Zap editor only the first set of line items will be processed, for example:
    bcd5aad620d0734505b20a91b1745df1.png
    But when your Zap runs live it will go through each set of line items in a separate loop iteration.
     
  5. Next you’d add a Find Contact (HubSpot) action to search for the contact (lead) based on the company name and job title. For example:
    67b50631edf2ddb1fd2753364f7a0f2c.png
     
  6. Ensure you set the If no search results are found option to Mark “successful” and run steps that use this search result:
    d739fbb979ef9cc6ee7d263b72e19495.png
    This will allow the following steps to run regardless of whether a contact was found or not.
     
  7. Next you’d use Paths to allow the Zap to carry out different actions based on whether a contact was found or not. A Zap Search Was Found Status field will contain a value of true if a contact is found and false if it’s not found:
    62de83f982c9170396692b5857cb0733.png
  8. So Path A (Lead found) you would set up like this:
    d02b2b27ef60d7d20a93d7a2f0ef8fe4.png
  9. And Path B (Lead not found) would be set up like so:
    840601ebbe34c34b3f0d5e86d54ca606.png
  10. Then in each path you’d add the relevant actions to be carried out. 

With the Path B (Lead not found) path I couldn’t see any existing actions available for the LinkedIn app that would allow you to search for the lead. There’s an existing feature request for that type of action to be added so I’ve gone ahead and added your vote for it. While I can’t guarantee if or when this will be added, we’ll be sure to notify you via email if it becomes available! When you say APPOLO are you referring to the Apollo app? If so, you could potentially use it’s Find Contact search action:

23957c9cfd56a7b3afc7b237550d0807.png
If not, you might need to look at using LinkedIn’s API Request action to perform the search assuming LinkedIn’s API supports that. Or if APPOLO or EXA.AI have a publicly-accessible API then you could potentially use a Webhooks by Zapier action. Both of those options are a bit advanced so I’d recommend checking out these guides on how to use them:

And I’m not sure if you’ve worked with paths or looping before so I’ll share some guides on them as well just in case it’s useful:

 

Hope that helps to get you pointed in the right direction. If you run into any issues on that or have further questions just let us know! 🙂

View original
Did this topic help you find an answer to your question?

8 replies

SamB
Community Manager
Forum|alt.badge.img+11
  • Community Manager
  • 7396 replies
  • January 20, 2025

Hi there ​@JeremMS, welcome to the Community! 👋

I’m not sure you’d necessarily need AI to perform the search. I’d have thought that you could use a Lookup Spreadsheet Row (Google Sheets) search action within a loop (Loop your Zap actions) to look for a matching company name in the spreadsheet that is stored on Google Drive and obtain the relevant first and last name.

How do you receive the Excel file, is it emailed to you or is this added to Google Drive? If it’s emailed, which email app do you use—Gmail, Microsoft Outlook for example?

Also, what actions are you looking to carry out when a match is found for a company name?

If you can share some further details here we’ll be better able to help you to work out exactly which triggers and actions your Zap will need. And if you can share some screenshots of how the Zap set up currently and where you’re getting stuck, that would be much appreciated. Make sure to blur or remove all personal information (names, emails, addresses etc.) from screenshots before sharing - you can use a tool like Zappy for that.

Looking forward to hearing from you on this!


  • Author
  • Beginner
  • 4 replies
  • January 21, 2025

Hello, 

First of all, thank you very much for your reply.

I think you're right about the lookup to spreadsheet logic, because in any case, the workflow reflexion I'd imagined didn't work.

A priori the excel file is received on gmail but it seemed simpler to me to work with as a trigger, the addition of this document to a google drive file.

But here are a few details:
- triggering either by mail reception or by google drive (I think google drive is simpler as I'm doing this project for someone who will have control over their drive)
-analysis of the event's customer database line by line (by company name and position held) and search for a match with my company's customer file to see if we already have this lead (which uses HUBSPOT so I think we can make this match directly, I'm not sure because I don't know this tool).
- if we have this lead, add its information to my spreadsheet and possibly specify that it is already a customer by adding a new line
- if we don't have this lead, search on linkedin (or with the APPOLO or EXA.AI tools) for the NAME and FIRST NAME information and implement this data on my spreadsheet, in order to complete my customer file to contact this one later on. 

I've tried to be as complete and clear as possible, I hope it's suitable for receiving help haha.

Thanks again 🙏

 


SamB
Community Manager
Forum|alt.badge.img+11
  • Community Manager
  • 7396 replies
  • Answer
  • January 22, 2025

Thanks for getting back to me ​@JeremMS. Totally makes sense to trigger from the file being uploaded to Google Drive. For that I’d recommend using the New File in Folder (Google Drive) trigger as you can set that to look for new files within a specific folder. From there I think you’d want to look at using the following actions:

  1. Depending on the sharing settings for the file you may need to use a Add File Sharing Preference action so that the next step will be able to access the file:
    ca3392faea2c0c8d76e9a61923f55daa.png
     
  2. If the Google Drive trigger doesn’t output a CSV version of the Excel file you’ll want to use a Utilities > Import CSV File (Formatter) action to import a CSV version by automatically creating the export link using this format: https://docs.google.com/spreadsheets/export?id=[Map the ID field from the trigger here]&exportFormat=csv

    For example:
    ce899be874b494687f3456bd09dcf7d1.png
     
  3. That will output the data from each row as a separate line item:
    0cf2d2bd59c7d36d9159d567cd7c1aa8.png
     
  4. You can then pass those line items over to a Create Loop from Line Items (Looping by Zapier) action and add the different line items for each column of the spreadsheet:991817e39d02a4ec3b21ccaa87292e4c.png

    It’s important to note that when testing in the Zap editor only the first set of line items will be processed, for example:
    bcd5aad620d0734505b20a91b1745df1.png
    But when your Zap runs live it will go through each set of line items in a separate loop iteration.
     
  5. Next you’d add a Find Contact (HubSpot) action to search for the contact (lead) based on the company name and job title. For example:
    67b50631edf2ddb1fd2753364f7a0f2c.png
     
  6. Ensure you set the If no search results are found option to Mark “successful” and run steps that use this search result:
    d739fbb979ef9cc6ee7d263b72e19495.png
    This will allow the following steps to run regardless of whether a contact was found or not.
     
  7. Next you’d use Paths to allow the Zap to carry out different actions based on whether a contact was found or not. A Zap Search Was Found Status field will contain a value of true if a contact is found and false if it’s not found:
    62de83f982c9170396692b5857cb0733.png
  8. So Path A (Lead found) you would set up like this:
    d02b2b27ef60d7d20a93d7a2f0ef8fe4.png
  9. And Path B (Lead not found) would be set up like so:
    840601ebbe34c34b3f0d5e86d54ca606.png
  10. Then in each path you’d add the relevant actions to be carried out. 

With the Path B (Lead not found) path I couldn’t see any existing actions available for the LinkedIn app that would allow you to search for the lead. There’s an existing feature request for that type of action to be added so I’ve gone ahead and added your vote for it. While I can’t guarantee if or when this will be added, we’ll be sure to notify you via email if it becomes available! When you say APPOLO are you referring to the Apollo app? If so, you could potentially use it’s Find Contact search action:

23957c9cfd56a7b3afc7b237550d0807.png
If not, you might need to look at using LinkedIn’s API Request action to perform the search assuming LinkedIn’s API supports that. Or if APPOLO or EXA.AI have a publicly-accessible API then you could potentially use a Webhooks by Zapier action. Both of those options are a bit advanced so I’d recommend checking out these guides on how to use them:

And I’m not sure if you’ve worked with paths or looping before so I’ll share some guides on them as well just in case it’s useful:

 

Hope that helps to get you pointed in the right direction. If you run into any issues on that or have further questions just let us know! 🙂


  • Author
  • Beginner
  • 4 replies
  • January 22, 2025

WAHOU what an amazing response!!! :clap:

 

Thank you very much for all this explications and illustrations! :pray:

 

I wiil try to make this soon and i will make you a return of any issues!

Thank you too for  the usual’s guides, i will look them to lear use ZAPIER better.

 

See you soon for the result or others projetcs an also a big thanks Sam! :saluting_face:

 

 


SamB
Community Manager
Forum|alt.badge.img+11
  • Community Manager
  • 7396 replies
  • January 22, 2025

You’re most welcome, ​@JeremMS! 🤗 I’m so pleased my reply was helpful.

Please do keep us posted on how you get on with this—excited to hear how you get on!


  • Author
  • Beginner
  • 4 replies
  • January 23, 2025

I've set up my zap but I can't find a solution to the problem in my HUBSPOT noed, which allows me to search for contacts.
Here's the error message:

I've tried several different configurations and formatting, but I can't find a solution.
I've checked that my variable to be searched for is called the same as on HUBSPOT (it's called “main company” on this one). 
I also tried, following claude.ai's advice, not to call a variable but to try to retrieve a company line that I knew was present (i.e. “Accor”) but still the same result.

I don't really understand where the problem comes from, I've spent a lot of time trying and if I come back to you it's because I can't find the logic that's causing the problem (I'd have preferred to come back shouting VICTORY but….no)


  • Author
  • Beginner
  • 4 replies
  • January 24, 2025

After a long moment to search i won the game about the HUbSPOT research company, i continue my adventure.

Also thank you and finally you can skip the préviously post.

 

:D


SamB
Community Manager
Forum|alt.badge.img+11
  • Community Manager
  • 7396 replies
  • January 27, 2025

Thanks for the updates here, ​@JeremMS. I’m really glad you were able to get past that “There was a problem with the request” error—nice work! 🙌

Happy to help if you run into any further trouble or questions. Until then, happy Zapping! 😁⚡