同步 QuickBooks 科目表到 Google BigQuery

• •可能需要目标 API 的认证凭证

{
  "meta": {
    "instanceId": "6493d417e13567dbe50b02d0e6eea87fdb5934d9207730b096adeb482873eda1"
  },
  "nodes": [
    {
      "id": "02e2f5e1-10c4-4e20-8706-7b80267d2620",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1280,
        -1260
      ],
      "parameters": {
        "width": 660,
        "height": 1340,
        "content": "## 将 QuickBooks 会计科目表同步到 Google BigQuery"
      },
      "typeVersion": 1
    },
    {
      "id": "381a303e-54bf-446f-8dfc-2a5690fb4953",
      "name": "开始：每周一",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -540,
        60
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                1
              ]
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "0f9cd612-4e11-4edb-a8ff-81ac30e32bed",
      "name": "1. 从 QuickBooks 获取更新的账户",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -160,
        60
      ],
      "parameters": {
        "url": "https://quickbooks.api.intuit.com/v3/company/{COMPANY_ID}/query",
        "options": {},
        "sendQuery": true,
        "sendHeaders": true,
        "authentication": "predefinedCredentialType",
        "queryParameters": {
          "parameters": [
            {
              "name": "query",
              "value": "=select * from Account Where MetaData.LastUpdatedTime > '{{ $now.minus(7,'days') }}'"
            }
          ]
        },
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        },
        "nodeCredentialType": "quickBooksOAuth2Api"
      },
      "credentials": {
        "quickBooksOAuth2Api": {
          "id": "EDeCW7HlPXed1qbK",
          "name": "QuickBooks Online account"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "72089ab0-2ecf-477c-8177-27ee9d4a9b29",
      "name": "2. 构建账户数据结构",
      "type": "n8n-nodes-base.code",
      "position": [
        240,
        60
      ],
      "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 = [];\n\nfor (const item of input) {\n  const queryResponse = item.json.QueryResponse;\n  if (!queryResponse || !queryResponse.Account) continue;\n\n  // Extract date_created_at from top-level \"time\" field\n  let dateCreatedAt = null;\n  if (item.json.time) {\n    dateCreatedAt = item.json.time.slice(0, 10); // YYYY-MM-DD\n  }\n\n  for (const account of queryResponse.Account) {\n    const seed = `${account.Id}|${account.Name}`;\n    const id = generateUuidFromSeed(seed);\n\n    // Set balances based on origin currency\n    let currentBalanceUsd = 0;\n    let currentBalanceCad = 0;\n\n    if (account.CurrencyRef?.value === 'USD') {\n      currentBalanceUsd = account.CurrentBalance;\n    } else if (account.CurrencyRef?.value === 'CAD') {\n      currentBalanceCad = account.CurrentBalance;\n    }\n\n    const record = {\n      id: id,\n      account_id: account.Id || null,\n      date_created_at: dateCreatedAt,\n      name: account.Name || null,\n      active: account.Active || null,\n      classification: account.Classification || null,\n      account_type: account.AccountType || null,\n      current_balance_usd: currentBalanceUsd,\n      current_balance_cad: currentBalanceCad,\n      currency_origin: account.CurrencyRef?.value || null,\n      domain: account.domain || null,\n      account_create_time: account.MetaData?.CreateTime || null,\n      account_last_update_time: account.MetaData?.LastUpdatedTime || null,\n    };\n\n    output.push({ json: record });\n  }\n}\n\nreturn output;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "c5a90944-e1de-4fb5-b030-19e8da1f6b33",
      "name": "3. 为 SQL 格式化数据",
      "type": "n8n-nodes-base.code",
      "position": [
        460,
        60
      ],
      "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\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    \"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": "65abd488-48ad-493c-8280-3363b9db1428",
      "name": "4. 将账户加载到 BigQuery",
      "type": "n8n-nodes-base.googleBigQuery",
      "position": [
        840,
        60
      ],
      "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  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"
        }
      },
      "credentials": {
        "googleBigQueryOAuth2Api": {
          "id": "CMSEb8EHWOqwqlmN",
          "name": "Google BigQuery account"
        }
      },
      "typeVersion": 2.1,
      "alwaysOutputData": true
    },
    {
      "id": "b9c0f8ff-3482-4ae3-a03b-6627f5209c17",
      "name": "便签 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -280,
        -80
      ],
      "parameters": {
        "width": 360,
        "height": 320,
        "content": "获取过去 7 天内 QuickBooks 中更新的账户。"
      },
      "typeVersion": 1
    },
    {
      "id": "aeb0d311-e38e-477c-a931-2089a3a0d223",
      "name": "便签 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        720,
        -80
      ],
      "parameters": {
        "width": 340,
        "height": 340,
        "content": "将新的账户数据插入到您的账户表中。"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "3. Format Data for SQL": {
      "main": [
        [
          {
            "node": "4. Load Accounts to BigQuery",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start: Weekly on Monday": {
      "main": [
        [
          {
            "node": "1. Get Updated Accounts from QuickBooks",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2. Structure Account Data": {
      "main": [
        [
          {
            "node": "3. Format Data for SQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1. Get Updated Accounts from QuickBooks": {
      "main": [
        [
          {
            "node": "2. Structure Account Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}