Automating Invoice Processing: From Email to Google Sheets

[Raian]

Efficient financial management is crucial, whether you’re running a business of any size or managing personal finances. And let’s face it—having optimized processes makes life much easier.

One of the most tedious tasks in budget tracking is working with invoices—a maximally exhausting process, especially when there’s an endless stream of them flooding your inbox.

But here’s the good news: you can automate this process once and for all.

Let me show you how.

How It Works

1. The Scenario Begins: The process is triggered when a new email with an invoice attachment arrives in your inbox.
2. Extracting and Processing the Document: The invoice file is identified, extracted, and converted into a structured format.
3. Saving to Google Sheets: The structured data is saved in a standardized format in your spreadsheet.

---

Step 1: Receiving the File

To start, we use two key nodes:

1. New Attachment Node: This node activates whenever an email with an attachment arrives in your inbox.
2. Get Attachment Node: This node extracts the binary file from the email.

Between these nodes, we add a filter to ensure that only PDF files are processed, avoiding unnecessary triggers.

Here’s how the filter is set up:

---

Step 2: Processing the Document

Once the file is received, it goes through several steps:

1. PDF-to-TXT Conversion
   The binary file is passed to a built-in converter tool, which seamlessly formats the PDF into a readable txt format.

   No external keys, subscriptions, or services (like PDF.co) are required—everything works out of the box!

2. JavaScript Processing
   A JavaScript node extracts the text content from the file for further processing. Here’s a simple example:

   import fs from 'fs';
   
   export default async function run({ execution_id, input, data, store, db }) {
       const filePath = data["{{43.result.file.content}}"];
       return {
           fileContent: fs.readFileSync(filePath, { encoding: 'utf-8' })
       };
   }
   

3. ChatGPT for Structuring Data
   The extracted text is sent to ChatGPT. A custom prompt is used to define the exact structure of the data. For example:

   Extract the content from the document and format it as JSON with the following structure (example):
   
   {
       "Amount": "$93.50",
       "LineItems": "1 x Web Design ($85.00)",
       "FromAddress": "DEMO - Sliced Invoices, Suite 5A-1204, 123 Somewhere Street, Your City AZ 12345",
       "DueDate": "January 31, 2016",
       "ParsingDate": "2024-12-24",
       "IsInvoice": true
   }
   
   Instructions:
   - Set `"IsInvoice": true` if the document contains clear invoice-related attributes such as `Invoice Number`, `Due Date`, or `Total Amount`.
   - Set `"IsInvoice": false` if such attributes are missing or if the document is not an invoice.
   - Leave other fields blank if the relevant data is missing.
   
   Use this content for processing:
   {{$7.fileContent}}
   

4. JSON Parse
   The JSON string returned by ChatGPT is parsed into a structured array format using a JSON Parse function. This step makes it easy to map the required data fields to your Google Sheets.

Pay attention to the IsInvoice field, which you also receive from ChatGPT. This serves as a filter to ensure that the processed file is indeed an invoice and not, for instance, a brochure or a catalog.
Here’s how the filter looks:

---

Step 3: Storing Data

The final step involves mapping the structured data to your Google Sheets. Each field is neatly organized into predefined columns, creating a well-structured table.

Here’s an example of the result:

---

Isn’t it amazing? Now, you’ll never have to worry about disorganized invoices or missing important receipts. This setup can save you and your team dozens of hours!

---

Want More Cases Like This?

Leave a comment, and I’ll share more automation solutions!

Join Latenode, and you too can create automations of any complexity effortlessly!