QuickBooks Desktop and Google BigQuery integration

What moves between them

The flow runs from QuickBooks Desktop into Google BigQuery. On each Web Connector cycle, ml-connector queries QuickBooks for bills, invoices, payments received, bill payments, purchase orders, chart of accounts, and journal entries changed since the last successful sync, then streams those rows into the matching BigQuery tables you have defined in your dataset. Cadence follows the Web Connector polling interval, typically every 5 to 15 minutes while QuickBooks is open, since QuickBooks offers no real-time push. BigQuery is treated as a read-only analytics destination, so ml-connector does not write records from BigQuery back into the accounting file.

How ml-connector handles it

ml-connector stores both credential sets encrypted and bridges two very different access models. On the QuickBooks side it hosts the SOAP endpoint the Web Connector calls, validates the password in the authenticate method, issues a session ticket, and returns QBXML query blobs for each entity. It uses a ModifiedDateRangeFilter with the last successful sync timestamp so each cycle pulls only changed records, and it walks the QBXML iterator (iterator Start then Continue with the IteratorID) to page large result sets across round-trips, staying under the roughly 60-second per-request timeout. To catch removals it runs a TxnDeletedQueryRq for transactions and tracks the IsActive flag on list records. On the BigQuery side it signs a JWT with the service-account private key, exchanges it for a bearer token, and refreshes before the 3600-second expiry. Rows are written with tabledata.insertAll using a stable insertId derived from the QuickBooks TxnID or ListID, giving best-effort dedup so a retried Web Connector cycle does not double-post. A gotcha to plan for: the BigQuery sandbox tier blocks streaming inserts and DML, so a billing-enabled project is required to write rows. Failed writes are retried with backoff and every record carries a full audit trail.

A real-world example

A regional construction subcontractor with about 80 employees runs QuickBooks Desktop Enterprise on a Windows server in the office for all accounts payable, billing, and the general ledger. Leadership wants weekly job-cost and cash-flow dashboards that combine accounting data with project and timekeeping data already sitting in BigQuery, but the numbers live in a desktop file no reporting tool can reach. Before the integration the controller exported reports to spreadsheets and uploaded them by hand, so dashboards were always a week stale. With QuickBooks Desktop and Google BigQuery connected, bills, invoices, payments, and journal entries flow into the warehouse on every Web Connector cycle, and the dashboards refresh from current data without anyone re-keying or exporting.

What you can do

• •Stream QuickBooks Desktop bills, invoices, payments, purchase orders, and journal entries into Google BigQuery tables you define.
• •Mirror the QuickBooks chart of accounts into BigQuery so financial reports group by the same accounts.
• •Pull only records changed since the last sync using QBXML modified-date filters and iterator pagination.
• •Bridge the QuickBooks Web Connector session ticket to a Google service-account OAuth2 token, both stored encrypted.
• •Write rows with a stable insertId for best-effort dedup, with retries and a full audit trail on every record.

Questions

Which direction does data move between QuickBooks Desktop and Google BigQuery?

Data moves from QuickBooks Desktop into Google BigQuery. The connector reads accounting records through the Web Connector and writes them into BigQuery tables for analytics and reporting. BigQuery is treated as a read-only destination, so ml-connector does not push records from the warehouse back into the QuickBooks company file.

Why does this integration require something installed on the QuickBooks machine?

QuickBooks Desktop has no cloud API, so the only supported access path is the QuickBooks Web Connector, a free Intuit application that runs on the same Windows machine as QuickBooks. It polls the connector endpoint on a set interval and relays QBXML messages to the local company file. QuickBooks must be open with the company file loaded for any sync to run, which is why this is scheduled polling rather than real-time.

Can the connector write rows into the free BigQuery sandbox?

No. The BigQuery sandbox tier does not allow streaming inserts or DML, which the connector relies on to write rows. A billing-enabled Google Cloud project is required for the write path, though the free monthly storage and query allowances still apply. The service account also needs the dataEditor and jobUser roles to insert rows and run jobs.