{ "edges": [ { "to": "c4147239-207f-414e-b185-61137351bde9", "from": "55eb7432-ce93-470a-89f3-b97eba58f0bb", "toPort": "0", "fromPort": "ai_languageModel" }, { "to": "67200fe9-b12a-4c5a-998d-1b1791771f2b", "from": "8e2b4203-b162-4a1b-97ae-4d99c40cb4ee", "toPort": "0", "fromPort": "0" }, { "to": "8869ddc8-9058-4fd0-a9f6-fd8cdf4ce152", "from": "2f1a67be-2e59-4c6c-b3a5-1e0604c0b45a", "toPort": "0", "fromPort": "0" }, { "to": "1f1e51c8-918b-4284-b5b2-72cfceb4fb46", "from": "22dcfa29-8e16-408a-9b92-34a48b2658cf", "toPort": "0", "fromPort": "0" }, { "to": "c4147239-207f-414e-b185-61137351bde9", "from": "7e56bf01-4ba6-4beb-919b-786ce0ad2011", "toPort": "0", "fromPort": "0" }, { "to": "22dcfa29-8e16-408a-9b92-34a48b2658cf", "from": "c4147239-207f-414e-b185-61137351bde9", "toPort": "0", "fromPort": "0" }, { "to": "8e2b4203-b162-4a1b-97ae-4d99c40cb4ee", "from": "8869ddc8-9058-4fd0-a9f6-fd8cdf4ce152", "toPort": "0", "fromPort": "0" }, { "to": "7e56bf01-4ba6-4beb-919b-786ce0ad2011", "from": "8869ddc8-9058-4fd0-a9f6-fd8cdf4ce152", "toPort": "0", "fromPort": "0" }, { "to": "c4147239-207f-414e-b185-61137351bde9", "from": "87b360c0-219a-451e-8035-7de9a3d1d1c5", "toPort": "0", "fromPort": "ai_outputParser" }, { "to": "a384703f-fd64-4743-ad74-283d38d2ab1a", "from": "67200fe9-b12a-4c5a-998d-1b1791771f2b", "toPort": "0", "fromPort": "0" }, { "to": "b84f4ee1-1b5c-462a-aa28-a3728f0278b1", "from": "1f1e51c8-918b-4284-b5b2-72cfceb4fb46", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "22dcfa29-8e16-408a-9b92-34a48b2658cf", "name": "Transform Output", "type": "code", "params": { "jsCode": "// Static or dynamic employee information\nconst profile = $('On form submission').first().json;\nconst employee = {\n name: profile[\"Employee Name\"],\n department: profile[\"Department\"],\n tripPurpose: profile[\"Trip Purpose\"],\n fromDate: profile[\"From Date\"],\n toDate: profile[\"To Date\"],\n};\n\n// Collect parsed expense outputs\nconst items = $input.all();\nconst expenses = items.map((item, index) => {\n const data = item.json.output;\n\n return {\n index: index + 1,\n expenseType: data.expense_type || \"\",\n vendor: data.vendor_name || \"\",\n invoiceNumber: data.invoice_number || \"\",\n receiptNumber: data.receipt_number || \"\",\n issueDate: data.issue_date || \"\",\n totalAmount: data.total_amount || 0,\n currency: data.currency || \"\",\n taxAmount: data.tax_amount || 0,\n paymentMethod: data.payment_method || \"\",\n location: data.location || \"\",\n notes: data.notes || \"\",\n items: data.itemized_descriptions || []\n };\n});\n\nreturn [\n {\n json: {\n employee,\n expenses\n }\n }\n];" }, "position": [ 3376, 640 ] }, { "id": "8e2b4203-b162-4a1b-97ae-4d99c40cb4ee", "name": "Upload file", "type": "integration", "params": { "name": "=invoice-{{ $now.toFormat(\"yyyyLLdd-HHmmss\") }}-{{$binary.data.fileName}}", "driveId": { "__rl": true, "mode": "list", "value": "My Drive" }, "options": {}, "service": "googleDrive", "folderId": { "__rl": true, "mode": "list", "value": "1IPcko8bzogO3W4mxhrW2Q017QA0Lc5MI", "cachedResultUrl": "https://drive.google.com/drive/folders/1IPcko8bzogO3W4mxhrW2Q017QA0Lc5MI", "cachedResultName": "SmartSales" }, "operation": "read" }, "position": [ 2448, 256 ] }, { "id": "7e56bf01-4ba6-4beb-919b-786ce0ad2011", "name": "Extract from File", "type": "passthrough", "params": { "mode": "noOp", "options": {}, "operation": "pdf" }, "position": [ 2448, 640 ] }, { "id": "a384703f-fd64-4743-ad74-283d38d2ab1a", "name": "Append row in sheet", "type": "integration", "params": { "columns": { "value": {}, "schema": [ { "id": "EmployeeName", "type": "string", "display": true, "removed": false, "required": false, "displayName": "EmployeeName", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Department", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Department", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "TripPurpose", "type": "string", "display": true, "removed": false, "required": false, "displayName": "TripPurpose", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "FromDate", "type": "string", "display": true, "removed": false, "required": false, "displayName": "FromDate", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "ToDate", "type": "string", "display": true, "removed": false, "required": false, "displayName": "ToDate", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "FileName", "type": "string", "display": true, "removed": false, "required": false, "displayName": "FileName", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "DownloadURL", "type": "string", "display": true, "removed": false, "required": false, "displayName": "DownloadURL", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Size", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Size", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "SubmittedAt", "type": "string", "display": true, "removed": false, "required": false, "displayName": "SubmittedAt", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "autoMapInputData", "matchingColumns": [], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "append", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1qk6OebcuZkIRorf1k235ew88oZ-UlUJSyiHFqZYDbaU/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "1qk6OebcuZkIRorf1k235ew88oZ-UlUJSyiHFqZYDbaU", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1qk6OebcuZkIRorf1k235ew88oZ-UlUJSyiHFqZYDbaU/edit?usp=drivesdk", "cachedResultName": "Invoices Tracking" } }, "position": [ 2928, 256 ] }, { "id": "67200fe9-b12a-4c5a-998d-1b1791771f2b", "name": "Transform invoice record", "type": "code", "params": { "mode": "runOnceForEachItem", "jsCode": "const claimForm = $('On form submission').first().json;\nreturn {\n EmployeeName: claimForm[\"Employee Name\"],\n Department: claimForm[\"Department\"],\n TripPurpose: claimForm[\"Trip Purpose\"],\n FromDate: claimForm[\"From Date\"],\n ToDate: claimForm[\"To Date\"],\n FileName: $json.name,\n DownloadURL: $json.webContentLink,\n Size: $json.size,\n SubmittedAt: claimForm[\"submittedAt\"]\n }" }, "position": [ 2688, 256 ] }, { "id": "87b360c0-219a-451e-8035-7de9a3d1d1c5", "name": "Structured Output Parser", "type": "passthrough", "params": { "mode": "noOp", "jsonSchemaExample": "{\n \"expense_type\": \"Hotel\",\n \"vendor_name\": \"Grand Central Hotel\",\n \"invoice_number\": \"INV-20250802-001\",\n \"receipt_number\": \"RCPT-56789\",\n \"issue_date\": \"2025-08-02\",\n \"total_amount\": 150.00,\n \"tax_amount\": 15.00,\n \"currency\": \"USD\",\n \"payment_method\": \"Credit Card\",\n \"location\": \"Hanoi, Vietnam\",\n \"trip_purpose\": \"Client Meeting\",\n \"trip_dates\": {\n \"from\": \"2025-08-02\",\n \"to\": \"2025-08-03\"\n },\n \"itemized_descriptions\": [\n {\n \"category\": \"Accommodation\",\n \"description\": \"1 night stay - Deluxe Room\",\n \"quantity\": 1,\n \"unit_price\": 120.00,\n \"line_total\": 120.00\n },\n {\n \"category\": \"Service Charge\",\n \"description\": \"Hotel service fee\",\n \"quantity\": 1,\n \"unit_price\": 10.00,\n \"line_total\": 10.00\n },\n {\n \"category\": \"Tax\",\n \"description\": \"VAT 10%\",\n \"quantity\": 1,\n \"unit_price\": 15.00,\n \"line_total\": 15.00\n }\n ],\n \"notes\": \"\"\n}" }, "position": [ 2928, 816 ] }, { "id": "1f1e51c8-918b-4284-b5b2-72cfceb4fb46", "name": "Create HTML Email Template", "type": "code", "params": { "jsCode": "const data = $input.first().json;\n\nconst { employee, expenses } = data;\n\n// Helper to generate expense summary rows\nconst expenseSummaryRows = expenses.map(expense => `\n \n ${expense.index}\n ${expense.expenseType}\n ${expense.vendor}\n ${expense.issueDate}\n ${expense.totalAmount}\n ${expense.currency}\n ${expense.taxAmount}\n ${expense.paymentMethod}\n \n`).join(\"\");\n\n// Helper to generate detailed tables for each expense\nconst expenseDetailSections = expenses.map(expense => {\n const itemRows = (expense.items || []).map(item => `\n \n ${item.category}\n ${item.description}\n ${item.quantity}\n ${item.unit_price}\n ${item.line_total}\n \n `).join(\"\");\n\n return `\n

Details for Expense #${expense.index} - ${expense.expenseType}

Vendor: ${expense.vendor}

Invoice No.: ${expense.invoiceNumber}

Receipt No.: ${expense.receiptNumber}

Location: ${expense.location}

Notes: ${expense.notes}

\n \n \n \n \n \n \n \n \n \n \n ${itemRows}\n

Category	Description	Qty	Unit Price	Line Total

\n `;\n}).join(\"\");\n\nconst html = `\n\n\n\n \n \n\n\n\n

Expense Claim Request

\n\n

Employee Name: ${employee.name}

Department: ${employee.department}

Trip Purpose: ${employee.tripPurpose}

Trip Dates: ${employee.fromDate} to ${employee.toDate}

\n\n

Expense Summary

\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ${expenseSummaryRows}\n \n

#	Type	Vendor	Issue Date	Total	Currency	Tax	Payment

\n\n ${expenseDetailSections}\n\n

Thank you,
${employee.name}

\n\n\n\n`;\n\nreturn [{ json: { html } }];" }, "position": [ 3664, 640 ] }, { "id": "8869ddc8-9058-4fd0-a9f6-fd8cdf4ce152", "name": "Handle multiple files", "type": "code", "params": { "jsCode": "const data = $input.item.json;\nconst binaryData = $input.item.binary;\n\nlet output = [];\n\nObject.keys(binaryData)\n .filter(label => label.startsWith(\"Receipts___Invoices_\"))\n .forEach(label => {\n output.push({\n json: data,\n binary: { data: binaryData[label] }\n });\n });\n\nreturn output;" }, "position": [ 2080, 496 ] }, { "id": "55eb7432-ce93-470a-89f3-b97eba58f0bb", "name": "GPT", "type": "llm", "params": { "model": "gpt-4.1-mini", "options": {}, "provider": "openai" }, "position": [ 2704, 816 ] }, { "id": "b84f4ee1-1b5c-462a-aa28-a3728f0278b1", "name": "Send a message", "type": "integration", "params": { "message": "={{ $json.html }}", "options": {}, "service": "gmail", "subject": "=Expense Claim Request - {{ $('Transform Output').item.json.employee.name }} – {{ $('Transform Output').item.json.employee.department }} - {{ $('Transform Output').item.json.employee.tripPurpose }}", "operation": "read" }, "position": [ 4160, 656 ] }, { "id": "c4147239-207f-414e-b185-61137351bde9", "name": "Document Extractor", "type": "llm", "params": { "text": "=Extract all relevant information from thisreceipt or invoice below:\n---\n{{ $json.text }}\n---\nI need the output in a structured format suitable for generating a business expense claim report. Include fields like vendor name, invoice or receipt number, date, total amount, tax, payment method, currency, and item descriptions.", "prompt": "=Extract all relevant information from thisreceipt or invoice below:\n---\n{{ $json.text }}\n---\nI need the output in a structured format suitable for generating a business expense claim report. Include fields like vendor name, invoice or receipt number, date, total amount, tax, payment method, currency, and item descriptions.", "options": { "systemMessage": "You are an intelligent document extraction assistant trained to accurately parse receipts and invoices from uploaded PDF files.\n\nYour task is to extract all relevant financial and business details typically required for expense reporting, including but not limited to:\n\nVendor Name\n\nInvoice Number / Receipt Number\n\nDate of Issue\n\nTotal Amount\n\nTax Amount\n\nCurrency\n\nPayment Method\n\nItemized Descriptions (each with name, quantity, price, and tax if available)\n\nInstructions:\n\nOutput the extracted data in a clean, structured JSON format.\n\nUse consistent and standardized field names (e.g., vendor_name, invoice_number, date, total_amount, currency, etc.).\n\nIf any field is missing or unreadable, include it with a null or empty string value — do not omit it.\n\nExclude all decorative, explanatory, or non-financial text.\n\nFocus on accuracy, completeness, and format consistency." }, "provider": "openai", "promptType": "define", "approximated": true, "systemMessage": "You are an intelligent document extraction assistant trained to accurately parse receipts and invoices from uploaded PDF files.\n\nYour task is to extract all relevant financial and business details typically required for expense reporting, including but not limited to:\n\nVendor Name\n\nInvoice Number / Receipt Number\n\nDate of Issue\n\nTotal Amount\n\nTax Amount\n\nCurrency\n\nPayment Method\n\nItemized Descriptions (each with name, quantity, price, and tax if available)\n\nInstructions:\n\nOutput the extracted data in a clean, structured JSON format.\n\nUse consistent and standardized field names (e.g., vendor_name, invoice_number, date, total_amount, currency, etc.).\n\nIf any field is missing or unreadable, include it with a null or empty string value — do not omit it.\n\nExclude all decorative, explanatory, or non-financial text.\n\nFocus on accuracy, completeness, and format consistency.", "hasOutputParser": true }, "position": [ 2720, 640 ] }, { "id": "2f1a67be-2e59-4c6c-b3a5-1e0604c0b45a", "name": "Jotform Trigger", "type": "integration", "params": { "form": "252815424602048", "service": "jotformtrigger", "operation": "run", "__originalType": "n8n-nodes-base.jotFormTrigger" }, "position": [ 1760, 496 ] } ] }

Automate employee trip expense processing with Jotform, OpenAI and Google Sheets