Question

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

  • 18 December 2023
  • 3 replies
  • 112 views

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

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

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

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.