Turn order emails into spreadsheet rows

Every hour, the order emails matching your Gmail search are read from your inbox, an AI pulls out who is ordering and every item and quantity, and one row per item is added to the tab you name in your spreadsheet. Each row records the customer, order reference, item, quantity, requested delivery date and the email it came from, so any row can be traced back. An order that gives no reference or delivery date is still logged, with 'Not given' in that column.

The workflow refuses to guess. If an email is not clearly an order, or any item's name or quantity is missing or unclear, nothing from it is written and it is labelled 'Order needs review' for a person. Handled emails are labelled 'Order logged' and both labels are left out of the search, so no order is recorded twice. If the AI cannot be reached or the sheet will not take rows, the emails are left unlabelled to be tried again next hour and the run is marked as failed, saying what went wrong.

You need Gmail, Google Sheets and OpenAI connected, a link to your spreadsheet, the tab's name with the six headings in row 1, and a Gmail search that finds your order emails.

Run every hour.Run every hour.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set sheetName to blank.Set sheetName to blank.

Set orderEmailSearch to blank.Set orderEmailSearch to blank.

Set maxEmails to 25.Set maxEmails to 25.

Set loggedLabel to Order logged.Set loggedLabel to Order logged.

Set reviewLabel to Order needs review.Set reviewLabel to Order needs review.

Set tabHeadings to an empty list.Set tabHeadings to an empty list.

If a step below fails, save the error as tabErrorIf a step below fails, save the error as tabError

Try

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

Change tabHeadings to orderTab.headers.Change tabHeadings to orderTab.headers.

On error

Stop and report a problem: Could not open the '@sheetName' tab of your order spreadsheet (tabError.message). Check the link in 'spreadsheetUrl' and the tab name in 'sheetName', then run it again.Stop and report a problem: Could not open the '@sheetName' tab of your order spreadsheet (tabError.message). Check the link in 'spreadsheetUrl' and the tab name in 'sheetName', then run it again.

If tabHeadings is emptyIf tabHeadings is empty

Yes

Stop and report a problem: The '@sheetName' tab is empty. Type these headings into row 1, one per column and in this order, then run it again: Customer, Order reference, Item, Quantity, Requested delivery date, Gmail message ID.Stop and report a problem: The '@sheetName' tab is empty. Type these headings into row 1, one per column and in this order, then run it again: Customer, Order reference, Item, Quantity, Requested delivery date, Gmail message ID.

No

No steps

Take the first 6 items of tabHeadings and save it as firstHeadings.Take the first 6 items of tabHeadings and save it as firstHeadings.

Join firstHeadings with , and save it as headingLine.Join firstHeadings with , and save it as headingLine.

If headingLine in lowercase is not "customer, order reference, item, quantity,…If headingLine in lowercase is not "customer, order reference, item, quantity,…

Yes

Stop and report a problem: Rows are added to the '@sheetName' tab column by column, so row 1 must read: Customer, Order reference, Item, Quantity, Requested delivery date, Gmail message ID. It reads: headingLine. Fix the headings, then run it again.Stop and report a problem: Rows are added to the '@sheetName' tab column by column, so row 1 must read: Customer, Order reference, Item, Quantity, Requested delivery date, Gmail message ID. It reads: headingLine. Fix the headings, then run it again.

No

No steps

GmailList emails matching '@orderEmailSearch -label:@loggedLabel -label:@reviewLabel'. Save it as inbox.List emails matching '@orderEmailSearch -label:@loggedLabel -label:@reviewLabel'. Save it as inbox.

Set rowsAdded to 0.Set rowsAdded to 0.

Set ordersLogged to 0.Set ordersLogged to 0.

Set reviewNotes to an empty list.Set reviewNotes to an empty list.

Set failures to an empty list.Set failures to an empty list.

Set aiFailed to an empty list.Set aiFailed to an empty list.

Set lastAiProblem to blank.Set lastAiProblem to blank.

For each email in inbox.emails:For each email in inbox.emails:

Use email.subject, or (no subject) if it is empty and save it as subject.Use email.subject, or (no subject) if it is empty and save it as subject.

Trim email.body and save it as emailBody.Trim email.body and save it as emailBody.

Set reviewReason to it has no text to read; an order sent only as an attachment has to be typed in by hand.Set reviewReason to it has no text to read; an order sent only as an attachment has to be typed in by hand.

Set orderRows to an empty list.Set orderRows to an empty list.

If emailBody isn't emptyIf emailBody isn't empty

Yes

Set extracted to an empty record.Set extracted to an empty record.

Set aiProblem to blank.Set aiProblem to blank.

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

Try

Run an AI agent: Copy the order out of this email. From: email.from Subject: subject emailBody and save it as reading.Run an AI agent: Copy the order out of this email. From: email.from Subject: subject emailBody and save it as reading.

Change extracted to reading.output.Change extracted to reading.output.

On error

Change aiProblem to aiError.message.Change aiProblem to aiError.message.

If aiProblem isn't emptyIf aiProblem isn't empty

Yes

Add '@subject' to the end of aiFailed and save it as aiFailed.Add '@subject' to the end of aiFailed and save it as aiFailed.

Change lastAiProblem to aiProblem.Change lastAiProblem to aiProblem.

Skip to the next item.Skip to the next item.

No

No steps

Trim extracted.customer and save it as customer.Trim extracted.customer and save it as customer.

Use extracted.order_reference, or Not given if it is empty and save it as orderReference.Use extracted.order_reference, or Not given if it is empty and save it as orderReference.

Use extracted.delivery_date, or Not given if it is empty and save it as deliveryDate.Use extracted.delivery_date, or Not given if it is empty and save it as deliveryDate.

Set incompleteLines to 0.Set incompleteLines to 0.

For each line in extracted.items:For each line in extracted.items:

Trim line.item and save it as itemName. Trim line.item and save it as itemName.

If itemName isn't empty and line.quantity is greater than 0 If itemName isn't empty and line.quantity is greater than 0

Yes

Add [customer, orderReference, itemName, line.quantity, deliveryDate, email.id] to the end of orderRows and save it as orderRows.Add [customer, orderReference, itemName, line.quantity, deliveryDate, email.id] to the end of orderRows and save it as orderRows.

No

Add 1 to incompleteLines.Add 1 to incompleteLines.

If extracted.is_order is noIf extracted.is_order is no

Yes

Change reviewReason to the AI did not see an order in it.Change reviewReason to the AI did not see an order in it.

Else if @extracted.is_order is not "yes"

Change reviewReason to the AI could not tell whether it places an order.Change reviewReason to the AI could not tell whether it places an order.

Else if @customer is empty

Change reviewReason to it does not say who the customer is.Change reviewReason to it does not say who the customer is.

Else if @orderRows is empty

Change reviewReason to no ordered item was found in it.Change reviewReason to no ordered item was found in it.

Else if @incompleteLines is greater than 0

Change reviewReason to an item's name or quantity is missing or unclear.Change reviewReason to an item's name or quantity is missing or unclear.

Otherwise

Change reviewReason to blank.Change reviewReason to blank.

No

No steps

If reviewReason isn't emptyIf reviewReason isn't empty

Yes

If a step below fails, save the error as reviewLabelErrorIf a step below fails, save the error as reviewLabelError

Try

GmailAdd label reviewLabel to email email. Save it as flagged.Add label reviewLabel to email email. Save it as flagged.

Add '@subject' (reviewReason) to the end of reviewNotes and save it as reviewNotes.Add '@subject' (reviewReason) to the end of reviewNotes and save it as reviewNotes.

On error

Add '@subject' needs review (reviewReason) but the label could not be added (reviewLabelError.message) to the end of failures and save it as failures.Add '@subject' needs review (reviewReason) but the label could not be added (reviewLabelError.message) to the end of failures and save it as failures.

Skip to the next item.Skip to the next item.

No

No steps

Set rowsWritten to 0.Set rowsWritten to 0.

Set writeProblem to blank.Set writeProblem to blank.

For each orderRow in orderRows:For each orderRow in orderRows:

If a step below fails, save the error as sheetErrorIf a step below fails, save the error as sheetError

Try

Google SheetsAdd a row to sheetName in spreadsheetUrl. Save it as added.Add a row to sheetName in spreadsheetUrl. Save it as added.

Add 1 to rowsWritten.Add 1 to rowsWritten.

On error

Change writeProblem to sheetError.message.Change writeProblem to sheetError.message.

Stop repeating.Stop repeating.

Change rowsAdded to rowsAdded plus rowsWritten.Change rowsAdded to rowsAdded plus rowsWritten.

If writeProblem isn't emptyIf writeProblem isn't empty

Yes

If rowsWritten is 0If rowsWritten is 0

Yes

Add no rows could be added to the '@sheetName' tab (writeProblem), so '@subject' and any emails after it were left unlabelled to be tried again next hour to the end of failures and save it as failures.Add no rows could be added to the '@sheetName' tab (writeProblem), so '@subject' and any emails after it were left unlabelled to be tried again next hour to the end of failures and save it as failures.

No

If a step below fails, save the error as partLabelErrorIf a step below fails, save the error as partLabelError

Try

GmailAdd label reviewLabel to email email. Save it as partlyFlagged.Add label reviewLabel to email email. Save it as partlyFlagged.

On error

No steps

Add only the first rowsWritten line(s) of '@subject' were added before the sheet stopped taking rows (writeProblem); the email is labelled '@reviewLabel' so the rest can be added by hand, and any emails after it are tried again next hour to the end of failures and save it as failures.Add only the first rowsWritten line(s) of '@subject' were added before the sheet stopped taking rows (writeProblem); the email is labelled '@reviewLabel' so the rest can be added by hand, and any emails after it are tried again next hour to the end of failures and save it as failures.

Stop repeating.Stop repeating.

No

No steps

If a step below fails, save the error as loggedLabelErrorIf a step below fails, save the error as loggedLabelError

Try

GmailAdd label loggedLabel to email email. Save it as logged.Add label loggedLabel to email email. Save it as logged.

Add 1 to ordersLogged.Add 1 to ordersLogged.

On error

Add the rows for '@subject' were added but the '@loggedLabel' label could not be put on the email (loggedLabelError.message): label it by hand, or its rows will be added again next hour to the end of failures and save it as failures.Add the rows for '@subject' were added but the '@loggedLabel' label could not be put on the email (loggedLabelError.message): label it by hand, or its rows will be added again next hour to the end of failures and save it as failures.

If aiFailed isn't emptyIf aiFailed isn't empty

Yes

Join aiFailed with , and save it as aiFailedList.Join aiFailed with , and save it as aiFailedList.

Add the AI could not read aiFailedList (lastAiProblem), so they were left unlabelled to be read again next hour to the end of failures and save it as failures.Add the AI could not read aiFailedList (lastAiProblem), so they were left unlabelled to be read again next hour to the end of failures and save it as failures.

No

No steps

Set reportLines to an empty list.Set reportLines to an empty list.

Add Added rowsAdded row(s) from ordersLogged order email(s). to the end of reportLines and save it as reportLines.Add Added rowsAdded row(s) from ordersLogged order email(s). to the end of reportLines and save it as reportLines.

If reviewNotes isn't emptyIf reviewNotes isn't empty

Yes

Join reviewNotes with ; and save it as reviewList.Join reviewNotes with ; and save it as reviewList.

Add Labelled '@reviewLabel' for a person to check: reviewList. to the end of reportLines and save it as reportLines.Add Labelled '@reviewLabel' for a person to check: reviewList. to the end of reportLines and save it as reportLines.

No

No steps

If the length of inbox.emails is greater than or equal to maxEmailsIf the length of inbox.emails is greater than or equal to maxEmails

Yes

Add Only maxEmails emails are read per run, so more may be waiting; they are read next hour. to the end of reportLines and save it as reportLines.Add Only maxEmails emails are read per run, so more may be waiting; they are read next hour. to the end of reportLines and save it as reportLines.

No

No steps

Join reportLines with a space and save it as report.Join reportLines with a space and save it as report.

If failures isn't emptyIf failures isn't empty

Yes

Join failures with ; and save it as problemList.Join failures with ; and save it as problemList.

Stop and report a problem: report Needs attention: problemList.Stop and report a problem: report Needs attention: problemList.

No

No steps

Finish the run: report.Finish the run: report.