Sync QuickBooks Chart of Accounts to Google BigQuery

{
  "meta": {
    "instanceId": "6493d417e13567dbe50b02d0e6eea87fdb5934d9207730b096adeb482873eda1"
  },
  "nodes": [
    {
      "id": "02e2f5e1-10c4-4e20-8706-7b80267d2620",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1280,
        -1260
      ],
      "parameters": {
        "width": 660,
        "height": 1340,
        "content": "## Sync QuickBooks Chart of Accounts to Google BigQuery\nKeep a historical, structured copy of your QuickBooks Chart of Accounts in BigQuery. This n8n workflow runs weekly, syncing new or updated accounts for better reporting and long-term tracking.\n\n### Who Is This For?\n* **Data Analysts & BI Developers**\n  Build a robust financial model and analyze changes over time.\n* **Financial Analysts & Accountants**\n  Track structural changes in your Chart of Accounts historically.\n* **Business Owners**\n  Maintain a permanent archive of your financial structure for future reference.\n\n### What the Workflow Does\n* **Extract**\n  Every Monday, fetch accounts created or updated in the past 7 days from QuickBooks.\n* **Transform**\n  Clean the API response, manage currencies, create stable IDs, and format the data.\n* **Format**\n  Convert cleaned data into an SQL insert-ready structure.\n* **Load**\n  Insert or update account records into BigQuery.\n\n### Setup Steps\n**1. Prepare BigQuery**\n* Create a table (e.g., `quickbooks.accounts`) with columns matching the final SQL insert step.\n\n\n**2. Add Credentials**\n* Connect QuickBooks Online and BigQuery credentials in n8n.\n\n\n**3. Configure the HTTP Node**\n* Open `1. Get Updated Accounts from QuickBooks`.\n* Replace the Company ID {COMPANY_ID} with your real Company ID.\n  * Press `Ctrl + Alt + ?` in QuickBooks to find it.\n\n\n**4. Configure the BigQuery Node**\n* Open `4. Load Accounts to BigQuery`.\n* Select the correct project.\n* Make sure your dataset and table name are correctly referenced in the SQL.\n\n\n**5. Activate**\n* Save and activate the workflow. It will now run every week.\n\n### Requirements\n* QuickBooks Online account\n* QuickBooks Company ID\n* Google Cloud project with BigQuery and a matching table\n\n### Customization Options\n* **Change Sync Frequency**\n  Adjust the schedule node to run daily, weekly, etc.\n* **Initial Backfill**\n  Temporarily update the API query to `select * from Account` for a full pull.\n* **Add Fields**\n  Modify `2. Structure Account Data` to include or transform fields as needed.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "381a303e-54bf-446f-8dfc-2a5690fb4953",
      "name": "Start: Weekly on Monday",
      "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. Get Updated Accounts from 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. Structure Account Data",
      "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. Format Data for 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. Load Accounts to 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": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -280,
        -80
      ],
      "parameters": {
        "width": 360,
        "height": 320,
        "content": "Fetches accounts from QuickBooks updated in the last 7 days.\n**ACTION: (MANDATORY)** You must replace the companyId in the URL with your own QuickBooks Company ID."
      },
      "typeVersion": 1
    },
    {
      "id": "aeb0d311-e38e-477c-a931-2089a3a0d223",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        720,
        -80
      ],
      "parameters": {
        "width": 340,
        "height": 340,
        "content": "Inserts the new account data into your accounts table.\nACTION: Select your GCP Project and verify the table name in the SQL query is correct."
      },
      "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
          }
        ]
      ]
    }
  }
}