Reads order emails hourly, has AI pull out each line item, and appends it to your order sheet.
Uses
Gmail
Google Sheets
OpenAI
Turn order emails into spreadsheet rows
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 tabErrorTry
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 emptyYes
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 emptyYes
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 aiErrorTry
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 emptyYes
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 0Yes
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 noYes
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 emptyYes
If a step below fails, save the error as reviewLabelErrorIf a step below fails, save the error as reviewLabelErrorTry
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 sheetErrorTry
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 emptyYes
If rowsWritten is 0If rowsWritten is 0Yes
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 partLabelErrorTry
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 loggedLabelErrorTry
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 emptyYes
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 emptyYes
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 maxEmailsYes
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 emptyYes
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.