How to Extract Structured Data From Emails Using a Zapier AI Step

Incoming emails like order confirmations, lead replies, or invoices hide the data you actually want inside paragraphs of text. An AI step can read each email and return the fields you name, so the next action drops clean values straight into Google Sheets. This guide builds that pipeline.

What you need

• A Zapier account
• Gmail or another email app as the trigger
• A Google Sheet with columns ready
• Either AI by Zapier or your own OpenAI/Anthropic key

Step 1: Trigger on the right emails

Use a Gmail New Email Matching Search trigger so you only fire on relevant mail. A search like from:orders@ subject:confirmation keeps noise out and saves tasks. Test it to load a real sample email.

Step 2: Ask the AI for JSON output

The trick to reliable extraction is forcing the model to answer in a fixed JSON shape. Map the email body into the prompt and describe each key precisely. If a value is missing, tell the model to use null so the structure never breaks.

Extract these fields from the email and reply with ONLY
valid JSON, no extra words:

{
  "customer_name": string,
  "order_id": string,
  "total_amount": number,
  "order_date": "YYYY-MM-DD"
}

If a field is not present, use null.

Email:
{{Body Plain}}

Step 3: Parse the JSON into fields

AI responses arrive as one text blob, so add a Formatter by Zapier step, choose Utilities, then Import JSON (Text to Object). Map the AI output text in, and Formatter splits it into individual fields you can use downstream.

Step 4: Append a spreadsheet row

Add a Google Sheets Create Spreadsheet Row action. Map each Formatter output to its matching column. Test the Zap end to end, then turn it on so every matching email becomes a tidy row.

Example: An order confirmation email lands, the AI returns clean JSON, Formatter splits it, and a new spreadsheet row appears with the customer, order ID, amount, and date already in the right columns.