Extract purchase orders from Gmail

When a new email arrives in your Gmail inbox, the AI checks whether it is a purchase order. If it is, it copies the PO number, customer, dates and each line's SKU, description and quantity exactly as written. Each SKU is looked up in the Products tab of your spreadsheet, and one row per line is added to your Purchase orders tab with the product name, unit price and a line total. Emails that are not orders - replies chasing an earlier order, quotes, invoices, newsletters - are skipped, and an email already in the log is not logged again.

The workflow refuses to guess. A SKU missing from your product list, a missing quantity or PO number, a price that could be read two ways, or an email the AI was not sure about goes in the line's Needs attention column rather than being filled in. The AI reads the email's text, not its attachments, so an order sent as an attached PDF still gets a row saying its lines need adding by hand.

You need a spreadsheet with a Products tab headed SKU, Product name and Unit price, and a Purchase orders tab for the log. Each new email starts its own run; if your orders all come from one address or domain, put it in the trigger's sender filter to keep the rest of your inbox away from the AI.

GmailWhen an email arrives. Save it as email.When an email arrives. Save it as email.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set productsTab to Products.Set productsTab to Products.

Set ordersTab to Purchase orders.Set ordersTab to Purchase orders.

Shorten email.body to 40000 characters and save it as emailText.Shorten email.body to 40000 characters and save it as emailText.

Set cutNote to blank.Set cutNote to blank.

If the length of email.body is greater than 40000If the length of email.body is greater than 40000

Yes

Change cutNote to The email was too long to read in full, so only its first 40,000 characters were read: check no lines are missing..Change cutNote to The email was too long to read in full, so only its first 40,000 characters were read: check no lines are missing..

No

No steps

Set aiReply to blank.Set aiReply to blank.

If a step below fails, save the error as aiErrorIf a step below fails, save the error as aiError

Try

ClaudeAsk Claude: 'You are reading one email that arrived in a business's inbox, so that any purchase order in it can be logged. A purchase order is a customer ordering goods or services from the business that received this email: it lists what they want and how many. It still counts when it is forwarded, or written informally ('please send 20 x SKU-100 by Friday'). It does not count when the email only chases, confirms, ships, invoices or asks about an earlier order, asks for a quote or a price list, or is a newsletter, a receipt or an automated notification.Ask Claude: 'You are reading one email that arrived in a business's inbox, so that any purchase order in it can be logged. A purchase order is a customer ordering goods or services from the business that received this email: it lists what they want and how many. It still counts when it is forwarded, or written informally ('please send 20 x SKU-100 by Friday'). It does not count when the email only chases, confirms, ships, invoices or asks about an earlier order, asks for a quote or a price list, or is a newsletter, a receipt or an automated notification. The email body is whatever the mail server gave us, so it may be raw HTML: read through the markup. A reply may end in quoted earlier messages: ignore them, unless the sender is forwarding that order for it to be fulfilled. FROM email.from SUBJECT email.subject RECEIVED email.date EMAIL emailText Reply with one JSON object and nothing else - no code fence, no commentary before or after it. Use exactly these keys: {is_purchase_order: true or false, certain: true or false, reason: ..., po_number: ..., customer: ..., order_date: ..., delivery_date: ..., note: ..., lines: [{sku: ..., description: ..., quantity: a number or null}]} Rules: - is_purchase_order: true only for a purchase order as described above. When it is false, put one short sentence in reason saying what the email is instead, and leave lines empty. - certain: false when you are not sure the email is a purchase order. When in doubt, count it as one and set certain to false: a person can delete an extra row, but a missed order is lost. You cannot see attachments, so an email that says its order is attached - 'please find our PO attached', or only 'see attached' - counts as a purchase order with certain false and no lines. - Copy every value exactly as the email writes it. Never guess, convert, total, or fill in a value that merely sounds right. A value the email does not give is an empty string. - po_number: the buyer's purchase order number or order reference. - customer: the company placing the order, or the person when no company is named. - order_date and delivery_date: the date of the order and the delivery date the buyer asks for, as the email writes them. Leave order_date empty when the email itself gives no order date: the RECEIVED date above is when it arrived, not an order date. - note: an empty string, unless the email says this order replaces, changes or repeats an earlier one - then say which in a few words, such as 'Replaces PO 4512'. - lines: one entry per item ordered, in the email's order. sku is the product code, item number or part number as written, or an empty string when the line has none. description is the item's name or description as written. quantity is how many were ordered, as a JSON number with no units, no thousands separators and a dot for any decimals (1500, not 1,500; 2.5, not 2,5), reading the email's numbers the way the email itself writes them; null when the email does not say or you cannot tell.'. Save it as extraction. Maximum response length: 8000

Change aiReply to extraction.response_text.Change aiReply to extraction.response_text.

On error

Stop and report a problem: The AI could not read the email '@email.subject' from email.from, so nothing was logged: aiError.message.Stop and report a problem: The AI could not read the email '@email.subject' from email.from, so nothing was logged: aiError.message.

Run Python code on reply, standing_note, and received and save it as order.Run Python code on reply, standing_note, and received and save it as order.

If order.problem isn't emptyIf order.problem isn't empty

Yes

Stop and report a problem: Could not log the email '@email.subject' from email.from: order.problem.Stop and report a problem: Could not log the email '@email.subject' from email.from: order.problem.

No

No steps

If order.is_purchase_order is noIf order.is_purchase_order is no

Yes

Finish the run: Not a purchase order, so nothing was logged. order.reason.Finish the run: Not a purchase order, so nothing was logged. order.reason.

No

No steps

Set productRows to an empty list.Set productRows to an empty list.

Set productColumns to an empty list.Set productColumns to an empty list.

If a step below fails, save the error as productListErrorIf a step below fails, save the error as productListError

Try

Google SheetsRead the rows of spreadsheetUrl. Save it as productList.Read the rows of spreadsheetUrl. Save it as productList.

Change productRows to productList.rows.Change productRows to productList.rows.

Change productColumns to productList.headers.Change productColumns to productList.headers.

On error

Stop and report a problem: Could not read the '@productsTab' tab, so the purchase order in the email '@email.subject' from email.from was not logged: productListError.message.Stop and report a problem: Could not read the '@productsTab' tab, so the purchase order in the email '@email.subject' from email.from was not logged: productListError.message.

If productColumns doesn't contain SKUIf productColumns doesn't contain SKU

Yes

Stop and report a problem: The '@productsTab' tab needs a column headed exactly 'SKU', so the purchase order in the email '@email.subject' from email.from was not logged. Give the tab the headers SKU, Product name and Unit price in its first row.Stop and report a problem: The '@productsTab' tab needs a column headed exactly 'SKU', so the purchase order in the email '@email.subject' from email.from was not logged. Give the tab the headers SKU, Product name and Unit price in its first row.

No

No steps

If productColumns doesn't contain Product nameIf productColumns doesn't contain Product name

Yes

Stop and report a problem: The '@productsTab' tab needs a column headed exactly 'Product name', so the purchase order in the email '@email.subject' from email.from was not logged. Give the tab the headers SKU, Product name and Unit price in its first row.Stop and report a problem: The '@productsTab' tab needs a column headed exactly 'Product name', so the purchase order in the email '@email.subject' from email.from was not logged. Give the tab the headers SKU, Product name and Unit price in its first row.

No

No steps

If productColumns doesn't contain Unit priceIf productColumns doesn't contain Unit price

Yes

Stop and report a problem: The '@productsTab' tab needs a column headed exactly 'Unit price', so the purchase order in the email '@email.subject' from email.from was not logged. Give the tab the headers SKU, Product name and Unit price in its first row.Stop and report a problem: The '@productsTab' tab needs a column headed exactly 'Unit price', so the purchase order in the email '@email.subject' from email.from was not logged. Give the tab the headers SKU, Product name and Unit price in its first row.

No

No steps

Set loggedIds to an empty list.Set loggedIds to an empty list.

If a step below fails, save the error as loggedIdsErrorIf a step below fails, save the error as loggedIdsError

Try

Google SheetsRead column 'M' of ordersTab in spreadsheetUrl. Save it as loggedColumn.Read column 'M' of ordersTab in spreadsheetUrl. Save it as loggedColumn.

Change loggedIds to loggedColumn.values.Change loggedIds to loggedColumn.values.

On error

Stop and report a problem: Could not read the '@ordersTab' tab to check whether the email '@email.subject' from email.from is already logged, so its purchase order was not logged: loggedIdsError.message.Stop and report a problem: Could not read the '@ordersTab' tab to check whether the email '@email.subject' from email.from is already logged, so its purchase order was not logged: loggedIdsError.message.

If loggedIds contains email.email_idIf loggedIds contains email.email_id

Yes

Finish the run: Already logged: the email '@email.subject' from email.from is in the '@ordersTab' tab under its Gmail message ID, so it was not logged again.Finish the run: Already logged: the email '@email.subject' from email.from is in the '@ordersTab' tab under its Gmail message ID, so it was not logged again.

No

No steps

Set received to email.date.Set received to email.date.

If order.received_iso isn't blankIf order.received_iso isn't blank

Yes

Replace T with in order.received_iso formatted as an ISO 8601 timestamp truncated to 16 characters using as the ellipsis and save it as received.Replace T with in order.received_iso formatted as an ISO 8601 timestamp truncated to 16 characters using as the ellipsis and save it as received.

No

No steps

Run Python code on lines, skus, names, prices, order, received, and email_id and save it as priced.Run Python code on lines, skus, names, prices, order, received, and email_id and save it as priced.

Set rowsToLog to priced.rows.Set rowsToLog to priced.rows.

Count the items in rowsToLog and save it as lineCount.Count the items in rowsToLog and save it as lineCount.

Set linesWritten to 0.Set linesWritten to 0.

If a step below fails, save the error as logErrorIf a step below fails, save the error as logError

Try

For each rowToAdd in rowsToLog:For each rowToAdd in rowsToLog:

Google Sheets Add a row to ordersTab in spreadsheetUrl. Save it as logged. Add a row to ordersTab in spreadsheetUrl. Save it as logged.

Add 1 to linesWritten. Add 1 to linesWritten.

On error

Stop and report a problem: Google Sheets stopped after linesWritten of the lineCount lines of the purchase order in the email '@email.subject' from email.from were added to the '@ordersTab' tab: logError.message. Add the missing lines by hand: once any line of an email is in the tab, the workflow treats that email as logged and does not add the rest.Stop and report a problem: Google Sheets stopped after linesWritten of the lineCount lines of the purchase order in the email '@email.subject' from email.from were added to the '@ordersTab' tab: logError.message. Add the missing lines by hand: once any line of an email is in the tab, the workflow treats that email as logged and does not add the rest.

Finish the run: Logged lineCount line(s) from the email '@email.subject' to the '@ordersTab' tab.Finish the run: Logged lineCount line(s) from the email '@email.subject' to the '@ordersTab' tab.