使用GPT-4.1-mini和Google
高级
这是一个Invoice Processing, AI Summarization领域的自动化工作流，包含 24 个节点。主要使用 If, Code, Merge, Switch, GoogleSheets 等节点。 使用GPT-4.1-mini和Google Sheets自动化银行对账单和发票对账
前置要求
- •Google Sheets API 凭证
- •OpenAI API Key
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"id": "wpg39fpK9dN3BaXH",
"meta": {
"instanceId": "27ffea455fd6438ca9c70f7a14250d75f584b8ec341c04d334cc7237b9299652",
"templateCredsSetupCompleted": true
},
"name": "Bank Reconciliation AI",
"tags": [],
"nodes": [
{
"id": "87eb23a7-ed90-458f-9cd3-c98315ac8070",
"name": "When clicking ‘Execute workflow’",
"type": "n8n-nodes-base.manualTrigger",
"position": [
864,
-544
],
"parameters": {},
"typeVersion": 1
},
{
"id": "00eaa2bc-f104-4666-b071-f0de28ec1049",
"name": "Invoice Data",
"type": "n8n-nodes-base.googleSheets",
"position": [
1232,
-656
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 595638947,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=595638947",
"cachedResultName": "Invoices"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
"cachedResultName": "Reconciliation POC"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "8GgfaHX1qOHcB2ej",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "ae6b1aee-33a9-48ea-b11e-6fd267b8e544",
"name": "Bank Statements",
"type": "n8n-nodes-base.googleSheets",
"position": [
1232,
-432
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 2045470915,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=2045470915",
"cachedResultName": "Bank Statement"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
"cachedResultName": "Reconciliation POC"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "8GgfaHX1qOHcB2ej",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "1ddece06-81b0-4b81-a78e-c91ddd21b6bf",
"name": "OpenAI Chat Model",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
"position": [
2240,
-304
],
"parameters": {
"model": {
"__rl": true,
"mode": "list",
"value": "gpt-4.1-mini"
},
"options": {
"temperature": 0
}
},
"credentials": {
"openAiApi": {
"id": "CncvDJ9wuNXul1oG",
"name": "OpenAi account"
}
},
"typeVersion": 1.2
},
{
"id": "1896c41e-96e9-4a00-be6e-ca1325fd137a",
"name": "Process the Invoice Vs Bank Statement Data1",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
2320,
-544
],
"parameters": {
"text": "=You are a Reconciliation Assistant.\nYou receive one JSON input object with:\n\n{\n \"invoices\": [ { ... } ],\n \"bank_transactions\": [ { ... } ],\n \"currency\": \"USD\",\n \"source\": \"Bank XYZ\"\n}\n\n🧩 Matching Rules (apply in order)\n\nHard reference match\n\nMatch if invoice[\"Invoice Number\"] == txn[\"Reference\"] (case-sensitive, trim spaces).\n\nAmount match\n\nMatch if invoice[\"Total (USD)\"] == txn[\"Credit (USD)\"].\n\nTreat empty strings as 0.\n\nDate proximity\n\nInvoice Date within ±7 days (inclusive) of Transaction Date.\n\nName/company heuristic (fallback)\n\nIf no reference match but company/customer name appears in transaction description (case-insensitive substring), and amount equal, and date within ±7 days.\n\nMark heuristic = true.\n\n🚫 Ignore / Exclude\n\nIgnore “Opening Balance” and “Closing Balance” rows.\n\nIgnore expense or debit transactions (Debit (USD) > 0) for invoice matching.\n\n🧠 Matching Priority\n\nIf multiple matches for one invoice:\n\nClosest date wins.\n\nIf tie, exact reference match preferred.\n\nIf still tied, pick lowest txn_row_number.\n\nIf one transaction matches multiple invoices (same amount), list under possible_matches.\n\n🧮 Confidence Scoring\n\nStart at 1.0, subtract:\n\n−0.05 → if only date proximity (no reference)\n\n−0.10 → if heuristic name match used\n\n−0.10 → if date gap > 3 days (but ≤ 7)\n\nClamp between 0 and 1.\n\n✅ Output Format (STRICT JSON ONLY)\n\nThe AI must return only JSON with the following structure and field names:\n\n{\n \"matched\": [\n {\n \"invoice_no\": \"INV-1001\",\n \"invoice_row_number\": 2,\n \"txn_row_number\": 3,\n \"amount\": 2470.0,\n \"reference\": \"INV-1001\",\n \"date_invoice\": \"2025-09-03\",\n \"date_txn\": \"2025-09-03\",\n \"confidence\": 0.99,\n \"heuristic\": false,\n \"notes\": \"reference+amount+date\"\n }\n ],\n \"possible_matches\": [\n {\n \"invoice_no\": \"INV-1007\",\n \"invoice_row_number\": 8,\n \"candidates\": [\n {\n \"txn_row_number\": 13,\n \"amount\": 1995.0,\n \"reference\": \"INV-1007\",\n \"date_txn\": \"2025-09-18\"\n }\n ],\n \"reason\": \"multiple or weak signals\"\n }\n ],\n \"unmatched\": {\n \"invoices\": [\n {\n \"invoice_no\": \"INV-XXXX\",\n \"invoice_row_number\": 12,\n \"amount\": 100.0\n }\n ],\n \"bank_transactions\": [\n {\n \"txn_row_number\": 19,\n \"amount\": 5.25,\n \"reason\": \"interest/expense/no reference\"\n }\n ]\n },\n \"summary\": {\n \"currency\": \"USD\",\n \"totals\": {\n \"invoices_count\": 0,\n \"bank_credits_count\": 0,\n \"matched_count\": 0,\n \"possible_matches_count\": 0,\n \"unmatched_invoices_count\": 0,\n \"unmatched_bank_count\": 0,\n \"matched_amount_sum\": 0.0\n },\n \"parameters\": {\n \"date_window_days\": 7\n }\n }\n}\n\n🧾 Output Rules\n\nAlways include all four sections (matched, possible_matches, unmatched, summary) even if empty.\n\nUse exact key names and field order as shown.\n\nAll numeric fields (amounts, counts, confidence) must be numbers, not strings.\n\nDates should be returned exactly as in the input, without conversion.\n\nDo not include any extra text or explanation — only valid JSON.\n\n\nUser (Data) Use the following JSON as input: {{ JSON.stringify($json.payload) }}\n",
"options": {},
"promptType": "define",
"hasOutputParser": true
},
"typeVersion": 2.2
},
{
"id": "36e5a7c1-aed9-4d9a-92bc-7145d5bcc302",
"name": "Merge",
"type": "n8n-nodes-base.merge",
"position": [
1552,
-544
],
"parameters": {},
"typeVersion": 3.2
},
{
"id": "149bd1b7-90e6-4309-8f52-3c73d0711199",
"name": "Combine and Label Merged Data",
"type": "n8n-nodes-base.code",
"position": [
1904,
-544
],
"parameters": {
"jsCode": "const invoices = $items(\"Invoice Data\").map(i => i.json);\nconst bank = $items(\"Bank Statements\").map(b => b.json);\n\nreturn [{\n json: {\n payload: {\n source: \"reconciliation_v1\",\n currency: \"USD\",\n invoices,\n bank_transactions: bank\n }\n }\n}];\n"
},
"typeVersion": 2
},
{
"id": "1bc4151f-8b9f-402c-a4c1-f30bf6e09bc9",
"name": "Switch",
"type": "n8n-nodes-base.switch",
"position": [
3488,
-576
],
"parameters": {
"rules": {
"values": [
{
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "ffa6f0a0-e329-4306-a693-18f191add964",
"operator": {
"type": "string",
"operation": "equals"
},
"leftValue": "={{ $json.stream }}",
"rightValue": "matched"
}
]
}
},
{
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "a0325682-054e-48b4-80f8-edbb83e34459",
"operator": {
"name": "filter.operator.equals",
"type": "string",
"operation": "equals"
},
"leftValue": "={{ $json.stream }}",
"rightValue": "unmatched"
}
]
}
},
{
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "65942913-ea50-4e77-b881-fd62ea4722e2",
"operator": {
"name": "filter.operator.equals",
"type": "string",
"operation": "equals"
},
"leftValue": "={{ $json.stream }}",
"rightValue": "summary"
}
]
}
},
{
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "ec6f09d2-4bed-4146-8e2a-3c01ae2cf64a",
"operator": {
"name": "filter.operator.equals",
"type": "string",
"operation": "equals"
},
"leftValue": "={{ $json.stream }}",
"rightValue": "possible_matches"
}
]
}
}
]
},
"options": {}
},
"typeVersion": 3.2
},
{
"id": "7c3a7f39-a4dd-4dc3-a43a-1293481668b4",
"name": "Matched",
"type": "n8n-nodes-base.googleSheets",
"position": [
3760,
-800
],
"parameters": {
"columns": {
"value": {
"Notes": "={{ $json.notes }}",
"amount": "={{ $json.amount }}",
"date_txn": "={{ $json.date_txn }}",
"heuristic": "={{ $json.heuristic }}",
"reference": "={{ $json.reference }}",
"confidence": "={{ $json.confidence }}",
"invoice_no": "={{ $json.invoice_no }}",
"date_invoice": "={{ $json.date_invoice }}",
"txn_row_number": "={{ $json.txn_row_number }}",
"invoice_row_number": "={{ $json.invoice_row_number }}"
},
"schema": [
{
"id": "invoice_no",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "invoice_no",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "invoice_row_number",
"type": "string",
"display": true,
"required": false,
"displayName": "invoice_row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "txn_row_number",
"type": "string",
"display": true,
"required": false,
"displayName": "txn_row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "amount",
"type": "string",
"display": true,
"required": false,
"displayName": "amount",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "reference",
"type": "string",
"display": true,
"required": false,
"displayName": "reference",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "date_invoice",
"type": "string",
"display": true,
"required": false,
"displayName": "date_invoice",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "date_txn",
"type": "string",
"display": true,
"required": false,
"displayName": "date_txn",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "confidence",
"type": "string",
"display": true,
"required": false,
"displayName": "confidence",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "heuristic",
"type": "string",
"display": true,
"required": false,
"displayName": "heuristic",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Notes",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Notes",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"invoice_no"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1032802624,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=1032802624",
"cachedResultName": "Reconciled Data(Matched)"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
"cachedResultName": "Reconciliation POC"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "8GgfaHX1qOHcB2ej",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "9b83a407-6011-4024-b203-b53e3b0cc3fa",
"name": "Possible Matches",
"type": "n8n-nodes-base.googleSheets",
"position": [
3776,
-256
],
"parameters": {
"columns": {
"value": {
"reason": "={{ $json.reason }}",
"invoice_no": "={{ $json.invoice_no }}",
"candidate_amount": "={{ $json.candidates[0].amount }}",
"candidate_txn_row": "={{ $json.candidates[0].txn_row_number }}",
"candidate_date_txn": "={{ $json.candidates[0].date_txn }}",
"invoice_row_number": "={{ $json.invoice_row_number }}",
"candidate_reference": "={{ $json.candidates[0].reference }}"
},
"schema": [
{
"id": "invoice_no",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "invoice_no",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "invoice_row_number",
"type": "string",
"display": true,
"required": false,
"displayName": "invoice_row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "reason",
"type": "string",
"display": true,
"required": false,
"displayName": "reason",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "candidate_txn_row",
"type": "string",
"display": true,
"required": false,
"displayName": "candidate_txn_row",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "candidate_amount",
"type": "string",
"display": true,
"required": false,
"displayName": "candidate_amount",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "candidate_reference",
"type": "string",
"display": true,
"required": false,
"displayName": "candidate_reference",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "candidate_date_txn",
"type": "string",
"display": true,
"required": false,
"displayName": "candidate_date_txn",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"invoice_no"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 124117742,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=124117742",
"cachedResultName": "Reconciled Data(Possible Matches)"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
"cachedResultName": "Reconciliation POC"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "8GgfaHX1qOHcB2ej",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "079696bc-1b88-42ac-89f2-535df24add51",
"name": "If",
"type": "n8n-nodes-base.if",
"position": [
3760,
-624
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "bc7cfe0d-1ce8-4c5b-96f2-6813b6abaea2",
"operator": {
"name": "filter.operator.equals",
"type": "string",
"operation": "equals"
},
"leftValue": "={{ $json.subtype }}",
"rightValue": "invoice"
}
]
}
},
"typeVersion": 2.2
},
{
"id": "1bdab5fa-bc29-461c-a07b-10228d27ed83",
"name": "Unmatched_Invoice",
"type": "n8n-nodes-base.googleSheets",
"position": [
4000,
-736
],
"parameters": {
"columns": {
"value": {
"type": "={{ $json.subtype }}",
"amount": "={{ $json.amount }}",
"reason": "=",
"invoice_no": "={{ $json.invoice_no }}",
"txn_row_number": "=",
"invoice_row_number": "={{ $json.invoice_row_number }}"
},
"schema": [
{
"id": "type",
"type": "string",
"display": true,
"required": false,
"displayName": "type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "invoice_no",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "invoice_no",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "invoice_row_number",
"type": "string",
"display": true,
"required": false,
"displayName": "invoice_row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "txn_row_number",
"type": "string",
"display": true,
"required": false,
"displayName": "txn_row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "amount",
"type": "string",
"display": true,
"required": false,
"displayName": "amount",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "reason",
"type": "string",
"display": true,
"required": false,
"displayName": "reason",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"invoice_no"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 481952904,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=481952904",
"cachedResultName": "Reconciled Data(Unmatched)"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
"cachedResultName": "Reconciliation POC"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "8GgfaHX1qOHcB2ej",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "33a28f43-8d0c-480d-bf19-8cbd2436b929",
"name": "Unmatched-Bank Transaction",
"type": "n8n-nodes-base.googleSheets",
"position": [
4016,
-528
],
"parameters": {
"columns": {
"value": {
"type": "={{ $json.subtype }}",
"amount": "={{ $json.amount }}",
"reason": "={{ $json.reason }}",
"invoice_no": "={{ $json.txn_row_number }}",
"txn_row_number": "={{ $json.txn_row_number }}",
"invoice_row_number": "="
},
"schema": [
{
"id": "type",
"type": "string",
"display": true,
"required": false,
"displayName": "type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "invoice_no",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "invoice_no",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "invoice_row_number",
"type": "string",
"display": true,
"required": false,
"displayName": "invoice_row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "txn_row_number",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "txn_row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "amount",
"type": "string",
"display": true,
"required": false,
"displayName": "amount",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "reason",
"type": "string",
"display": true,
"required": false,
"displayName": "reason",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"txn_row_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 481952904,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=481952904",
"cachedResultName": "Reconciled Data(Unmatched)"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
"cachedResultName": "Reconciliation POC"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "8GgfaHX1qOHcB2ej",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "135102bc-cbdf-4a6d-a0a6-0266bda6f006",
"name": "Summary",
"type": "n8n-nodes-base.googleSheets",
"position": [
3776,
-448
],
"parameters": {
"columns": {
"value": {
"stream": "={{ $json.stream }}",
"currency": "={{ $json.currency }}",
"totals.invoice_count": "={{ $json.totals.invoices_count }}",
"totals.matched_count": "={{ $json.totals.matched_count }}",
"totals.bank_credit_count": "={{ $json.totals.bank_credits_count }}",
"totals.matched_amount_sum": "={{ $json.totals.matched_amount_sum }}",
"totals.unmatched_bank_count": "={{ $json.totals.unmatched_bank_count }}",
"parameters.date_windows_date": "={{ $json.parameters.date_window_days }}",
"totals.possible_matches_count": "={{ $json.totals.possible_matches_count }}",
"totals.unmatched_invoice_count": "={{ $json.totals.unmatched_invoices_count }}"
},
"schema": [
{
"id": "stream",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "stream",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "currency",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "currency",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "totals.invoice_count",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "totals.invoice_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "totals.bank_credit_count",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "totals.bank_credit_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "totals.matched_count",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "totals.matched_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "totals.possible_matches_count",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "totals.possible_matches_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "totals.unmatched_bank_count",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "totals.unmatched_bank_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "totals.unmatched_invoice_count",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "totals.unmatched_invoice_count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "totals.matched_amount_sum",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "totals.matched_amount_sum",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "parameters.date_windows_date",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "parameters.date_windows_date",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 657591862,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=657591862",
"cachedResultName": "Reconciled Data(Summary)"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
"cachedResultName": "Reconciliation POC"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "8GgfaHX1qOHcB2ej",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "7605189e-60e8-4f02-8336-e924806a2b7a",
"name": "Clean and Format Data",
"type": "n8n-nodes-base.code",
"position": [
2896,
-544
],
"parameters": {
"jsCode": "const items = $input.all();\n\nfunction parseFencedJson(text) {\n if (typeof text !== 'string') {\n throw new Error('Expected a string in `output`.');\n }\n\n let s = text.trim();\n // Remove leading ```[lang]\\n and trailing ```\n if (s.startsWith('```')) {\n s = s.replace(/^```[a-zA-Z0-9_-]*\\n?/, ''); // drop opening fence (with optional language)\n s = s.replace(/```$/, ''); // drop closing fence\n }\n\n // In case there is any extra wrapper text, extract the JSON block\n const start = s.indexOf('{');\n const end = s.lastIndexOf('}');\n if (start !== -1 && end !== -1 && end > start) {\n s = s.slice(start, end + 1);\n }\n\n return JSON.parse(s);\n}\n\nconst out = [];\n\nfor (const item of items) {\n // Support either item.json.output or item.output\n const raw = (item.json && item.json.output) ?? item.output;\n try {\n const parsed = parseFencedJson(raw);\n out.push({ json: parsed });\n } catch (e) {\n // If parsing fails, return a helpful error payload\n out.push({\n json: {\n error: true,\n message: e.message,\n original: raw,\n },\n });\n }\n}\nreturn out;"
},
"typeVersion": 2
},
{
"id": "8730e70e-6c20-434c-844e-6fc6b6a1a1e8",
"name": "Add Stream Label Filter",
"type": "n8n-nodes-base.code",
"position": [
3104,
-544
],
"parameters": {
"jsCode": "// Input can be [ { ... } ] or { ... }\nlet root = items[0]?.json;\nif (Array.isArray(root)) root = root[0] || {};\n\nconst out = [];\n\n// Helpers for relational schema\nconst invoices = Array.isArray(root.invoices) ? root.invoices : [];\nconst txns = Array.isArray(root.bank_transactions) ? root.bank_transactions : [];\nconst invById = new Map(invoices.map(i => [i.id, i]));\nconst txnById = new Map(txns.map(t => [t.id, t]));\n\n// ---- 1) MATCHED ----\n// Legacy: root.matched\nif (Array.isArray(root.matched)) {\n root.matched.forEach(r => out.push({ json: { stream: 'matched', ...r } }));\n}\n\n// Relational: root.matches\nif (Array.isArray(root.matches)) {\n root.matches.forEach(m => {\n const inv = invById.get(m.invoice_id) || {};\n const tx = txnById.get(m.txn_id) || {};\n out.push({\n json: {\n stream: 'matched',\n // keep original relational fields\n ...m,\n // enrich with row numbers & dates if available\n invoice_row_number: inv.invoice_row_number ?? m.invoice_row_number,\n txn_row_number: tx.txn_row_number ?? m.txn_row_number,\n date_invoice: inv.date_invoice ?? m.date_invoice,\n date_txn: tx.date_txn ?? m.date_txn\n }\n });\n });\n}\n\n// ---- 2) UNMATCHED ----\n// Legacy nested object\nif (root?.unmatched) {\n (root.unmatched.invoices ?? []).forEach(r =>\n out.push({ json: { stream: 'unmatched', subtype: 'invoice', ...r } })\n );\n (root.unmatched.bank_transactions ?? []).forEach(r =>\n out.push({ json: { stream: 'unmatched', subtype: 'bank_transaction', ...r } })\n );\n}\n\n// Relational separate arrays\nif (Array.isArray(root.unmatched_invoices)) {\n root.unmatched_invoices.forEach(r =>\n out.push({ json: { stream: 'unmatched', subtype: 'invoice', ...r } })\n );\n}\nif (Array.isArray(root.unmatched_bank_transactions)) {\n root.unmatched_bank_transactions.forEach(r =>\n out.push({ json: { stream: 'unmatched', subtype: 'bank_transaction', ...r } })\n );\n}\n\n// ---- 3) POSSIBLE MATCHES ----\n// Legacy: already combined\nif (Array.isArray(root.possible_matches)) {\n root.possible_matches.forEach(r =>\n out.push({ json: { stream: 'possible_matches', ...r } })\n );\n}\n\n// Relational: groups + candidates → combine by group\nif (Array.isArray(root.possible_match_groups) || Array.isArray(root.possible_match_candidates)) {\n const groups = root.possible_match_groups ?? [];\n const cands = root.possible_match_candidates ?? [];\n const byGroup = new Map();\n cands.forEach(c => {\n const arr = byGroup.get(c.match_group_id) || [];\n // enrich candidate with txn_row_number/date if available\n const tx = txnById.get(c.txn_id) || {};\n arr.push({\n ...c,\n txn_row_number: tx.txn_row_number ?? c.txn_row_number,\n date_txn: c.date_txn ?? tx.date_txn\n });\n byGroup.set(c.match_group_id, arr);\n });\n\n groups.forEach(g => {\n const inv = invById.get(g.invoice_id) || {};\n out.push({\n json: {\n stream: 'possible_matches',\n ...g,\n // surface invoice info if available\n invoice_no: inv.invoice_no,\n invoice_row_number: inv.invoice_row_number,\n date_invoice: inv.date_invoice,\n candidates: byGroup.get(g.id) || []\n }\n });\n });\n}\n\n// ---- 4) SUMMARY ----\n// Legacy: root.summary\nif (root.summary) {\n out.push({ json: { stream: 'summary', ...root.summary } });\n}\n\n// Relational: same key name (already handled above)\n\n// Done\nreturn out;\n"
},
"typeVersion": 2
},
{
"id": "3feb7656-b1f8-47e7-807b-a0aca26da41e",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
752,
-672
],
"parameters": {
"width": 304,
"height": 320,
"content": "## Manual Trigger \n**Workflow starts manually to initiate the reconciliation process on demand."
},
"typeVersion": 1
},
{
"id": "6ae4a0ab-7afe-4a05-a275-aeeec1e3ce0a",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
1168,
-848
],
"parameters": {
"height": 704,
"content": "## Fetch Invoices & Bank Statements \n**Retrieves invoice data and bank statement data from Google Sheets for comparison."
},
"typeVersion": 1
},
{
"id": "2a18094a-8941-4481-9970-c66dfcbcb8d1",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
1488,
-688
],
"parameters": {
"height": 352,
"content": "## Merge Data\nCombines both datasets into a single structured dataset for processing."
},
"typeVersion": 1
},
{
"id": "2a645f55-b778-44a0-b4ca-6c925c0300ea",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
1824,
-720
],
"parameters": {
"width": 272,
"height": 448,
"content": "## Format Payload for AI\nFunction node prepares and structures the merged data into a clean JSON payload for AI analysis."
},
"typeVersion": 1
},
{
"id": "c96fa17a-cd05-414b-96dd-ccdb5da631fa",
"name": "Sticky Note4",
"type": "n8n-nodes-base.stickyNote",
"position": [
2192,
-816
],
"parameters": {
"width": 512,
"height": 672,
"content": "## AI Reconciliation\nAI Agent analyzes the invoice and bank statement data to identify matches, discrepancies, and reconciled entries."
},
"typeVersion": 1
},
{
"id": "f706ab77-4ffd-4d85-9d54-c65025753014",
"name": "Sticky Note5",
"type": "n8n-nodes-base.stickyNote",
"position": [
2816,
-720
],
"parameters": {
"width": 464,
"height": 400,
"content": "## Parse AI Output\n**Parses the AI response into a structured format suitable for adding back to Google Sheets."
},
"typeVersion": 1
},
{
"id": "b7029e44-31d1-46a3-852b-a67956ff85ff",
"name": "Sticky Note6",
"type": "n8n-nodes-base.stickyNote",
"position": [
3424,
-912
],
"parameters": {
"width": 896,
"height": 832,
"content": "## Update Sheets\nAdds the reconciled data and reconciliation results into the target Google Sheet for recordkeeping."
},
"typeVersion": 1
},
{
"id": "76021357-252b-4df9-b192-01563a251745",
"name": "Sticky Note7",
"type": "n8n-nodes-base.stickyNote",
"position": [
-256,
-960
],
"parameters": {
"color": 3,
"width": 768,
"height": 864,
"content": "🧾 Prerequisites\n\n✅ OpenAI API Credentials\n\nRequired for the AI Reconciliation node to process and match transactions.\n\nAdd your OpenAI API key in n8n → Credentials → OpenAI.\n\n✅ Google Sheets Credentials\nNeeded to read invoice and bank statement data and to write reconciled results.\nAdd credentials in n8n → Credentials → Google Sheets.\n\n✅ Google Sheets Setup\nThe connected spreadsheet must contain the following tabs:\nInvoices – for invoice data\nBank_Statement – for bank transaction data\nReconciled_Data – for storing the AI-processed reconciliation output\n\n✅ Tab Structure & Required Headers\nInvoices Sheet Columns:\nInvoice_ID\nInvoice_Date\nCustomer_Name\nAmount\nStatus\nBank_Statement Sheet Columns:\nTransaction_ID\nTransaction_Date\nDescription\nDebit/Credit\nAmount\nReconciled_Data Sheet Columns:\nInvoice_ID\nTransaction_ID\nMatched_Status\nRemarks\nConfidence_Score\n\n⚙️ n8n Environment Setup\nEnsure all nodes are connected correctly and the workflow has permission to access the required sheets.\nTest each fetch and write operation before running the full workflow.\n\n\nTab Name and headers can be adjusted accordingly. Changes have to be made in the sheets as well accordingly"
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "975828e6-ce78-4c58-9a12-f8c53430eb85",
"connections": {
"079696bc-1b88-42ac-89f2-535df24add51": {
"main": [
[
{
"node": "1bdab5fa-bc29-461c-a07b-10228d27ed83",
"type": "main",
"index": 0
}
],
[
{
"node": "33a28f43-8d0c-480d-bf19-8cbd2436b929",
"type": "main",
"index": 0
}
]
]
},
"36e5a7c1-aed9-4d9a-92bc-7145d5bcc302": {
"main": [
[
{
"node": "149bd1b7-90e6-4309-8f52-3c73d0711199",
"type": "main",
"index": 0
}
]
]
},
"1bc4151f-8b9f-402c-a4c1-f30bf6e09bc9": {
"main": [
[
{
"node": "7c3a7f39-a4dd-4dc3-a43a-1293481668b4",
"type": "main",
"index": 0
}
],
[
{
"node": "079696bc-1b88-42ac-89f2-535df24add51",
"type": "main",
"index": 0
}
],
[
{
"node": "135102bc-cbdf-4a6d-a0a6-0266bda6f006",
"type": "main",
"index": 0
}
],
[
{
"node": "9b83a407-6011-4024-b203-b53e3b0cc3fa",
"type": "main",
"index": 0
}
]
]
},
"00eaa2bc-f104-4666-b071-f0de28ec1049": {
"main": [
[
{
"node": "36e5a7c1-aed9-4d9a-92bc-7145d5bcc302",
"type": "main",
"index": 0
}
]
]
},
"ae6b1aee-33a9-48ea-b11e-6fd267b8e544": {
"main": [
[
{
"node": "36e5a7c1-aed9-4d9a-92bc-7145d5bcc302",
"type": "main",
"index": 1
}
]
]
},
"1ddece06-81b0-4b81-a78e-c91ddd21b6bf": {
"ai_languageModel": [
[
{
"node": "1896c41e-96e9-4a00-be6e-ca1325fd137a",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"7605189e-60e8-4f02-8336-e924806a2b7a": {
"main": [
[
{
"node": "8730e70e-6c20-434c-844e-6fc6b6a1a1e8",
"type": "main",
"index": 0
}
]
]
},
"8730e70e-6c20-434c-844e-6fc6b6a1a1e8": {
"main": [
[
{
"node": "1bc4151f-8b9f-402c-a4c1-f30bf6e09bc9",
"type": "main",
"index": 0
}
]
]
},
"149bd1b7-90e6-4309-8f52-3c73d0711199": {
"main": [
[
{
"node": "1896c41e-96e9-4a00-be6e-ca1325fd137a",
"type": "main",
"index": 0
}
]
]
},
"87eb23a7-ed90-458f-9cd3-c98315ac8070": {
"main": [
[
{
"node": "00eaa2bc-f104-4666-b071-f0de28ec1049",
"type": "main",
"index": 0
},
{
"node": "ae6b1aee-33a9-48ea-b11e-6fd267b8e544",
"type": "main",
"index": 0
}
]
]
},
"1896c41e-96e9-4a00-be6e-ca1325fd137a": {
"main": [
[
{
"node": "7605189e-60e8-4f02-8336-e924806a2b7a",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 发票处理, AI 摘要总结
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
Facebook页面评论管理机器人：回复、删除、封禁和通知
AI驱动的Facebook评论管理：自动回复、删除、封禁和通知
If
Set
Code
+
If
Set
Code
59 节点SpaGreen Creative
社交媒体
潜在客户开发与邮件工作流
使用Google Maps、SendGrid和AI自动化B2B潜在客户开发与邮件营销
If
Set
Code
+
If
Set
Code
141 节点Ezema Kingsley Chibuzo
潜在客户开发
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并、筛选和汇总
If
Set
Code
+
If
Set
Code
21 节点Robert Breen
文档提取
[astro/nextjs] 为文章/帖子分配类别/标签
使用OpenAI GPT-4、GitHub和Google Sheets为Astro/Next.js博客文章自动分类
Code
Form
Merge
+
Code
Form
Merge
29 节点Piotr Sikora
内容创作
AI 客户支持分流与摘要系统
使用GPT-4o、Slack和CRM集成自动处理客户支持
If
Set
Code
+
If
Set
Code
32 节点NodeAlchemy
工单管理
Intercom对话评论
使用Intercom、GPT和Google Sheets自动化支持质量评估
If
Code
Merge
+
If
Code
Merge
22 节点Saleshandy
工单管理
