About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

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

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

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.

Question

How do I extract multiple items from emails & Lookup them in Google Sheets?

  • 18 December 2023
  • 3 replies
  • 94 views
D

Hello Zapier Community,

I am working on an automation project and could use some advice on how to best approach a particular challenge. My goal is to automate a process that involves parsing an email body with multiple items, extracting those items, and then looking them up in a Google Sheet. Here's a brief overview of what I'm trying to accomplish:

Process Overview

Trigger: A Google Form submission, containing fields for the sender's email and the body of the email. The email body includes requests for pricing on multiple items, formatted like "Please send me your prices for 1x ZB2BY4953 and 2x ZB2BY9330."

Parsing Email Body: I need to parse the email body to extract individual items and their quantities. Each item is identified by a unique code (e.g., ZB2BY4953).

Looking Up Items in Google Sheets: Each extracted item needs to be looked up in a Google Sheet to retrieve item details like price, name, etc. The item code corresponds to a column in the sheet.

Output: The final step is to create a new row in a Google Sheet with the details of all the requested items.

Challenges

  • I'm not sure how to handle the parsing of multiple items in the email body using Zapier.
  • I need a way to loop through each extracted item to perform the Google Sheets lookup.
     

Question

  • How can I set up Zapier to parse multiple items from an email body and then loop through each item to perform a lookup in Google Sheets?
  • Are there any specific apps or features within Zapier that I should use for this purpose?
  • Any guidance, tips, or suggestions on how to set up this workflow would be greatly appreciated!

Thank you in advance for your help!

Best regards,
David

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

3 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @Davieboy 

Good questions.

We would need a better sense of the GForm that is being filled out.

We would need a better sense of the HTML email template format that you are trying to parse. (screenshots)

 

I’d recommend using Airtable instead of GSheets.

Airtable has Form Views that may be able to replace GForms, and email parsing.

Airtable has the ability to share Views externally to let others sort/filter the data, which could be used to let people self-serve their lookups.

With Airtable you can have Tables that are linked. (Order, Items, Products)

Airtable Zap actions allows you to create multiple records in 1 action, which could replace the need for looping. (may not be needed)

You can configure the lookup logic via Airtable Automations to link records.

Airtable Automations have a native integration with Gmail, that could be used to send an email.

 

If trying to use GSheets, then check out these Zap apps...

Looping: https://zapier.com/apps/looping/help

Delay: https://zapier.com/apps/delay/help

Digest: https://zapier.com/apps/digest/help

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
D

Hi Troy,

Thank you for your previous response. I have a specific workflow in mind and I'm wondering if you could assist me with setting it up in Zapier.

The process is as follows:

  1. Input: Receiving RFQs via Gmail.
  2. AI Detection: Using AI to parse the RFQ request within the email body or attachments.
  3. Item and Quantity Detection: AI identification of requested items and their quantities.
  4. Lookup in Database: Conducting a lookup in a SQL Express database for item details.
  5. RFQ Response Email Preparation: Creating a draft email response to the RFQ.
  6. Gmail Draft: Saving the response as a draft in Gmail.
  7. Notifications: Implementing a notification mechanism for the process.

 

The input will be:

  1. Email with RFQ included in the email body 

Example

Subject: RFQ for Immediate Requirements

Dear [Supplier Name],

Please provide us with a quote for the following items:

  • 3x ZB4BC54
  • 2x ZB4BD57
  • 5x ZCE65

Looking forward to your prompt response.

Best regards,

[Your Name] [Your Company]

 

  1. Email with RFQ included in the attachment (Excel.)

 

For clarity, here's a snapshot of the SQL database format where the lookup will occur:

  • RegCis: The unique item code.
  • PriceX: The unit price of the item.
  • ETA: Estimated time of arrival or availability (expressed in days).

For the Zapier setup, we'll be using the "RegCis" column to look up the corresponding "PriceX" and "ETA" for each item identified by the AI in the RFQs received via Gmail. Here's a sample of the data structure in the database:

 

RegCis PriceX ETA
ZB4BA8112 NULL 0
ZB4BA8134 NULL 0
ZB4BC54 8.56000 12
ZB4BD57 7.15000 0
ZCE65 12.64000 7
... ... ...

 

Could you guide me on how to configure Zapier to automate this process, particularly focusing on the AI detection and SQL database lookup steps?

 

Best regards, David

Troy Tessalone
Userlevel 7
Badge +14

@Davieboy

ChatGPT can be used as an email parser.

 

You may have to split the workflow up into dependent Zaps.

Zap 1: Parse email for add line items and do lookup

Zap 2: Create email draft

 

I’d recommend using Airtable instead of GSheets.

Airtable has Form Views that may be able to replace GForms, and email parsing.

Airtable has the ability to share Views externally to let others sort/filter the data, which could be used to let people self-serve their lookups.

With Airtable you can have Tables that are linked. (Order, Items, Products)

Airtable Zap actions allows you to create multiple records in 1 action, which could replace the need for looping. (may not be needed)

You can configure the lookup logic via Airtable Automations to link records.

Airtable Automations have a native integration with Gmail, that could be used to send an email.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Terms & Conditions