Sync Multi-Bank Balance Data to BigQuery using Plaid
説明
Categories
⚙️ Automation
Nodes Used
n8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.mergen8n-nodes-base.splitOutn8n-nodes-base.splitOutn8n-nodes-base.splitOut
Price無料
Views0
最終更新11/28/2025
workflow.json
{
"meta": {
"instanceId": "689fa22e68cd4198e4ae37f3cc44f498087edd235a867e22515be823bab694c7"
},
"nodes": [
{
"id": "b975d014-c7f8-4f0a-af75-183ca678af46",
"name": "Schedule Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-272,
560
],
"parameters": {
"rule": {
"interval": [
{
"field": "weeks",
"triggerAtDay": [
1
]
}
]
}
},
"typeVersion": 1.2
},
{
"id": "b33dba80-b6d0-4415-b90b-ba16f0e132f9",
"name": "Fetch RBC Balances (Plaid)",
"type": "n8n-nodes-base.httpRequest",
"position": [
-48,
272
],
"parameters": {
"url": "https://production.plaid.com/accounts/balance/get",
"method": "POST",
"options": {},
"sendBody": true,
"bodyParameters": {
"parameters": [
{
"name": "client_id",
"value": "CLIENT_ID"
},
{
"name": "secret",
"value": "SECRET"
},
{
"name": "access_token",
"value": "=AMEX_ACCESS_TOKEN"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "f020756b-f0d5-48d3-83a4-f85d00f530e5",
"name": "Fetch Amex Balances (Plaid)",
"type": "n8n-nodes-base.httpRequest",
"position": [
-48,
464
],
"parameters": {
"url": "https://production.plaid.com/accounts/balance/get",
"method": "POST",
"options": {},
"sendBody": true,
"bodyParameters": {
"parameters": [
{
"name": "client_id",
"value": "CLIENT_ID"
},
{
"name": "secret",
"value": "SECRET"
},
{
"name": "access_token",
"value": "=AMEX_ACCESS_TOKEN"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "f69faba1-668e-4aa9-9f97-2081749ebccb",
"name": "Fetch Wise Balances (Plaid)",
"type": "n8n-nodes-base.httpRequest",
"position": [
-48,
656
],
"parameters": {
"url": "https://production.plaid.com/accounts/balance/get",
"method": "POST",
"options": {},
"sendBody": true,
"bodyParameters": {
"parameters": [
{
"name": "client_id",
"value": "CLIENT_ID"
},
{
"name": "secret",
"value": "SECRET"
},
{
"name": "access_token",
"value": "=WISE_ACCESS_TOKEN"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "77e3e769-a9e2-469f-b7bd-35d02e39e2c0",
"name": "Fetch PayPal Balances (Plaid)",
"type": "n8n-nodes-base.httpRequest",
"position": [
-48,
848
],
"parameters": {
"url": "https://production.plaid.com/accounts/balance/get",
"method": "POST",
"options": {},
"sendBody": true,
"bodyParameters": {
"parameters": [
{
"name": "client_id",
"value": "CLIENT_ID"
},
{
"name": "secret",
"value": "SECRET"
},
{
"name": "access_token",
"value": "=PAYPAL_ACCESS_TOKEN"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "316fd7af-d711-40fe-9891-b64d9b9b1d13",
"name": "Split RBC Accounts",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
272
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "08f9605e-0fb6-441d-8ddc-285b6c63fcc8",
"name": "Split Amex Accounts",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
464
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "2c41813c-4cc7-4d51-8b96-5cf740eda48b",
"name": "Split Wise Accounts",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
656
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "80167b88-42ed-461a-9f4e-53fa6c905105",
"name": "Split PayPal Accounts",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
848
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "9ed9eb63-0de6-4e3b-97ab-a493f8abc432",
"name": "Map RBC Accounts to QBO",
"type": "n8n-nodes-base.code",
"position": [
400,
272
],
"parameters": {
"jsCode": "// Map of mask -> QBO account name\nconst qboMap = {\n \"2245\": \"RBC Business Profits-2245\",\n \"0330\": \"RBC Chequing-0330\",\n \"2278\": \"RBC Taxes-2278\",\n \"7593\": \"RBC MC-7593\",\n \"2252\": \"RBC Employee Payments-2252\",\n \"3456\": \"RBC Visa-3456\",\n \"2260\": \"RBC Youssef-2260\"\n};\n\nfor (const item of $input.all()) {\n const mask = item.json.mask;\n if (qboMap[mask]) {\n item.json.qbo_account_name = qboMap[mask];\n } else {\n // fallback: leave it null or default\n item.json.qbo_account_name = \"RBC - \" + mask;\n }\n}\n\nreturn $input.all();\n"
},
"typeVersion": 2
},
{
"id": "07232bb2-7c4a-49fc-96fc-be84b08d622e",
"name": "Map Amex Accounts to QBO",
"type": "n8n-nodes-base.code",
"position": [
400,
464
],
"parameters": {
"jsCode": "// Map of mask -> QBO account name\nconst qboMap = {\n \"2009\": \"AMEX (CAD)\"\n};\n\nfor (const item of $input.all()) {\n const mask = item.json.mask;\n if (qboMap[mask]) {\n item.json.qbo_account_name = qboMap[mask];\n } else {\n // fallback: leave it null or default\n item.json.qbo_account_name = \"AMEX - \" + item.json.official_name;\n }\n}\n\nreturn $input.all();\n"
},
"typeVersion": 2
},
{
"id": "56d32edb-7b52-4531-8492-da072e23d561",
"name": "Map Wise Accounts to QBO",
"type": "n8n-nodes-base.code",
"position": [
400,
656
],
"parameters": {
"jsCode": "// Map of mask -> QBO account name\nconst qboMap = {\n \"4940\": \"WISE (USD)\",\n \"6364\": \"WISE (CAD)\"\n};\n\nfor (const item of $input.all()) {\n const mask = item.json.mask;\n if (qboMap[mask]) {\n item.json.qbo_account_name = qboMap[mask];\n } else {\n // fallback: leave it null or default\n item.json.qbo_account_name = \"WISE - \" + item.json.official_name;\n }\n}\n\nreturn $input.all();\n"
},
"typeVersion": 2
},
{
"id": "0fb19a66-3bc2-4f86-8424-cefc2efcd4fb",
"name": "Map PayPal Accounts to QBO",
"type": "n8n-nodes-base.code",
"position": [
400,
848
],
"parameters": {
"jsCode": "// Map of mask -> QBO account name\nconst qboMap = {\n \"4940\": \"WISE (USD)\",\n \"6364\": \"WISE (CAD)\"\n};\n\nfor (const item of $input.all()) {\n const mask = item.json.mask;\n const name = item.json.official_name.charAt(0) + item.json.official_name.substring(1).toLowerCase();\n if (qboMap[mask]) {\n item.json.qbo_account_name = qboMap[mask];\n } else {\n // fallback: leave it null or default\n item.json.qbo_account_name =name;\n }\n}\n\nreturn $input.all();\n"
},
"typeVersion": 2
},
{
"id": "c881c2f6-2169-4a4f-86fa-3a56bb62b0c4",
"name": "Combine All Accounts",
"type": "n8n-nodes-base.merge",
"position": [
624,
528
],
"parameters": {
"numberInputs": 4
},
"typeVersion": 3.2
},
{
"id": "f11d0cb6-4d43-43fd-ad8d-e445b0baddbf",
"name": "Structure Account Records",
"type": "n8n-nodes-base.code",
"position": [
848,
560
],
"parameters": {
"jsCode": "// UUID generator from seed\nconst generateUuidFromSeed = (seed) => {\n const base = seed + Math.random().toString(16);\n const hash = Array.from(base).reduce((hash, char) => {\n return ((hash << 5) - hash) + char.charCodeAt(0);\n }, 0);\n const hex = (hash >>> 0).toString(16).padStart(8, '0');\n return (\n hex.slice(0, 8) + '-' +\n hex.slice(0, 4) + '-' +\n '4' + hex.slice(4, 3) + '-' +\n ((parseInt(hex[0], 16) & 0x3) | 0x8).toString(16) + hex.slice(1, 3) + '-' +\n hex.padEnd(12, '0')\n );\n};\n\nconst input = $input.all();\nconst output = [];\nconst now = new Date().toISOString(); // current timestamp in ISO\n\nfor (const item of input) {\n const account = item.json;\n if (!account) continue;\n\n const seed = `${account.account_id}|${account.qbo_account_name}`;\n const id = generateUuidFromSeed(seed);\n\n // Balances\n let currentBalanceUsd = 0;\n let currentBalanceCad = 0;\n let availableBalanceUsd = 0;\n let availableBalanceCad = 0;\n\n if (account.balances?.iso_currency_code === 'USD') {\n currentBalanceUsd = account.balances.current || 0;\n availableBalanceUsd = account.balances.available || 0;\n } else if (account.balances?.iso_currency_code === 'CAD') {\n currentBalanceCad = account.balances.current || 0;\n availableBalanceCad = account.balances.available || 0;\n }\n\n const record = {\n id: id,\n account_id: account.account_id || null,\n date_created_at: now.slice(0, 10), // YYYY-MM-DD\n name: account.qbo_account_name || null,\n active: true,\n classification: account.subtype || null,\n account_type: account.type || null,\n current_balance_usd: currentBalanceUsd,\n current_balance_cad: currentBalanceCad,\n currency_origin: account.balances?.iso_currency_code || null,\n domain: \"Plaid\",\n account_create_time: now,\n account_last_update_time: now,\n available_balance_usd: availableBalanceUsd,\n available_balance_cad: availableBalanceCad,\n };\n\n output.push({ json: record });\n}\n\nreturn output;\n"
},
"typeVersion": 2
},
{
"id": "49d5cc52-d430-428d-8c68-bff8e68601ee",
"name": "Prepare BigQuery Insert",
"type": "n8n-nodes-base.code",
"position": [
1072,
560
],
"parameters": {
"jsCode": "const formatValue = (val) => {\n if (typeof val === 'number') return val;\n if (typeof val === 'boolean') return val ? 'TRUE' : 'FALSE';\n if (typeof val === 'string') {\n const clean = val\n .replace(/[\\u2018\\u2019\\u201A\\u201B\\u2032\\u2035]/g, '') // remove smart single quotes\n .replace(/[\\u201C\\u201D\\u201E\\u201F\\u2033\\u2036]/g, '\"') // smart double quotes to plain\n .normalize('NFKC') // normalize unicode\n .replace(/[^\\x00-\\x7F]/g, '') // strip non-ASCII\n .replace(/\\?/g, '') // remove ?\n .replace(/'/g, '') // remove all apostrophes\n .replace(/\\n/g, ' ') // replace newlines\n .replace(/\\r/g, '') // remove carriage returns\n .replace(/\\t/g, ' ') // replace tabs\n .replace(/\\\\/g, '\\\\\\\\'); // escape literal backslashes\n return `'${clean}'`;\n }\n\n if (val === null || val === undefined) return `''`; // empty string instead of NULL\n return `'${JSON.stringify(val).replace(/'/g, '')}'`; // sanitize other cases\n};\n\nconst formatDate = (val) => {\n if (!val) return 'NULL';\n return `DATE '${val}'`;\n};\n\nconst rows = $input.all().map(item => {\n const d = item.json;\n\n return `(\n ${formatValue(d.id)},\n ${formatValue(d.account_id)},\n ${formatDate(d.date_created_at)},\n ${formatValue(d.name)},\n ${formatValue(d.active)},\n ${formatValue(d.classification)},\n ${formatValue(d.account_type)},\n ${d.current_balance_usd ?? 'NULL'},\n ${d.current_balance_cad ?? 'NULL'},\n ${formatValue(d.currency_origin)},\n ${formatValue(d.domain)},\n ${formatValue(d.account_create_time)},\n ${formatValue(d.account_last_update_time)},\n ${d.available_balance_usd ?? 'NULL'},\n ${d.available_balance_cad ?? 'NULL'},\n \"description\"\n )`;\n});\n\nreturn [\n {\n json: {\n valuesString: rows.filter(Boolean).join(',\\n') // ensures no comma after the last row\n }\n }\n];\n"
},
"typeVersion": 2
},
{
"id": "894c3083-f95a-4621-aace-1a1df6d23b08",
"name": "Load Accounts into BigQuery",
"type": "n8n-nodes-base.googleBigQuery",
"position": [
1264,
560
],
"parameters": {
"options": {},
"sqlQuery": "INSERT INTO `quickbooks.accounts`\n(\n id,\n account_id,\n date_created_at,\n name,\n active,\n classification,\n account_type,\n current_balance_usd,\n current_balance_cad,\n currency_origin,\n domain,\n account_create_time,\n account_last_update_time,\n available_balance_usd,\n available_balance_cad,\n description\n)\nVALUES\n{{ $json.valuesString }};\n",
"projectId": {
"__rl": true,
"mode": "list",
"value": "n8n-self-host-461314",
"cachedResultUrl": "https://console.cloud.google.com/bigquery?project=n8n-self-host-461314",
"cachedResultName": "n8n-self-host"
}
},
"typeVersion": 2.1,
"alwaysOutputData": true
},
{
"id": "28abdae4-e605-482e-9c37-f3a6c86b0cc4",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1168,
112
],
"parameters": {
"width": 768,
"height": 656,
"content": "## Automated Multi-Bank Balance Sync to BigQuery\n\nThis workflow automatically fetches balances from multiple financial institutions (RBC, Amex, Wise, PayPal) using Plaid, maps them to QuickBooks account names, and loads structured records into Google BigQuery for analytics.\n\n## Who’s it for?\nFinance teams, accountants, and data engineers managing consolidated bank reporting in Google BigQuery.\n\n## How it works\n1. The Schedule Trigger runs weekly.\n2. Four Plaid API calls fetch balances from RBC, Amex, Wise, and PayPal.\n3. Each response splits out individual accounts and maps them to QuickBooks names.\n4. All accounts are merged into one dataset.\n5. The workflow structures the account data, generates UUIDs, and formats SQL inserts.\n6. BigQuery node uploads the finalized records.\n\n## How to set up\nAdd Plaid and Google BigQuery credentials, replace client IDs and secrets with variables, test each connection, and schedule the trigger for your reporting cadence.\n"
},
"typeVersion": 1
}
],
"pinData": {},
"connections": {
"Schedule Trigger": {
"main": [
[
{
"node": "Fetch RBC Balances (Plaid)",
"type": "main",
"index": 0
},
{
"node": "Fetch Amex Balances (Plaid)",
"type": "main",
"index": 0
},
{
"node": "Fetch Wise Balances (Plaid)",
"type": "main",
"index": 0
},
{
"node": "Fetch PayPal Balances (Plaid)",
"type": "main",
"index": 0
}
]
]
},
"Split RBC Accounts": {
"main": [
[
{
"node": "Map RBC Accounts to QBO",
"type": "main",
"index": 0
}
]
]
},
"Split Amex Accounts": {
"main": [
[
{
"node": "Map Amex Accounts to QBO",
"type": "main",
"index": 0
}
]
]
},
"Split Wise Accounts": {
"main": [
[
{
"node": "Map Wise Accounts to QBO",
"type": "main",
"index": 0
}
]
]
},
"Combine All Accounts": {
"main": [
[
{
"node": "Structure Account Records",
"type": "main",
"index": 0
}
]
]
},
"Split PayPal Accounts": {
"main": [
[
{
"node": "Map PayPal Accounts to QBO",
"type": "main",
"index": 0
}
]
]
},
"Map RBC Accounts to QBO": {
"main": [
[
{
"node": "Combine All Accounts",
"type": "main",
"index": 0
}
]
]
},
"Prepare BigQuery Insert": {
"main": [
[
{
"node": "Load Accounts into BigQuery",
"type": "main",
"index": 0
}
]
]
},
"Map Amex Accounts to QBO": {
"main": [
[
{
"node": "Combine All Accounts",
"type": "main",
"index": 1
}
]
]
},
"Map Wise Accounts to QBO": {
"main": [
[
{
"node": "Combine All Accounts",
"type": "main",
"index": 2
}
]
]
},
"Structure Account Records": {
"main": [
[
{
"node": "Prepare BigQuery Insert",
"type": "main",
"index": 0
}
]
]
},
"Fetch RBC Balances (Plaid)": {
"main": [
[
{
"node": "Split RBC Accounts",
"type": "main",
"index": 0
}
]
]
},
"Map PayPal Accounts to QBO": {
"main": [
[
{
"node": "Combine All Accounts",
"type": "main",
"index": 3
}
]
]
},
"Fetch Amex Balances (Plaid)": {
"main": [
[
{
"node": "Split Amex Accounts",
"type": "main",
"index": 0
}
]
]
},
"Fetch Wise Balances (Plaid)": {
"main": [
[
{
"node": "Split Wise Accounts",
"type": "main",
"index": 0
}
]
]
},
"Fetch PayPal Balances (Plaid)": {
"main": [
[
{
"node": "Split PayPal Accounts",
"type": "main",
"index": 0
}
]
]
}
}
}