{ "edges": [ { "to": "3bfcca0c-c5fa-4268-ae2e-9fe17fb83ecc", "from": "a819dc84-625a-46c0-9a16-48df8318af59", "toPort": "0", "fromPort": "0" }, { "to": "2e876e9f-5349-4d06-87f5-b92174bf95be", "from": "53719b09-efca-451c-86bc-be8e3c83af51", "toPort": "0", "fromPort": "0" }, { "to": "a819dc84-625a-46c0-9a16-48df8318af59", "from": "9d2bcf80-3af7-4c4b-8b92-9e9e9baa4cca", "toPort": "0", "fromPort": "ai_languageModel" }, { "to": "40768100-aea3-4fa5-85a3-83d3ca931676", "from": "2e876e9f-5349-4d06-87f5-b92174bf95be", "toPort": "0", "fromPort": "0" }, { "to": "53719b09-efca-451c-86bc-be8e3c83af51", "from": "7c549ca7-a008-4493-9de7-f657536064c8", "toPort": "0", "fromPort": "0" }, { "to": "3d704d7d-2944-402a-ae32-317a012d4226", "from": "3bfcca0c-c5fa-4268-ae2e-9fe17fb83ecc", "toPort": "0", "fromPort": "0" }, { "to": "df1e00e6-09f6-466c-98a7-e2e610ad631c", "from": "3bfcca0c-c5fa-4268-ae2e-9fe17fb83ecc", "toPort": "0", "fromPort": "0" }, { "to": "211298d1-ac7d-4346-99fd-b7623c45df6d", "from": "3bfcca0c-c5fa-4268-ae2e-9fe17fb83ecc", "toPort": "0", "fromPort": "0" }, { "to": "462a857b-a73a-45b7-8c4b-cae68f3fdc47", "from": "df1e00e6-09f6-466c-98a7-e2e610ad631c", "toPort": "0", "fromPort": "0" }, { "to": "a819dc84-625a-46c0-9a16-48df8318af59", "from": "40768100-aea3-4fa5-85a3-83d3ca931676", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "40768100-aea3-4fa5-85a3-83d3ca931676", "name": "04 | Code Split PDF Attachments into Items", "type": "code", "params": { "jsCode": "const results = [];\n\nfor (const item of $input.all()) {\n // Grab the extracted PDF text\n const pdfText = item.json.text || item.json.content || '';\n\n // Grab Drive file metadata carried from the trigger\n const driveData = item.json;\n\n results.push({\n json: {\n emailId: driveData.id || driveData.name || 'drive_' + Date.now(),\n from: driveData.owners?.[0]?.emailAddress || 'drive-upload',\n subject: driveData.name || 'Invoice PDF',\n date: driveData.createdTime || new Date().toISOString(),\n filename: driveData.name || 'invoice.pdf',\n pdfText: pdfText\n }\n });\n}\n\nreturn results;" }, "position": [ 1136, 448 ] }, { "id": "3bfcca0c-c5fa-4268-ae2e-9fe17fb83ecc", "name": "07 | Code Parse and Enrich Invoice Data", "type": "code", "params": { "jsCode": "const raw = $json.message?.content || $json.text || '{}';\nlet inv;\ntry {\n inv = JSON.parse(raw);\n} catch(e) {\n inv = {\n invoice_number: null,\n vendor_name: 'Unknown',\n invoice_date: null,\n due_date: null,\n amount_due: null,\n currency: null,\n status: 'unclear',\n line_items_summary: 'Parse error'\n };\n}\n\nconst prev = $('04 | Code Split PDF Attachments into Items').item.json;\nconst today = new Date();\nconst dueDate = inv.due_date ? new Date(inv.due_date) : null;\nconst isOverdue = dueDate && inv.status === 'unpaid' && dueDate < today;\n\nreturn [{ json: {\n emailId: prev.emailId,\n filename: prev.filename,\n from: prev.from,\n subject: prev.subject,\n email_date: prev.date,\n invoice_number: inv.invoice_number,\n vendor_name: inv.vendor_name,\n invoice_date: inv.invoice_date,\n due_date: inv.due_date,\n amount_due: inv.amount_due,\n currency: inv.currency || 'USD',\n status: inv.status,\n is_overdue: isOverdue,\n line_items_summary: inv.line_items_summary,\n processed_at: new Date().toISOString()\n} }];" }, "position": [ 1760, 448 ] }, { "id": "3d704d7d-2944-402a-ae32-317a012d4226", "name": "08 | Google Sheets Append to Invoice Log", "type": "integration", "params": { "columns": { "value": { "Date": "={{ $json.emailId }}", "Notes": "={{ $json.processed_at }}", "Keyword": "={{ $json.filename }}", "Post Type": "={{ $json.subject }}", "Trend Topic": "={{ $json.filename }}", "Generated Content": "={{ $json.email_date }}" }, "schema": [ { "id": "Date", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Date", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Keyword", "type": "string", "display": true, "required": false, "displayName": "Keyword", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Trend Topic", "type": "string", "display": true, "required": false, "displayName": "Trend Topic", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Post Type", "type": "string", "display": true, "required": false, "displayName": "Post Type", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Generated Content", "type": "string", "display": true, "required": false, "displayName": "Generated Content", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Status", "type": "string", "display": true, "removed": true, "required": false, "displayName": "Status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Notes", "type": "string", "display": true, "required": false, "displayName": "Notes", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [ "Date" ], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "appendOrUpdate", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1zNoiO_ixXR6DaQW2R0yjWURHy_Cj22qNmWQM6cs6Tt4/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "1zNoiO_ixXR6DaQW2R0yjWURHy_Cj22qNmWQM6cs6Tt4", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1zNoiO_ixXR6DaQW2R0yjWURHy_Cj22qNmWQM6cs6Tt4/edit?usp=drivesdk", "cachedResultName": "Content Log" } }, "position": [ 2128, 416 ] }, { "id": "211298d1-ac7d-4346-99fd-b7623c45df6d", "name": "10 | Slack Alert Finance on Overdue Invoices", "type": "integration", "params": { "text": "=:warning: *Overdue Invoice Alert*\n\n*Vendor:* {{ $json.vendor_name }}\n*Invoice #:* {{ $json.invoice_number }}\n*Amount Due:* {{ $json.amount_due }} {{ $json.currency }}\n*Due Date:* {{ $json.due_date }}\n*Status:* {{ $json.status.toUpperCase() }}\n\n*Line Items:* {{ $json.line_items_summary }}\n\nPlease review and action immediately.", "select": "channel", "service": "slack", "channelId": { "__rl": true, "mode": "list", "value": "C0ASJMR4MMJ", "cachedResultName": "sales-team" }, "operation": "read", "otherOptions": {}, "authentication": "oAuth2" }, "position": [ 2128, 640 ] }, { "id": "df1e00e6-09f6-466c-98a7-e2e610ad631c", "name": "11 | Code Generate Weekly Summary Stats", "type": "code", "params": { "jsCode": "const allItems = $input.all();\nconst total = allItems.length;\nconst paid = allItems.filter(i => i.json.status === 'paid').length;\nconst unpaid = allItems.filter(i => i.json.status === 'unpaid').length;\nconst overdue = allItems.filter(i => i.json.is_overdue).length;\nconst totalAmount = allItems.reduce((sum, i) => sum + (i.json.amount_due || 0), 0);\n\nreturn [{ json: {\n report_date: new Date().toISOString().split('T')[0],\n total_invoices: total,\n paid_count: paid,\n unpaid_count: unpaid,\n overdue_count: overdue,\n total_amount: totalAmount.toFixed(2),\n summary_text: `Weekly Invoice Report: ${total} invoices processed. Paid: ${paid}, Unpaid: ${unpaid}, Overdue: ${overdue}. Total value: $${totalAmount.toFixed(2)}`\n} }];" }, "position": [ 2144, 224 ] }, { "id": "462a857b-a73a-45b7-8c4b-cae68f3fdc47", "name": "12 | Gmail Email Weekly Report to Finance", "type": "integration", "params": { "sendTo": "user@example.com", "message": "=Hi Finance Team,\n\nHere is the weekly invoice reconciliation summary:\n\nTotal Invoices Processed: {{ $json.total_invoices }}\nPaid: {{ $json.paid_count }}\nUnpaid: {{ $json.unpaid_count }}\nOverdue: {{ $json.overdue_count }}\nTotal Value: ${{ $json.total_amount }}\n\nAll invoice data has been logged to the Invoice Tracker Google Sheet.\nOverdue invoices have been flagged in the #finance Slack channel.\n\nBest regards,\nAutomated Finance Bot", "options": {}, "service": "gmail", "subject": "=Weekly Invoice Reconciliation Report {{ $json.report_date }}", "operation": "read" }, "position": [ 2416, 224 ] }, { "id": "53719b09-efca-451c-86bc-be8e3c83af51", "name": "Download file1", "type": "integration", "params": { "fileId": { "__rl": true, "mode": "id", "value": "={{ $json.id }}" }, "options": {}, "service": "googleDrive", "operation": "download" }, "position": [ 544, 448 ] }, { "id": "2e876e9f-5349-4d06-87f5-b92174bf95be", "name": "Extract from File1", "type": "passthrough", "params": { "mode": "noOp", "options": {}, "operation": "pdf" }, "position": [ 720, 448 ] }, { "id": "a819dc84-625a-46c0-9a16-48df8318af59", "name": "AI Agent", "type": "llm", "params": { "text": "=Filename: {{ $json.filename }}\nUploaded by: {{ $json.from }}\nFile date: {{ $json.date }}\n\n--- PDF CONTENT START ---\n{{ $json.pdfText }}\n--- PDF CONTENT END ---\n\nExtract all invoice data from the content above and return it as JSON.", "prompt": "=Filename: {{ $json.filename }}\nUploaded by: {{ $json.from }}\nFile date: {{ $json.date }}\n\n--- PDF CONTENT START ---\n{{ $json.pdfText }}\n--- PDF CONTENT END ---\n\nExtract all invoice data from the content above and return it as JSON.", "options": { "systemMessage": "You are a precise invoice data extraction engine. You will be given the full text content of a PDF invoice along with basic file metadata. Your only job is to extract structured invoice data and return it as valid JSON.\n\nRules:\n- Return ONLY raw JSON. No markdown, no code blocks, no explanation, no preamble.\n- If a field cannot be found or is ambiguous, return null for that field.\n- For status: return \"paid\" if there is clear evidence of payment, \"unpaid\" if an amount is owed, \"unclear\" if you cannot determine.\n- For currency: detect from symbols ($, €, £, ৳) or written codes (USD, EUR, GBP, BDT). Default to null if not found.\n- For dates: always convert to YYYY-MM-DD format regardless of how they appear in the document.\n- For amount_due: return a plain number only, no currency symbols, no commas. Example: 1500.00\n- For line_items_summary: write a single short sentence describing what was billed. Example: \"3 months of cloud hosting and 1 setup fee.\"\n\nReturn this exact structure:\n{\n \"invoice_number\": \"\",\n \"vendor_name\": \"\",\n \"invoice_date\": \"\",\n \"due_date\": \"\",\n \"amount_due\": ,\n \"currency\": \"\",\n \"status\": \"paid | unpaid | unclear\",\n \"line_items_summary\": \"\"\n}" }, "provider": "openai", "promptType": "define", "approximated": true, "systemMessage": "You are a precise invoice data extraction engine. You will be given the full text content of a PDF invoice along with basic file metadata. Your only job is to extract structured invoice data and return it as valid JSON.\n\nRules:\n- Return ONLY raw JSON. No markdown, no code blocks, no explanation, no preamble.\n- If a field cannot be found or is ambiguous, return null for that field.\n- For status: return \"paid\" if there is clear evidence of payment, \"unpaid\" if an amount is owed, \"unclear\" if you cannot determine.\n- For currency: detect from symbols ($, €, £, ৳) or written codes (USD, EUR, GBP, BDT). Default to null if not found.\n- For dates: always convert to YYYY-MM-DD format regardless of how they appear in the document.\n- For amount_due: return a plain number only, no currency symbols, no commas. Example: 1500.00\n- For line_items_summary: write a single short sentence describing what was billed. Example: \"3 months of cloud hosting and 1 setup fee.\"\n\nReturn this exact structure:\n{\n \"invoice_number\": \"\",\n \"vendor_name\": \"\",\n \"invoice_date\": \"\",\n \"due_date\": \"\",\n \"amount_due\": ,\n \"currency\": \"\",\n \"status\": \"paid | unpaid | unclear\",\n \"line_items_summary\": \"\"\n}" }, "position": [ 1344, 448 ] }, { "id": "9d2bcf80-3af7-4c4b-8b92-9e9e9baa4cca", "name": "OpenAI Chat Model", "type": "llm", "params": { "model": "gpt-5-mini", "options": {}, "provider": "openai", "builtInTools": {} }, "position": [ 1376, 592 ] }, { "id": "7c549ca7-a008-4493-9de7-f657536064c8", "name": "Google Drive Trigger1", "type": "trigger", "params": { "kind": "n8n-nodes-base.googleDriveTrigger", "event": "fileCreated", "options": {}, "pollTimes": { "item": [ { "mode": "everyMinute" } ] }, "triggerOn": "specificFolder", "folderToWatch": { "__rl": true, "mode": "list", "value": "179lkBMnCq6_PqhA9dWzuS6zcXo4Qvijf", "cachedResultUrl": "https://drive.google.com/drive/folders/179lkBMnCq6_PqhA9dWzuS6zcXo4Qvijf", "cachedResultName": "Inv" } }, "position": [ 352, 448 ] } ] }

Invoice Reconciliation Automation