Extract Colombian invoice data from Drive to Sheets with GPT-4o

Automate Colombian Cashflow Data Extraction to Google Sheets with AI

Who’s it for

This workflow is designed for finance professionals, accountants, small business owners in Colombia, or anyone needing to automate the extraction of invoice data and its entry into Google Sheets. It's particularly useful for handling Colombian tax and legal specifics.

How it works / What it does

This workflow automates the process of extracting critical data from invoices and receipts (PDFs and JPEGs) and organizing it in a Google Sheet:

• Triggers: The workflow initiates when a new file is created or an existing file is updated in a designated Google Drive folder.
• File Handling: It first downloads the detected file.
• Routing: A "Switch" node intelligently routes the file based on its extension – one path for PDFs and another for JPEGs.
• Data Extraction:
  • For PDF files, it directly extracts all text content from the document.
  • For JPEG image files, it utilizes an AI Agent (Azure OpenAI) to process the image and extract its textual content.
• AI-Powered Reasoning: Two "Reasoning Agent" nodes (Azure OpenAI Chat Models) act as a specialized "Colombian Tax and Legal Extraction Agent". They parse the extracted text from invoices to pull out structured data in JSON format, including:
  • Vendor name.
  • Modification date.
  • Line items with detailed description, sub_total, iva_value, total_amount, category, and sub_category.
  • Specific Colombian tax fields like Retefuente and ReteICA.
  • The number of items generated.
• Output Parsing: A "Structured Output Parser" node ensures that the AI's output strictly adheres to a predefined JSON schema, guaranteeing consistent data formatting.
• Data Preparation: "Edit Field" nodes ensure the AI's extracted data is in a valid format.
• Item Splitting: "Split data" nodes separate the 'items' array from the AI's output, allowing each individual line item from the invoice to be processed as a separate entry for the Google Sheet.
• Google Sheet Integration: Finally, "Fill Template" nodes append the fully processed invoice data (per line item) into your designated Google Sheet.

How to set up

1. Google Drive Credentials: Ensure you have configured your Google Drive OAuth2 API credentials in n8n.
2. Azure OpenAI Credentials: Set up your Azure OpenAI API credentials, ensuring access to models like gpt-4o. Or you can simply use your traditional OpenAI or others LLMs.
3. Google Sheets Credentials: Configure your Google Sheets OAuth2 API credentials.
4. Google Drive Folder ID: In the "1a. Updated file trigger" and "1b. Created file trigger" nodes, update the folderToWatch parameter with your specific Google Drive Folder ID.
5. Google Sheet ID and Sheet Name: In the "8. Fill Template" and "8. Fill Template1" nodes, update the documentId and sheetName parameters with your specific Google Sheet ID and the name of the sheet where data should be appended.

Requirements

• An active n8n instance.
• A Google Drive account for file uploads.
• A Google Sheets account for data storage.
• An Azure OpenAI account with access to chat models (e.g., gpt-4o) for the "Azure OpenAI Chat Model" nodes and "Extract Data Agent".

How to customize the workflow

• AI Extraction Prompts: Modify the prompt instructions in the "5. Reasoning Agent" and "5. Reasoning Agent1" nodes if you need to extract different data points or change the output format.
• Google Sheet Column Mappings: Adjust the columns mapping in the "8. Fill Template" and "8. Fill Template1" nodes to match your specific Google Sheet headers and data requirements.
• File Types: Extend the "3. Route" node to handle additional file types (e.g., DOCX, PNG) by adding new conditions and corresponding extraction nodes.