Automate timesheet to invoice conversion with OpenAI, Gmail & Google Workspace

This workflow converts emailed timesheets into structured invoice rows in Google Sheets and stores them in the correct Google Drive folder structure. It: - Listens to Gmail for timesheet attachments - Runs OCR and AI parsing - Looks up Customer and PO data from a Google Sheet - Organizes files in Client → Employee → Year folders - Reuses an existing invoice sheet or creates a new one and writes the invoice row --- Quick Implementation Steps 1. Import the workflow JSON into your n8n instance. 2. Set up credentials for: - Gmail - Google Drive - Google Sheets - OpenAI 3. Check the OCR HTTP node: - Default URL: https://universal-file-to-text-extractor.vercel.app/extract 4. Configure "Get Customer Info From PO Sheet" with: - Spreadsheet ID - Correct sheet and column names 5. Confirm Gmail Trigger filter: - has:attachment (filename:timesheet OR subject:timesheet) and unread only 6. Ensure your Client Invoices root folder exists in Google Drive. 7. Test once with a sample timesheet email. 8. Activate the workflow. --- What It Does - Reads unread Gmail messages with timesheet attachments. - Splits and processes each attachment separately. - Sends files to OCR and converts them to text. - Uses OpenAI to extract: - Employee Name - Client Name - Week Start and End Dates - Total Billable Hours - Current Year - Looks up Customer and PO data from a Google Sheet: - Account Number - PO Number - Item Name - Folder Name - Invoice range - Due Date offset - Builds or finds: - Client folder - Employee folder - Year folder - Either: - Appends to an existing sheet for that employee and period, or - Creates a new sheet, sets timezone, moves it into the right folder, and adds the invoice row --- Who Is It For - Agencies and consultancies billing from emailed timesheets - Finance or ops teams managing many clients and employees in Google Workspace - Service providers that keep one sheet per employee per period - Anyone who wants to stop manually reading timesheets and filling invoice sheets --- Requirements - n8n instance - Gmail account with timesheet emails - Google Drive and Google Sheets - OpenAI API key - OCR API endpoint (or the default one) - Customer POs Google Sheet with: - Email - Customer Account Number - PO Number - Item - Folder Name - Invoice range - Due Date Calculation --- How It Works 1. Email Intake and Loop - Gmail Trigger - Polls every minute - Filter: unread + has attachment + timesheet in file name or subject - Split Binary Attachments - Creates one item per attachment - Loop: Process Each Attachment - Handles each timesheet file in sequence 2. OCR and AI Parsing - Extract Text from Attachment - Sends binary file to OCR endpoint - Returns plain text - Extract Timesheet Data (OpenAI) - Reads the text and outputs strict JSON with: - Employee Name - Client Name - Week Starting Date - Week Ending Date - Total Working Hours - Set Timesheet JSON Fields - Normalizes and stores: - Employee Name - Total Billable hours - Week Start Date and Week End Date - Client Name - Current Year 3. Customer and PO Lookup - Get Customer Info From PO Sheet - Looks up sender email - Pulls: - Customer Account Number - PO Number - Item - Folder Name - Invoice range - Due Date Calculation 4. Drive Folder Discovery - Search: Client Invoices Folder - Finds the main invoices root folder - Search or create: - Client folder using Client Name - Employee folder using Folder Name from PO sheet - Search: Year Folder - Looks for folder matching Current Year - If Year Folder does not exist: - Create Year Folder or Create Current Year Folder - Set: Invoice Range - Stores invoice range and Year Folder id 5. File Naming and Sheet Search - Set: File Name from Start and End Based Date Range - Builds: - File Name (Start Date Based) - File Name (End Date Based) - Handles weekly and 15 days invoice logic - Search: File By Start Date Name - Search: File By End Date Name - Merge: Combine Folder Search Results - Merges both search results - If: Invoice Range is 15 Days - Uses cust