银行对账AI
高级
这是一个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": "银行对账 AI",
"tags": [],
"nodes": [
{
"id": "87eb23a7-ed90-458f-9cd3-c98315ac8070",
"name": "当点击\"执行工作流\"时",
"type": "n8n-nodes-base.manualTrigger",
"position": [
864,
-544
],
"parameters": {},
"typeVersion": 1
},
{
"id": "00eaa2bc-f104-4666-b071-f0de28ec1049",
"name": "发票数据",
"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": "银行对账单",
"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 聊天模型",
"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": "处理发票与银行对账单数据",
"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": "合并",
"type": "n8n-nodes-base.merge",
"position": [
1552,
-544
],
"parameters": {},
"typeVersion": 3.2
},
{
"id": "149bd1b7-90e6-4309-8f52-3c73d0711199",
"name": "合并并标记合并数据",
"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": "分支",
"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": "已匹配",
"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": "可能匹配",
"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": "条件判断",
"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": "未匹配发票",
"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": "未匹配银行交易",
"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": "摘要",
"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": "清理和格式化数据",
"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": "添加流标签过滤器",
"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": "便签",
"type": "n8n-nodes-base.stickyNote",
"position": [
752,
-672
],
"parameters": {
"width": 304,
"height": 320,
"content": "## 手动触发器"
},
"typeVersion": 1
},
{
"id": "6ae4a0ab-7afe-4a05-a275-aeeec1e3ce0a",
"name": "便签1",
"type": "n8n-nodes-base.stickyNote",
"position": [
1168,
-848
],
"parameters": {
"height": 704,
"content": "## 获取发票和银行对账单"
},
"typeVersion": 1
},
{
"id": "2a18094a-8941-4481-9970-c66dfcbcb8d1",
"name": "便签2",
"type": "n8n-nodes-base.stickyNote",
"position": [
1488,
-688
],
"parameters": {
"height": 352,
"content": "## 合并数据"
},
"typeVersion": 1
},
{
"id": "2a645f55-b778-44a0-b4ca-6c925c0300ea",
"name": "便签3",
"type": "n8n-nodes-base.stickyNote",
"position": [
1824,
-720
],
"parameters": {
"width": 272,
"height": 448,
"content": "## 为 AI 格式化有效载荷"
},
"typeVersion": 1
},
{
"id": "c96fa17a-cd05-414b-96dd-ccdb5da631fa",
"name": "便签4",
"type": "n8n-nodes-base.stickyNote",
"position": [
2192,
-816
],
"parameters": {
"width": 512,
"height": 672,
"content": "## AI 对账"
},
"typeVersion": 1
},
{
"id": "f706ab77-4ffd-4d85-9d54-c65025753014",
"name": "便利贴5",
"type": "n8n-nodes-base.stickyNote",
"position": [
2816,
-720
],
"parameters": {
"width": 464,
"height": 400,
"content": "## 解析 AI 输出"
},
"typeVersion": 1
},
{
"id": "b7029e44-31d1-46a3-852b-a67956ff85ff",
"name": "便签 6",
"type": "n8n-nodes-base.stickyNote",
"position": [
3424,
-912
],
"parameters": {
"width": 896,
"height": 832,
"content": "## 更新表格"
},
"typeVersion": 1
},
{
"id": "76021357-252b-4df9-b192-01563a251745",
"name": "便签 7",
"type": "n8n-nodes-base.stickyNote",
"position": [
-256,
-960
],
"parameters": {
"color": 3,
"width": 768,
"height": 864,
"content": "🧾 先决条件"
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "975828e6-ce78-4c58-9a12-f8c53430eb85",
"connections": {
"If": {
"main": [
[
{
"node": "Unmatched_Invoice",
"type": "main",
"index": 0
}
],
[
{
"node": "Unmatched-Bank Transaction",
"type": "main",
"index": 0
}
]
]
},
"Merge": {
"main": [
[
{
"node": "Combine and Label Merged Data",
"type": "main",
"index": 0
}
]
]
},
"Switch": {
"main": [
[
{
"node": "Matched",
"type": "main",
"index": 0
}
],
[
{
"node": "If",
"type": "main",
"index": 0
}
],
[
{
"node": "Summary",
"type": "main",
"index": 0
}
],
[
{
"node": "Possible Matches",
"type": "main",
"index": 0
}
]
]
},
"Invoice Data": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Bank Statements": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
},
"OpenAI Chat Model": {
"ai_languageModel": [
[
{
"node": "Process the Invoice Vs Bank Statement Data1",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Clean and Format Data": {
"main": [
[
{
"node": "Add Stream Label Filter",
"type": "main",
"index": 0
}
]
]
},
"Add Stream Label Filter": {
"main": [
[
{
"node": "Switch",
"type": "main",
"index": 0
}
]
]
},
"Combine and Label Merged Data": {
"main": [
[
{
"node": "Process the Invoice Vs Bank Statement Data1",
"type": "main",
"index": 0
}
]
]
},
"When clicking ‘Execute workflow’": {
"main": [
[
{
"node": "Invoice Data",
"type": "main",
"index": 0
},
{
"node": "Bank Statements",
"type": "main",
"index": 0
}
]
]
},
"Process the Invoice Vs Bank Statement Data1": {
"main": [
[
{
"node": "Clean and Format Data",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 发票处理, AI 摘要总结
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
Facebook页面评论管理机器人：回复、删除、封禁和通知
AI驱动的Facebook评论管理：自动回复、删除、封禁和通知
If
Set
Code
+18
59 节点SpaGreen Creative
社交媒体
潜在客户开发与邮件工作流
使用Google Maps、SendGrid和AI自动化B2B潜在客户开发与邮件营销
If
Set
Code
+21
141 节点Ezema Kingsley Chibuzo
潜在客户开发
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并、筛选和汇总
初学者数据分析：使用 GPT-4o 在 Google Sheets 中合并、筛选和汇总
If
Set
Code
+9
21 节点Robert Breen
文档提取
（Duc）深度研究市场模板
集成PerplexityAI研究和OpenAI内容的多层级WordPress博客生成器
If
Set
Xml
+28
132 节点Daniel Ng
人工智能
[astro/nextjs] 为文章/帖子分配类别/标签
使用OpenAI GPT-4、GitHub和Google Sheets为Astro/Next.js博客文章自动分类
Code
Form
Merge
+11
29 节点Piotr Sikora
内容创作
AI 客户支持分流与摘要系统
使用GPT-4o、Slack和CRM集成自动处理客户支持
If
Set
Code
+10
32 节点NodeAlchemy
工单管理
