銀行照合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": "Bank Reconciliation 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 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": "請求書と銀行取引明細データの処理1",
"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": "## Manual Trigger \n**Workflow starts manually to initiate the reconciliation process on demand."
},
"typeVersion": 1
},
{
"id": "6ae4a0ab-7afe-4a05-a275-aeeec1e3ce0a",
"name": "付箋1",
"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": "付箋2",
"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": "付箋3",
"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": "付箋4",
"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": "付箋5",
"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": "付箋6",
"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": "付箋7",
"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シートで結合・フィルタリング・サマリー
初心者のデータ分析：GPT-4o を使用して Google スプレッドシートで結合、フィルタリング、集計
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
チケット管理
