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

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

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 rSupplier 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,

pYour 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

@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.

 

Terms & Conditions