Convert emailed timesheets into QuickBooks invoices with OCR, AI, Gmail and Sheets

AI-Powered Timesheet → Invoice Automation (Gmail + OCR + AI + Google Sheets + QuickBooks) > Note: This workflow uses sticky notes extensively to document each logical section of the automation. Sticky notes are mandatory and already included to explain OCR, AI parsing, folder logic, duplicate handling, and QuickBooks steps. This workflow automates the full lifecycle of timesheet-based invoicing — from emailed timesheets to structured Google Sheets records and finalized invoices in QuickBooks Online. It is designed for real-world billing scenarios, including split weeks across months, zero-hour months, duplicate prevention, and first-week-of-year edge cases. --- What This Workflow Does - Listens to Gmail for timesheet emails with attachments - Splits and processes each attachment independently - Extracts text using OCR (no hardcoded API keys) - Uses AI to parse month-wise billable hours - Correctly splits weeks spanning multiple months - Looks up Customer and PO details from Google Sheets - Organizes files in Client → Employee → Year folders in Google Drive - Reuses existing invoice sheets or creates new ones - Prevents duplicate invoice rows - Automatically finds or creates customers in QuickBooks - Creates invoices in QuickBooks using validated data --- High-Level Workflow Stages 1. Gmail Intake and Attachment Loop 2. OCR Text Extraction 3. AI-Based Timesheet Parsing 4. Month Normalization and Validation 5. Customer & PO Lookup 6. Drive Folder Discovery and Creation 7. Invoice Sheet Reuse or Creation 8. Duplicate and Edge-Case Handling 9. Append Invoice Rows to Google Sheets 10. Create / Update Customers in QuickBooks 11. Create Invoices in QuickBooks Each of these stages is clearly documented with sticky notes inside the workflow canvas. --- Quick Setup Instructions 1. Import the workflow JSON into your n8n instance 2. Configure credentials for: - Gmail - Google Drive - Google Sheets - OpenAI or Google Gemini - QuickBooks Online 3. Verify the OCR HTTP node: - Default URL: https://universal-file-to-text-extractor.vercel.app/extract - No hardcoded API keys are used 4. Configure Get Customer Info From PO Sheet: - Spreadsheet ID - Sheet name and column mappings 5. Ensure the Client Invoices root folder exists in Google Drive 6. Send a test timesheet email 7. Execute the workflow once manually 8. Activate the workflow --- Who This Workflow Is For - Agencies and consultancies billing from emailed timesheets - Finance and operations teams using Google Workspace + QuickBooks - Staffing firms with monthly or bi-weekly contractor billing - Teams that want a fully auditable, zero-manual invoice process --- Requirements - n8n instance - Gmail account receiving timesheet emails - Google Drive and Google Sheets - OpenAI or Google Gemini API - OCR API endpoint (configurable) - QuickBooks Online account - Customer PO Google Sheet containing: - Email - Customer Name - Company Name - Customer Account Number - PO Number - Item - Folder Name - Invoice range - Due Date Calculation --- How It Works (Detailed) 1. Email Intake and Attachment Loop - Gmail Trigger polls for timesheet emails - Attachments are split so each file is processed independently - Sticky notes explain the intake and loop logic --- 2. OCR Extraction - Each attachment is sent to the OCR API - PDFs and images are converted to plain text - OCR logic is documented via sticky notes --- 3. AI Timesheet Parsing (Month-Wise) - AI extracts data only from BILLABLE HOURS sections - Outputs strict JSON: - Employee Name - Client Name - Month - Year - Week Start Date - Week End Date - Total Billable Hours Special handling included: - Split weeks across months - Zero-hour months still included - No guessed or inferred dates --- 4. Month Normalization and Validation - AI output is normalized into a month array - Each month is processed independently - Invalid or zero-hour entries are skipped --- 5. Customer and PO Lookup - Sender email is matched in the PO sheet - Retrieved values drive: -