使用 Plaid 将多银行余额数据同步至 BigQuery
高级
这是一个Invoice Processing领域的自动化工作流，包含 18 个节点。主要使用 Code, Merge, SplitOut, HttpRequest, GoogleBigQuery 等节点。 使用 Plaid 将多银行余额数据同步至 BigQuery
前置要求
- •可能需要目标 API 的认证凭证
分类
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"meta": {
"instanceId": "689fa22e68cd4198e4ae37f3cc44f498087edd235a867e22515be823bab694c7"
},
"nodes": [
{
"id": "b975d014-c7f8-4f0a-af75-183ca678af46",
"name": "计划触发器",
"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": "获取 RBC 余额 (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": "获取 Amex 余额 (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": "获取 Wise 余额 (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": "获取 PayPal 余额 (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": "拆分 RBC 账户",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
272
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "08f9605e-0fb6-441d-8ddc-285b6c63fcc8",
"name": "拆分 Amex 账户",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
464
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "2c41813c-4cc7-4d51-8b96-5cf740eda48b",
"name": "拆分 Wise 账户",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
656
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "80167b88-42ed-461a-9f4e-53fa6c905105",
"name": "拆分 PayPal 账户",
"type": "n8n-nodes-base.splitOut",
"position": [
176,
848
],
"parameters": {
"options": {},
"fieldToSplitOut": "accounts"
},
"typeVersion": 1
},
{
"id": "9ed9eb63-0de6-4e3b-97ab-a493f8abc432",
"name": "映射 RBC 账户到 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": "映射 Amex 账户到 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": "映射 Wise 账户到 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": "映射 PayPal 账户到 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": "合并所有账户",
"type": "n8n-nodes-base.merge",
"position": [
624,
528
],
"parameters": {
"numberInputs": 4
},
"typeVersion": 3.2
},
{
"id": "f11d0cb6-4d43-43fd-ad8d-e445b0baddbf",
"name": "结构化账户记录",
"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": "准备 BigQuery 插入",
"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": "将账户加载到 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": "便签1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1168,
112
],
"parameters": {
"width": 768,
"height": 656,
"content": "## 自动化多银行余额同步到 BigQuery"
},
"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
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 发票处理
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
同步 QuickBooks 科目表到 Google BigQuery
将 QuickBooks 科目表同步到 Google BigQuery
Code
Http Request
Google Big Query
+2
8 节点Fahmi Fahreza
工程
每周ETL管道：QuickBooks财务数据到Google BigQuery
每周ETL管道：QuickBooks财务数据到Google BigQuery
Code
Quickbooks
Google Big Query
+2
8 节点Fahmi Fahreza
工程
每周SEO监控清单审计至Google Sheets（使用Gemini和Decodo）
每周SEO监控清单审计至Google Sheets（使用Gemini和Decodo）
Set
Code
Merge
+9
19 节点Fahmi Fahreza
市场调研
自动化新闻到简报AI代理v13
AI新闻研究团队：24/7简报自动化，含Perplexity引用
Set
Code
Gmail
+15
37 节点Derek Cheung
产品
基于AI的会议研究与每日议程（Google日历、Attio CRM和Slack）
基于AI的会议研究与每日议程：使用Google日历、Attio CRM和Slack
If
Set
Code
+15
30 节点Harry Siggins
AI 摘要总结
MailChimp自动化
基于AI的餐厅通讯生成器，集成Mailchimp和Telegram审批
If
Set
Code
+16
43 节点Femi Ad
人工智能
