Turn receipts and invoices into expense rows

Paste the links to a batch of receipts and invoices and run it. Each file is read, its supplier, date, invoice number, total, tax and currency are pulled out, and a row is added to your 'Expenses' tab.

Values are written exactly as they were read, so nothing becomes a formula or a reinterpreted date. A wrong figure costs more than a missing row, so it refuses to guess: if no supplier, date or total could be read, or the reader was not confident, the row goes to a 'Needs review' tab with a last column saying what is wrong. A figure not found is left blank rather than written as zero. Links already on either tab are skipped, so you can add receipts to the list and run it again. A file that cannot be opened is named in the run's result and tried again next time, and the rest of the batch still goes through.

You need an OpenAI connection (it reads the receipts) and a Google Sheets connection, a spreadsheet with an 'Expenses' tab and a 'Needs review' tab headed for the columns the rows fill, its link, and the receipt links - each must open the file itself without signing in.

Set receiptLinks to blank.Set receiptLinks to blank.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set expensesTab to Expenses.Set expensesTab to Expenses.

Set reviewTab to Needs review.Set reviewTab to Needs review.

Set maxDocuments to 25.Set maxDocuments to 25.

Get today's date and save it as recordedOn.Get today's date and save it as recordedOn.

Set recordedLinks to an empty list.Set recordedLinks to an empty list.

If a step below fails, save the error as recordedLinksErrorIf a step below fails, save the error as recordedLinksError

Try

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

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

Get column_8 from every item in expenseTabRows.rows and save it as expenseLinks.Get column_8 from every item in expenseTabRows.rows and save it as expenseLinks.

Get column_8 from every item in reviewTabRows.rows and save it as reviewLinks.Get column_8 from every item in reviewTabRows.rows and save it as reviewLinks.

Combine expenseLinks and reviewLinks into one list and save it as recordedLinks.Combine expenseLinks and reviewLinks into one list and save it as recordedLinks.

On error

Stop and report a problem: Could not read the links already recorded on the '@expensesTab' and '@reviewTab' tabs (recordedLinksError.message), so nothing was read or added: without them, receipts recorded earlier could be added twice. Check the address in 'spreadsheetUrl', that both tabs exist with exactly those names, and that the Google account connected to Lodol can edit the sheet, then run it again.Stop and report a problem: Could not read the links already recorded on the '@expensesTab' and '@reviewTab' tabs (recordedLinksError.message), so nothing was read or added: without them, receipts recorded earlier could be added twice. Check the address in 'spreadsheetUrl', that both tabs exist with exactly those names, and that the Google account connected to Lodol can edit the sheet, then run it again.

Set documentsRead to 0.Set documentsRead to 0.

Set expensesAdded to 0.Set expensesAdded to 0.

Set sentForReview to 0.Set sentForReview to 0.

Set alreadyRecorded to 0.Set alreadyRecorded to 0.

Set linksLeft to 0.Set linksLeft to 0.

Set unreadLinks to an empty list.Set unreadLinks to an empty list.

Set lastReadError to blank.Set lastReadError to blank.

For each rawLink in receiptLinks split by a new line:For each rawLink in receiptLinks split by a new line:

Trim rawLink and save it as link.Trim rawLink and save it as link.

If link is emptyIf link is empty

Yes

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

No

No steps

If recordedLinks contains link is yesIf recordedLinks contains link is yes

Yes

Add 1 to alreadyRecorded.Add 1 to alreadyRecorded.

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

No

No steps

If documentsRead is greater than or equal to maxDocumentsIf documentsRead is greater than or equal to maxDocuments

Yes

Add 1 to linksLeft.Add 1 to linksLeft.

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

No

No steps

Add 1 to documentsRead.Add 1 to documentsRead.

If link doesn't start with http (ignoring case)If link doesn't start with http (ignoring case)

Yes

Set badLinkRow to [recordedOn, , , , , , , link, This is not a web address. Use a link that opens the PDF or image directly.].Set badLinkRow to [recordedOn, , , , , , , link, This is not a web address. Use a link that opens the PDF or image directly.].

Google SheetsAdd a row to reviewTab in spreadsheetUrl.Add a row to reviewTab in spreadsheetUrl.

Add link to the end of recordedLinks and save it as recordedLinks.Add link to the end of recordedLinks and save it as recordedLinks.

Add 1 to sentForReview.Add 1 to sentForReview.

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

No

No steps

Set invoice to nothing.Set invoice to nothing.

If a step below fails, save the error as readErrorIf a step below fails, save the error as readError

Try

Run an AI agent: Read the attached receipt or invoice and pull out who issued it, its date, its number, it… and save it as receiptReading.Run an AI agent: Read the attached receipt or invoice and pull out who issued it, its date, its number, it… and save it as receiptReading.

Change invoice to receiptReading.output.Change invoice to receiptReading.output.

On error

Add link to the end of unreadLinks and save it as unreadLinks.Add link to the end of unreadLinks and save it as unreadLinks.

Change lastReadError to readError.message.Change lastReadError to readError.message.

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

Set supplier to blank.Set supplier to blank.

If a step below fails, save the error as missingSupplierIf a step below fails, save the error as missingSupplier

Try

Trim invoice.vendor_name and save it as supplier.Trim invoice.vendor_name and save it as supplier.

On error

Change supplier to blank.Change supplier to blank.

Set invoiceDate to blank.Set invoiceDate to blank.

If a step below fails, save the error as missingInvoiceDateIf a step below fails, save the error as missingInvoiceDate

Try

Trim invoice.invoice_date and save it as invoiceDate.Trim invoice.invoice_date and save it as invoiceDate.

On error

Change invoiceDate to blank.Change invoiceDate to blank.

Set invoiceNumber to blank.Set invoiceNumber to blank.

If a step below fails, save the error as missingInvoiceNumberIf a step below fails, save the error as missingInvoiceNumber

Try

Trim invoice.invoice_number and save it as invoiceNumber.Trim invoice.invoice_number and save it as invoiceNumber.

On error

Change invoiceNumber to blank.Change invoiceNumber to blank.

Set currency to blank.Set currency to blank.

If a step below fails, save the error as missingCurrencyIf a step below fails, save the error as missingCurrency

Try

Trim invoice.currency and save it as currency.Trim invoice.currency and save it as currency.

On error

Change currency to blank.Change currency to blank.

Set total to invoice.total.Set total to invoice.total.

Set tax to invoice.tax.Set tax to invoice.tax.

Set readerIsConfident to invoice.requires_review is false.Set readerIsConfident to invoice.requires_review is false.

Set reviewReason to blank.Set reviewReason to blank.

If supplier is emptyIf supplier is empty

Yes

Change reviewReason to reviewReasonNo supplier name was read.Change reviewReason to reviewReasonNo supplier name was read.

No

No steps

If invoiceDate is emptyIf invoiceDate is empty

Yes

Change reviewReason to reviewReasonNo date was read.Change reviewReason to reviewReasonNo date was read.

No

No steps

If total is emptyIf total is empty

Yes

Change reviewReason to reviewReasonNo total was read.Change reviewReason to reviewReasonNo total was read.

No

No steps

If readerIsConfident is noIf readerIsConfident is no

Yes

Change reviewReason to reviewReasonThe reader was not confident about this document.Change reviewReason to reviewReasonThe reader was not confident about this document.

No

No steps

For each problem in invoice.validation_errors:For each problem in invoice.validation_errors:

Set problemText to blank.Set problemText to blank.

If a step below fails, save the error as problemErrorIf a step below fails, save the error as problemError

Try

Trim problem.description and save it as problemText.Trim problem.description and save it as problemText.

On error

Change problemText to blank.Change problemText to blank.

If problemText isn't emptyIf problemText isn't empty

Yes

Change reviewReason to reviewReason@problemText .Change reviewReason to reviewReason@problemText .

No

No steps

Set totalCell to blank.Set totalCell to blank.

If total isn't emptyIf total isn't empty

Yes

Change totalCell to total.Change totalCell to total.

No

No steps

Set taxCell to blank.Set taxCell to blank.

If tax isn't emptyIf tax isn't empty

Yes

Change taxCell to tax.Change taxCell to tax.

No

No steps

Set expenseRow to [recordedOn, supplier, invoiceDate, invoiceNumber, totalCell, taxCell, currency, link].Set expenseRow to [recordedOn, supplier, invoiceDate, invoiceNumber, totalCell, taxCell, currency, link].

If reviewReason is emptyIf reviewReason is empty

Yes

Google SheetsAdd a row to expensesTab in spreadsheetUrl.Add a row to expensesTab in spreadsheetUrl.

Add link to the end of recordedLinks and save it as recordedLinks.Add link to the end of recordedLinks and save it as recordedLinks.

Add 1 to expensesAdded.Add 1 to expensesAdded.

No

Add reviewReason to the end of expenseRow and save it as reviewRow.Add reviewReason to the end of expenseRow and save it as reviewRow.

Google SheetsAdd a row to reviewTab in spreadsheetUrl.Add a row to reviewTab in spreadsheetUrl.

Add link to the end of recordedLinks and save it as recordedLinks.Add link to the end of recordedLinks and save it as recordedLinks.

Add 1 to sentForReview.Add 1 to sentForReview.

Build the text Added to '@expensesTab': @expensesAdded. Sent to '@reviewTab' for a person to check: @sentForReview. Already recorded, so skipped: @alreadyRecorded. and save it as runSummary.Build the text Added to '@expensesTab': @expensesAdded. Sent to '@reviewTab' for a person to check: @sentForReview. Already recorded, so skipped: @alreadyRecorded. and save it as runSummary.

If unreadLinks isn't emptyIf unreadLinks isn't empty

Yes

Join unreadLinks with a new line and save it as unreadList.Join unreadLinks with a new line and save it as unreadList.

Stop and report a problem: runSummary These links could not be opened or read, so nothing was recorded for them and they are tried again on the next run:Stop and report a problem: runSummary These links could not be opened or read, so nothing was recorded for them and they are tried again on the next run: unreadList The error was: lastReadError Each link must open the receipt itself (a PDF, PNG, JPG or WEBP file) without signing in. Fix or remove these links in 'receiptLinks'.

No

No steps

If linksLeft is greater than 0If linksLeft is greater than 0

Yes

Finish the run: runSummary Links left for the next run: linksLeft (at most maxDocuments are read per run). Run it again to carry on; links already recorded are skipped.Finish the run: runSummary Links left for the next run: linksLeft (at most maxDocuments are read per run). Run it again to carry on; links already recorded are skipped.

No

No steps

If documentsRead is 0If documentsRead is 0

Yes

Finish the run: Nothing new to read: every link in 'receiptLinks' is already on the '@expensesTab' or '@reviewTab' tab. Paste the links to new receipts and run it again.Finish the run: Nothing new to read: every link in 'receiptLinks' is already on the '@expensesTab' or '@reviewTab' tab. Paste the links to new receipts and run it again.

No

No steps

Finish the run: runSummary.Finish the run: runSummary.