每周ETL管道：QuickBooks财务数据到Google BigQuery

• •无特殊前置要求，导入即可使用

{
  "meta": {
    "instanceId": "689fa22e68cd4198e4ae37f3cc44f498087edd235a867e22515be823bab694c7",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "92c34609-88d8-4891-9717-ee0f5bb57626",
      "name": "开始：每周一",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1424,
        176
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                1
              ]
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "14e349dc-51e6-4d5d-a059-2005d12ed85a",
      "name": "1. 获取上周的交易记录",
      "type": "n8n-nodes-base.quickbooks",
      "position": [
        -1184,
        176
      ],
      "parameters": {
        "simple": false,
        "filters": {
          "date_macro": "Last Week"
        },
        "resource": "transaction"
      },
      "credentials": {
        "quickBooksOAuth2Api": {
          "id": "A1qrozAz8b5xfsad",
          "name": "QuickBooks Online Test"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "6731e22e-aec2-420b-8bd2-cd8b13a7954f",
      "name": "2. 清理和分类交易记录",
      "type": "n8n-nodes-base.code",
      "position": [
        -720,
        160
      ],
      "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\n// Known transaction type classifications\nconst expenseTypes = new Set([\n  'Expense', 'Bill', 'Check', 'Credit Card Charge', 'Purchase', 'Vendor Credit', 'Bank Fee',\n]);\n\nconst incomeTypes = new Set([\n  'Invoice', 'Payment', 'Deposit', 'Sales Receipt', 'Credit Memo', 'Refund',\n]);\n\nconst internalTransferAccounts = new Set([\n  //FILL HERE\n]);\n\nconst expenseCategories = new Set([\n  //FILL HERE\n]);\n\nconst incomeCategories = new Set([\n  //FILL HERE\n]);\n\nconst output = [];\n\nfor (const item of $input.all()) {\n  const report = item.json;\n  const weekStartDate = report.Header?.StartPeriod || null;\n  const currency = report.Header?.Currency || null;\n\n  if (report.Rows && report.Rows.Row) {\n    for (const row of report.Rows.Row) {\n      if (row.type && row.type !== 'Data') continue;\n\n      const cols = row.ColData;\n      if (!cols || cols.length < 9) continue;\n\n      const txnType = cols[1]?.value || '';\n      const splitAccount = cols[7]?.value || null;\n\n      let amountRaw = cols[8]?.value || '0';\n      let amountNum = parseFloat(amountRaw);\n      if (isNaN(amountNum)) amountNum = 0;\n\n      const seed = [\n        cols[0]?.value || '',\n        txnType,\n        cols[4]?.value || '',\n        amountNum.toString()\n      ].join('|');\n\n      // Default classifications\n      let isExpense = false;\n      let isIncome = false;\n\n      // Priority: category-based override\n      if (expenseCategories.has(splitAccount)) {\n        isExpense = true;\n        isIncome = false;\n      } else if (incomeCategories.has(splitAccount)) {\n        isIncome = true;\n        isExpense = false;\n      } else if (txnType === \"Transfer\") {\n        if (internalTransferAccounts.has(splitAccount)) {\n          isExpense = false;\n          isIncome = false;\n        } else {\n          isExpense = amountNum < 0;\n          isIncome = amountNum > 0;\n        }\n      } else {\n        isExpense = amountNum < 0 || expenseTypes.has(txnType);\n        isIncome = incomeTypes.has(txnType) && !isExpense;\n      }\n\n      const amount = Math.abs(amountNum);\n      const amountSigned = isExpense ? -amount : amount;\n\n      let amount_usd = 0;\n      let amount_cad = 0;\n      if (currency === 'CAD') {\n        amount_cad = amount;\n      } else if (currency === 'USD') {\n        amount_usd = amount;\n      }\n\n      const record = {\n        id: generateUuidFromSeed(seed),\n        txn_date: cols[0]?.value || null,\n        txn_type: txnType,\n        doc_num: cols[2]?.value || null,\n        is_posted: cols[3]?.value === \"Yes\",\n        name: cols[4]?.value || null,\n        memo: cols[5]?.value || null,\n        account_name: cols[6]?.value || null,\n        account_id: cols[6]?.id || null,\n        category: splitAccount,\n        category_id: cols[7]?.id || null,\n        signed_amount: amountSigned,\n        is_expense: isExpense,\n        is_income: isIncome,\n        amount_usd: amount_usd,\n        amount_cad: amount_cad,\n        currency: currency,\n        week_start_date: weekStartDate,\n        source_id: cols[1]?.id || ''\n      };\n\n      output.push({ json: record });\n    }\n  }\n}\n\nreturn output;"
      },
      "typeVersion": 2
    },
    {
      "id": "a19aaed6-185f-4380-bc48-debb46976e16",
      "name": "3. 为 SQL 格式化数据",
      "type": "n8n-nodes-base.code",
      "position": [
        -256,
        176
      ],
      "parameters": {
        "jsCode": "const formatValue = (val) => {\n  if (val === null || val === undefined) return 'NULL';\n  if (typeof val === 'number') return val;\n  if (typeof val === 'boolean') return val ? 'TRUE' : 'FALSE';\n\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 → \"\n      .normalize('NFKC')                                        // normalize accents/compatibility\n      .replace(/[^\\x00-\\x7F]/g, '')                             // strip non-ASCII\n      .replace(/\\?/g, '')                                      // remove question marks\n      .replace(/'/g, '')                                       // remove apostrophes\n      .replace(/\\n/g, ' ')                                     // replace line breaks with space\n      .replace(/\\r/g, '')                                      // remove carriage returns\n      .replace(/\\t/g, ' ')                                     // replace tabs with space\n      .replace(/\\\\/g, '');                                     // remove literal backslashes\n    return `'${clean}'`;\n  }\n\n  // Catch-all stringify fallback\n  return `'${JSON.stringify(val).replace(/'/g, '')}'`;\n};\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.source_id)},\n    ${formatDate(d.txn_date || d.date)},\n    ${formatValue(d.txn_type || d.type)},\n    ${formatValue(d.doc_num)},\n    ${formatValue(d.is_posted)},\n    ${formatValue(d.name)},\n    ${formatValue(d.memo)},\n    ${formatValue(d.account_name)},\n    ${formatValue(d.category)},\n    ${d.amount_usd ?? 'NULL'},\n    ${formatValue(d.is_expense)},\n    ${formatValue(d.is_income)},\n    ${formatDate(d.week_start_date)},\n    ${d.amount_cad ?? 'NULL'},\n    ${formatValue(d.account_id)},\n    ${formatValue(d.category_id)},\n    ${d.signed_amount ?? 'NULL'},\n    ${formatValue(d.currency)}\n  )`;\n});\n\nreturn [\n  {\n    json: {\n      valuesString: rows.join(',\\n')\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "4405def8-7618-4d5f-8789-db975a5c169b",
      "name": "4. 将数据加载到 BigQuery",
      "type": "n8n-nodes-base.googleBigQuery",
      "position": [
        80,
        176
      ],
      "parameters": {
        "options": {},
        "sqlQuery": "INSERT INTO `quickbooks.transactions`\n(\n  id,\n  source_id,\n  date,\n  type,\n  doc_num,\n  is_posted,\n  name,\n  memo,\n  account_name,\n  category,\n  amount_usd,\n  is_expense,\n  is_income,\n  week_start_date,\n  amount_cad,\n  account_id,\n  category_id,\n  signed_amount,\n  currency\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"
        }
      },
      "credentials": {
        "googleBigQueryOAuth2Api": {
          "id": "lW59SbDcjoy07IvX",
          "name": "Google BigQuery Test"
        }
      },
      "typeVersion": 2.1,
      "alwaysOutputData": true
    },
    {
      "id": "6989fb08-d02d-4519-a67a-8cf55b633b58",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1920,
        -1840
      ],
      "parameters": {
        "width": 608,
        "height": 1408,
        "content": "## 将 QuickBooks 交易记录同步并丰富到 Google BigQuery"
      },
      "typeVersion": 1
    },
    {
      "id": "8238ff82-91db-4c13-8add-a555a8d24897",
      "name": "便签 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -832,
        0
      ],
      "parameters": {
        "width": 336,
        "height": 304,
        "content": "这是工作流的核心逻辑。"
      },
      "typeVersion": 1
    },
    {
      "id": "d8b8a131-b3ff-4eb8-a79d-631caf718142",
      "name": "便签 4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -352,
        32
      ],
      "parameters": {
        "width": 304,
        "height": 272,
        "content": "此节点获取清理后的数据，并将其转换为单个安全的字符串，用于批量 SQL INSERT 命令。通常此处无需更改。"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "3. Format Data for SQL": {
      "main": [
        [
          {
            "node": "4. Load Data to BigQuery",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start: Weekly on Monday": {
      "main": [
        [
          {
            "node": "1. Get Last Week's Transactions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4. Load Data to BigQuery": {
      "main": [
        []
      ]
    },
    "1. Get Last Week's Transactions": {
      "main": [
        [
          {
            "node": "2. Clean & Classify Transactions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2. Clean & Classify Transactions": {
      "main": [
        [
          {
            "node": "3. Format Data for SQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}