How to connect QuickBooks to Google Sheets so the numbers tie out

Exporting accounting data into a spreadsheet has a failure mode that other exports do not: the number can be defensibly correct and still disagree with the accounts. Accrual against cash basis, a period that has been closed and adjusted, a transaction dated differently from when it was entered — each produces a figure somebody will challenge, and they will be right to.

So the work here is less about moving data and more about making the sheet say which question it answered. Get that right and the export is trusted; get it wrong once and nobody uses it again.

What we'll cover

• Why your figure disagrees with the books
• Reports or transactions
• Building it with CodeWords
• Closed periods and adjustments
• The chart of accounts, which changes
• Labelling the sheet so it is defensible
• What should not leave QuickBooks
• Operational reports people actually chase
• Making it survive
• Limits worth knowing about
• What to set up first
• Frequently asked questions

Why your figure disagrees with the books

Five reasons, and all of them are working as intended.

Accrual against cash basis. The same period produces different revenue depending on which basis you ask for, and the API lets you choose, so an export that does not state which has answered an ambiguous question.

Transaction date against entry date. An invoice dated last month and entered this month belongs to last month in the accounts and to this month in a naive query.

The period was adjusted after you exported. Accountants post journals into closed periods, which changes historical figures after the fact.

Deleted and voided transactions behave differently, and a query that ignores the distinction counts things that are no longer there.

Multi-currency. The exchange rate applied at transaction time differs from today's, so a converted total depends on which rate you used and when.

None of these is an error. Each is a question your export answered implicitly, and saying which answer you took is the whole job.

Reports or transactions

Two routes, and the choice matters more here than in most exports.

The reports endpoints return what QuickBooks itself reports — profit and loss, balance sheet, aged receivables — already computed on the basis you request. If the figure needs to match what the accountant sees, use these.

Querying transactions gives you the detail and means you are recomputing something QuickBooks already computes, which is where disagreements come from.

Use reports for anything anybody will compare against the accounts. Use transaction queries for operational work — chasing invoices, checking what is outstanding — where the detail is the point and the total is not.

Do not rebuild a profit and loss from transactions. It can be done and it will not tie out, and the time spent reconciling it is the reason the reports endpoints exist.

Building it with CodeWords

CodeWords connects to more than 3,000 integrations, and the connection is made once and reused.

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody, the automation builder: which report or query, which period and basis, and which sheet it lands in.
3. Authorize the connection to QuickBooks, and grant access to the target spreadsheet.
4. Describe the exceptions: a period that has been closed since the last run, an empty report, a figure outside a plausible range.
5. Run it for a period you already have signed-off figures for and check that it matches before trusting anything.

You describe the outcome; Cody builds it, connects it, and deploys it. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.

Closed periods and adjustments

The behaviour that surprises people who have not worked with accounting data.

History changes. A figure for March can be different in June because an adjustment was posted, and this is normal rather than a data integrity problem.

A sheet holding a snapshot goes stale silently, and the number in it was correct when written.

Re-export closed periods periodically rather than assuming they are settled, or refresh a rolling window that covers the period still being adjusted.

Record the export date alongside the figure, since "revenue for March as at 10 April" is a defensible statement and "revenue for March" is not.

Ask your accountant when periods close and align the schedule, so the export lands after adjustments rather than during them.

Flag when a re-export changes a historical figure, because somebody may have already circulated the old one.

The chart of accounts, which changes

Accounts get renamed, merged, and made inactive, usually during a tidy-up nobody mentions.

Match on account identifiers, not names, since names are exactly what changes.

A renamed account silently changes a grouped total if your export groups by name, and the new group looks like a new line rather than an error.

Report accounts you did not expect, rather than dropping them into an "other" bucket that quietly grows.

Sub-accounts roll up, and whether your export includes the parent, the children, or both is a decision that changes the total.

Validate the account list against the previous run and report the difference, which is a five-line check that catches most of this.

Labelling the sheet so it is defensible

The habit that makes the difference between a trusted report and an argument.

State the basis — accrual or cash — in the sheet, every time.

State the period, with its exact start and end dates rather than a month name.

State the export date and time, so a figure can be reconciled with what the books said then.

Name the report or query that produced it.

Note the currency and, for multi-currency, which rate convention was used.

Five lines at the top of the sheet. They cost nothing and they end most of the disputes this kind of report generates.

What should not leave QuickBooks

Worth a moment, because financial exports travel further than intended.

Customer contact details and addresses arrive with transaction data whether or not anybody needs them.

Employee and payroll information should generally not be in a shared spreadsheet at all.

Bank account details on supplier records are among the more sensitive things in any business system.

Aggregate where you can. A revenue-by-month sheet is shareable in a way that a transaction-level export is not.

Check who the sheet is shared with, since a finance export set to link-sharing is a genuine exposure and access widens over time without review.

Operational reports people actually chase

Beyond the management figures, a few exports save real time each month.

Aged receivables with contact details, so whoever chases invoices has the list and the phone numbers in one place rather than in two systems.

Invoices about to become overdue, which is the version that prevents the problem rather than reporting it.

Unreconciled transactions, which accumulate quietly and are painful to clear in bulk at period end.

Bills due this week, so nothing is paid late for want of somebody remembering to look.

Expenses without receipts attached, which is the query that saves the most argument at year end.

Duplicate supplier invoices, matched on amount and reference, since paying one twice happens in every organisation and is found months later.

None of these needs to tie out to the accounts in the way a management report does, which makes them a good place to start: the stakes are lower and the time saved is immediate.

Making it survive

Use the reports endpoints for anything that must tie out.

Match accounts on identifiers, not names.

Replace a named range rather than appending, so a re-export of an adjusted period corrects rather than duplicates.

Handle the token refresh, which is where these integrations break after a few weeks of working.

Report the outcome. Which report, which period, which basis, and whether any historical figure changed since the last run.

Limits worth knowing about

Rate limits apply, and reports over long periods can be slow to generate.

Tokens expire and must be refreshed, with a refresh flow that needs exercising rather than assuming.

Sandbox and production are separate, with separate credentials, so confirm which one a connection is using.

Report shapes differ between report types, so a parser written for one will not read another.

Sheets has a cell limit, which a transaction-level export over a long period will reach.

What to set up first

One report from the reports endpoints — profit and loss for the current period is the usual choice — with the basis, the period, the export time, and the currency written at the top of the sheet. Check it against a period your accountant has already signed off before circulating it to anybody.

Two habits make the difference. Use the reports endpoints rather than recomputing from transactions, because a figure that does not tie out to the accounts is worse than no figure. And re-export recent closed periods on a schedule, since adjustments change history and a snapshot quietly stops being true.

Frequently asked questions

Why does my export disagree with QuickBooks?

Usually accrual against cash basis, transaction date against entry date, or an adjustment posted into a closed period after you exported. All three are normal, which is why the sheet should state which basis and period it used.

Should I use the reports endpoints or query transactions?

Reports for anything anybody will compare against the accounts, because they return what QuickBooks itself reports. Transaction queries for operational detail, where the individual records are the point rather than the total.

Why did last month's figure change?

An adjustment was posted into the period. Accounting history is not immutable, and this is expected rather than a fault. Re-export recent periods on a schedule and flag when a historical figure moves.

Why did a grouped total change shape?

Probably a renamed or merged account. Match on account identifiers rather than names, and report accounts you did not expect rather than letting them fall into an "other" bucket.

What should the sheet say about itself?

Basis, exact period dates, export timestamp, the report that produced it, and the currency convention. Five lines that cost nothing and settle most of the disputes a finance report attracts.

Is it safe to share a finance export?

Only deliberately. Transaction-level exports carry customer details and sometimes supplier bank details, and a sheet on link-sharing is a real exposure. Aggregate where the reader only needs the summary.

Why does the integration stop working after a few weeks?

Token refresh, almost always. Exercise the refresh path during testing rather than meeting it in production on a Monday morning when somebody needs the report.

What is worth exporting besides the management accounts?

Aged receivables with contact details, invoices about to fall overdue, unreconciled transactions, bills due this week, and expenses missing receipts. None has to tie out the way a management report does, so they are a lower-risk place to start.

How do I catch duplicate supplier invoices?

Match on supplier, amount, and reference across a window and report the pairs. It happens in every organisation, it is normally found months later during a reconciliation, and it is a short query to run weekly.

Related reading

• QuickBooks and CodeWords
• Connecting Xero to Google Sheets
• CodeWords integrations and templates