Google Sheets和QuickBooks费用自动化模板
高级
这是一个Invoice Processing, Multimodal AI领域的自动化工作流，包含 16 个节点。主要使用 If, Set, SplitOut, Quickbooks, HttpRequest 等节点。 Google Sheets和QuickBooks费用自动化模板
前置要求
- •可能需要目标 API 的认证凭证
- •Google Sheets API 凭证
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"meta": {
"instanceId": "d6e2f2f655b1125bbcac14a4cac6d2e46c7a150e927f85fc96fdca1a6dc39e0e",
"templateCredsSetupCompleted": true
},
"nodes": [
{
"id": "4678f902-8b59-42f2-adab-f123ca1f637f",
"name": "当点击\"执行工作流\"时",
"type": "n8n-nodes-base.manualTrigger",
"position": [
-16,
192
],
"parameters": {},
"typeVersion": 1
},
{
"id": "5b68c737-5caa-4d36-b957-5b4458c759a8",
"name": "获取 QuickBooks 中的活跃供应商",
"type": "n8n-nodes-base.quickbooks",
"position": [
880,
0
],
"parameters": {
"filters": {},
"resource": "vendor",
"operation": "getAll",
"returnAll": true
},
"credentials": {
"quickBooksOAuth2Api": {
"id": "zZyR3nZpTizUyR0u",
"name": "QuickBooks n8n Sandbox"
}
},
"executeOnce": true,
"typeVersion": 1
},
{
"id": "5f464de5-83b7-4fd2-9068-93422b2356bf",
"name": "获取会计科目表",
"type": "n8n-nodes-base.httpRequest",
"position": [
432,
192
],
"parameters": {
"url": "=https://sandbox-quickbooks.api.intuit.com/v3/company/{{ $json.realmID }}/query",
"options": {},
"sendQuery": true,
"authentication": "predefinedCredentialType",
"queryParameters": {
"parameters": [
{
"name": "query",
"value": "select * from Account where active=true maxResults 500"
},
{
"name": "minorversion",
"value": "75"
}
]
},
"nodeCredentialType": "quickBooksOAuth2Api"
},
"credentials": {
"quickBooksOAuth2Api": {
"id": "zZyR3nZpTizUyR0u",
"name": "QuickBooks n8n Sandbox"
}
},
"executeOnce": true,
"typeVersion": 4.2
},
{
"id": "0b295195-8e3b-4d64-b1ef-ca4dbfca84c9",
"name": "拆分账户",
"type": "n8n-nodes-base.splitOut",
"position": [
656,
192
],
"parameters": {
"options": {},
"fieldToSplitOut": "QueryResponse.Account"
},
"typeVersion": 1
},
{
"id": "aaaa6cf5-8e29-42d1-a182-b4506dd73583",
"name": "将账户添加到 Google Sheets 模板",
"type": "n8n-nodes-base.googleSheets",
"position": [
880,
192
],
"parameters": {
"columns": {
"value": {
"ID": "={{ $json.Id }}",
"Name": "={{ $json.Name }}",
"Account Type": "={{ $json.AccountType }}"
},
"schema": [
{
"id": "Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Account Type",
"type": "string",
"display": true,
"required": false,
"displayName": "Account Type",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"ID"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "appendOrUpdate",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 124163501,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit#gid=124163501",
"cachedResultName": "Accounts"
},
"documentId": {
"__rl": true,
"mode": "url",
"value": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit?usp=sharing"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "iNp7jBkCGXjTLqFd",
"name": "Rosh's Personal Sheets Account"
}
},
"typeVersion": 4.6
},
{
"id": "a37448d0-6c95-4bf0-8504-3519abda9d49",
"name": "在 QBO 中添加费用",
"type": "n8n-nodes-base.httpRequest",
"onError": "continueErrorOutput",
"position": [
880,
384
],
"parameters": {
"url": "=https://sandbox-quickbooks.api.intuit.com/v3/company/{{ $('Set Realm ID for Custom API Call').item.json.realmID }}/purchase",
"method": "POST",
"options": {},
"jsonBody": "={\n \"PaymentType\": \"Cash\",\n \"TxnDate\": \"{{ $json.Date.toDateTime('M/d/y').toFormat('yyyy-MM-dd') }}\",\n \"PrivateNote\": \"{{ $json.Description }}\",\n \"EntityRef\": {\n \"value\": \"{{ $json['Vendor ID'] }}\"\n },\n \"AccountRef\": {\n \"value\": \"{{ $json['Asset ID'] }}\"\n },\n \"Line\": [\n {\n \"DetailType\": \"AccountBasedExpenseLineDetail\",\n \"Amount\": {{ $json.Total }},\n \"AccountBasedExpenseLineDetail\": {\n \"AccountRef\": {\n \"value\": \"{{ $json['Expense ID'] }}\"\n }\n }\n }\n ]\n}\n",
"sendBody": true,
"sendQuery": true,
"specifyBody": "json",
"authentication": "predefinedCredentialType",
"queryParameters": {
"parameters": [
{
"name": "minorversion",
"value": "75"
}
]
},
"nodeCredentialType": "quickBooksOAuth2Api"
},
"credentials": {
"quickBooksOAuth2Api": {
"id": "zZyR3nZpTizUyR0u",
"name": "QuickBooks n8n Sandbox"
}
},
"executeOnce": false,
"typeVersion": 4.2
},
{
"id": "b1ed3c64-f832-4822-ae8a-2bf04cd7dd01",
"name": "从 Google Sheets 获取新供应商",
"type": "n8n-nodes-base.googleSheets",
"position": [
208,
0
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1066861641,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit#gid=1066861641",
"cachedResultName": "Vendors"
},
"documentId": {
"__rl": true,
"mode": "url",
"value": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit?usp=sharing"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "iNp7jBkCGXjTLqFd",
"name": "Rosh's Personal Sheets Account"
}
},
"typeVersion": 4.6
},
{
"id": "5ed5cff9-7c9a-48c0-90f7-fdb158e10983",
"name": "刷新 Google Sheets 模板中的供应商",
"type": "n8n-nodes-base.googleSheets",
"position": [
1104,
0
],
"parameters": {
"columns": {
"value": {
"ID": "={{ $json.Id }}",
"Name": "={{ $json.DisplayName }}"
},
"schema": [
{
"id": "Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "ID",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"ID"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "appendOrUpdate",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1066861641,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit#gid=1066861641",
"cachedResultName": "Vendors"
},
"documentId": {
"__rl": true,
"mode": "url",
"value": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit?usp=sharing"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "iNp7jBkCGXjTLqFd",
"name": "Rosh's Personal Sheets Account"
}
},
"typeVersion": 4.6
},
{
"id": "6de704e8-7007-4802-ac84-10f81ca21af3",
"name": "获取新的费用交易",
"type": "n8n-nodes-base.googleSheets",
"position": [
432,
384
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupColumn": "Transaction ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit#gid=0",
"cachedResultName": "Expenses"
},
"documentId": {
"__rl": true,
"mode": "url",
"value": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit?usp=sharing"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "iNp7jBkCGXjTLqFd",
"name": "Rosh's Personal Sheets Account"
}
},
"typeVersion": 4.6
},
{
"id": "17d09d9c-028a-4ccd-b2d7-1fc269e7fcca",
"name": "去重",
"type": "n8n-nodes-base.removeDuplicates",
"position": [
432,
0
],
"parameters": {
"compare": "selectedFields",
"options": {},
"fieldsToCompare": "Name"
},
"typeVersion": 2,
"alwaysOutputData": true
},
{
"id": "ff8d137b-b491-49e4-b656-81896a6bed0e",
"name": "移除空值",
"type": "n8n-nodes-base.if",
"position": [
656,
384
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "2aa9700c-74c1-464a-bce8-beb2ce28c7da",
"operator": {
"type": "string",
"operation": "empty",
"singleValue": true
},
"leftValue": "={{ $json['Transaction ID'] }}",
"rightValue": ""
},
{
"id": "481f12f7-e8d0-4c80-bb3c-c21850c8795c",
"operator": {
"type": "string",
"operation": "notEmpty",
"singleValue": true
},
"leftValue": "={{ $json.Vendor }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "da89ac36-ba2b-431f-b2ff-345951d90a45",
"name": "在 Google Sheets 中记录交易 ID",
"type": "n8n-nodes-base.googleSheets",
"position": [
1104,
288
],
"parameters": {
"columns": {
"value": {
"#": "={{ $('Remove Empties').item.json['#'] }}",
"Transaction ID": "={{ $json.Purchase.Id }}"
},
"schema": [
{
"id": "#",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "#",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Description",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Description",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Total",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Total",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Vendor",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Vendor",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Asset Account",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Asset Account",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Expense Account",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Expense Account",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Transaction ID",
"type": "string",
"display": true,
"required": false,
"displayName": "Transaction ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Message",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Message",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Vendor ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Vendor ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Asset ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Asset ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Expense ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Expense ID",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"#"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/10pdz8hvNsCWX6IogQ_Nyb2qlXmOHWtgsAH4EgAUKfSw/edit#gid=0",
"cachedResultName": "Expenses"
},
"documentId": {
"__rl": true,
"mode": "url",
"value": "https://docs.google.com/spreadsheets/d/10pdz8hvNsCWX6IogQ_Nyb2qlXmOHWtgsAH4EgAUKfSw/edit?pli=1&gid=0#gid=0"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "iNp7jBkCGXjTLqFd",
"name": "Rosh's Personal Sheets Account"
}
},
"typeVersion": 4.6
},
{
"id": "fb36bbce-7b08-4ec1-aeaf-4fbc409a3f4c",
"name": "在 QuickBooks 中创建新供应商",
"type": "n8n-nodes-base.quickbooks",
"onError": "continueRegularOutput",
"position": [
656,
0
],
"parameters": {
"resource": "vendor",
"operation": "create",
"displayName": "={{ $json.Name }}",
"additionalFields": {}
},
"credentials": {
"quickBooksOAuth2Api": {
"id": "zZyR3nZpTizUyR0u",
"name": "QuickBooks n8n Sandbox"
}
},
"executeOnce": false,
"typeVersion": 1
},
{
"id": "615ced03-6162-494f-9006-7aef2b8f10b6",
"name": "为自定义 API 调用设置 Realm ID",
"type": "n8n-nodes-base.set",
"position": [
208,
288
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "df10e087-0f27-4d07-98ba-aee302ee5477",
"name": "realmID",
"type": "string",
"value": ""
}
]
}
},
"executeOnce": true,
"typeVersion": 3.4
},
{
"id": "cd56254b-d062-40f0-87dc-f3c6d1bca8c3",
"name": "记录错误消息",
"type": "n8n-nodes-base.googleSheets",
"position": [
1104,
480
],
"parameters": {
"columns": {
"value": {
"#": "={{ $json['#'] }}",
"Message": "={{ $json.error }}"
},
"schema": [
{
"id": "#",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "#",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Description",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Description",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Total",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Total",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Vendor",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Vendor",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Asset Account",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Asset Account",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Expense Account",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Expense Account",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Transaction ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Transaction ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Vendor ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Vendor ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Asset ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Asset ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Expense ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Expense ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Message",
"type": "string",
"display": true,
"required": false,
"displayName": "Message",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"#"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "appendOrUpdate",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/10pdz8hvNsCWX6IogQ_Nyb2qlXmOHWtgsAH4EgAUKfSw/edit#gid=0",
"cachedResultName": "Expenses"
},
"documentId": {
"__rl": true,
"mode": "url",
"value": "https://docs.google.com/spreadsheets/d/1dmkXHeMghVp5AHrdyU1vrwjUHWNaoDfkk9UOuG-SNKI/edit?usp=sharing"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "iNp7jBkCGXjTLqFd",
"name": "Rosh's Personal Sheets Account"
}
},
"typeVersion": 4.6
},
{
"id": "cc3c8c70-63c7-4c48-bcd2-3c8321f7f489",
"name": "便签",
"type": "n8n-nodes-base.stickyNote",
"position": [
-800,
-448
],
"parameters": {
"width": 660,
"height": 2192,
"content": "## 从 Google Sheets 自动上传费用到 QuickBooks"
},
"typeVersion": 1
}
],
"pinData": {},
"connections": {
"Remove Empties": {
"main": [
[
{
"node": "Add an Expense to QBO",
"type": "main",
"index": 0
}
]
]
},
"Remove Duplicates": {
"main": [
[
{
"node": "Create New Vendors in QuickBooks",
"type": "main",
"index": 0
}
]
]
},
"Split Out Accounts": {
"main": [
[
{
"node": "Add Accounts to Google Sheet Template",
"type": "main",
"index": 0
}
]
]
},
"Add an Expense to QBO": {
"main": [
[
{
"node": "Record Txn ID in Google Sheets",
"type": "main",
"index": 0
}
],
[
{
"node": "Record Error Message",
"type": "main",
"index": 0
}
]
]
},
"Get Chart of Accounts": {
"main": [
[
{
"node": "Split Out Accounts",
"type": "main",
"index": 0
}
]
]
},
"Get New Expense Transactions": {
"main": [
[
{
"node": "Remove Empties",
"type": "main",
"index": 0
}
]
]
},
"Create New Vendors in QuickBooks": {
"main": [
[
{
"node": "Get Active Vendors in QuickBooks",
"type": "main",
"index": 0
}
],
[]
]
},
"Get Active Vendors in QuickBooks": {
"main": [
[
{
"node": "Refresh Vendors in Google Sheet Template",
"type": "main",
"index": 0
}
]
]
},
"Set Realm ID for Custom API Call": {
"main": [
[
{
"node": "Get Chart of Accounts",
"type": "main",
"index": 0
},
{
"node": "Get New Expense Transactions",
"type": "main",
"index": 0
}
]
]
},
"Get New Vendors from Google Sheets": {
"main": [
[
{
"node": "Remove Duplicates",
"type": "main",
"index": 0
}
]
]
},
"When clicking ‘Execute workflow’": {
"main": [
[
{
"node": "Get New Vendors from Google Sheets",
"type": "main",
"index": 0
},
{
"node": "Set Realm ID for Custom API Call",
"type": "main",
"index": 0
}
]
]
},
"Add Accounts to Google Sheet Template": {
"main": [
[]
]
},
"Refresh Vendors in Google Sheet Template": {
"main": [
[]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 发票处理, 多模态 AI
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
使用Google Drive、OCR和OpenAI处理扫描发票到Google表格
使用Google Drive、OCR和OpenAI处理扫描发票到Google表格
If
Set
Html
+13
24 节点Ovadia Rocks
发票处理
Printify自动化 - 更新标题和描述 - AlexK1919
使用GPT-4o-mini为Printify自动生成SEO产品标题和描述
If
Set
Code
+10
26 节点Amit Mehta
内容创作
使用Google Maps、GPT-4和WhatsApp自动化商业合作伙伴拓展
使用Google Maps、GPT-4和WhatsApp自动化商业合作伙伴拓展
If
Set
Code
+23
64 节点Khairul Muhtadin
AI 聊天机器人
内容聚合
使用Gemini AI从网站文章自动化社交媒体帖子发布到LinkedIn和X/Twitter
If
Set
Xml
+16
34 节点Vadim
内容创作
LinkedIn和X病毒内容自动引擎
使用AI生成和发布自动创建LinkedIn和X的病毒内容
If
Set
Wait
+26
156 节点Diptamoy Barman
内容创作
ELV网站邮箱提取
使用EmailListVerify API从网站提取邮箱地址并存储到Google Sheets
If
Set
Code
+6
20 节点EmailListVerify
潜在客户开发
