은행 계좌 조회 AI
고급
이것은Invoice Processing, AI Summarization분야의자동화 워크플로우로, 24개의 노드를 포함합니다.주로 If, Code, Merge, Switch, GoogleSheets 등의 노드를 사용하며. GPT-4.1-mini 및 Google 시트를 사용한 은행 명세서 및 송장 대조 자동화
사전 요구사항
- •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 채팅 모델",
"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 지도, 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
티켓 관리
