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.

Hello Zapier Community,

I need help setting up automation to populate dynamic lead information from Excel files stored in Google Drive/OneDrive into my CRM Airtable when a new lead is received. Here's the process I envision:

  1. Trigger: The automation should start when a new lead is captured in my Airtable CRM.
  2. File Retrieval: The lead’s details, especially the unique identifier (like a Ref # or Phone 😵, should be used to locate the corresponding Excel file in Google Drive/OneDrive. (There are multiple excel files with lead information).
  3. Data Extraction: Once the file is found, specific data from the Excel sheet needs to be extracted – this data is dynamic and may vary slightly from lead to lead.
  4. Update Airtable: The extracted information should then be used to update or populate the lead’s record in Airtable.

Key challenges include dynamically identifying the correct Excel file based on lead information and extracting varying data points from these files.

I am able to find the folder in OneDrive but not the specific row in Excel. 

Any advice on setting up this automation, particularly with identifying and extracting data from the right Excel file, would be greatly appreciated!

Thank you!

Here is my current set up.

Hi @hg305 

We will need to see how your Zap Step 3 Action section is configured to have context about the error.

 

Here you go.

“SENT” is the name of the folder within Onedrive and Google Drive where all lead offer campaign files are stored.

The spreadsheet, worksheet, lookup column, and lookup value are all dynamic as I am working with different unique leads and excel files where my leads are stored. 

For Example:

  • Lead A:

    • Mailer Campaign: "2312A RVL Abby 2-35ac"
    • Ref #: 2312A100
    • Ref # Extract: 2312A
    • Excel File: "2312A RVL Abby 2-35ac.xlsx"
    • Lookup Column: Reference
    • Worksheet: 2312A (Ref # Extract)
    • Lookup Value: 2312A100 (Ref #)
    • Details: This file contains property-specific information, such as price and lot size, legal descriptions, features, owner details like mailing address, etc.

  • Lead B:

    • Mailer Campaign: "241B MH 10-20ac"
    • Ref #: 241B435
    • Ref # Extract: 241B
    • Excel File: "241B MH 10-20ac.xlsx"
    • Lookup Column: Reference
    • Worksheet: 241B (Ref # Extract)
    • Lookup Value: 241B435 (Ref #)
    • Details: This file contains property-specific information, such as price and lot size, legal descriptions, features, owner details like mailing address, etc.

 

@hg305 

The attachment did not upload.

 

Not sure why its not working for you.

@hg305 

Help article to follow for using dropdown fields with dynamic variables: https://help.zapier.com/hc/en-us/articles/8496241696141-Add-custom-values-to-dropdown-menu-fields-in-Zaps

 

Thank you but I need direct help with this.

@hg305 

If you need help, consider hiring a Certified Zapier Expert: https://zapier.com/experts

Reply


Terms & Conditions