How to connect Xero to Google Sheets with the tax treatment intact

Xero's export problem is not getting the transactions out. It is that an invoice line carries a tax rate, possibly a tracking category, and a reconciliation state, and dropping any of the three produces a spreadsheet that looks complete and answers the question wrongly.

Tracking categories in particular are the thing people most often want from Xero and most often fail to export, because they are not columns on the transaction — they are attached to the line.

What we'll cover

• Three things that make it useful
• Tracking categories live on the line
• Building it with CodeWords
• Tax rates and what a total includes
• Reconciled or not, which changes the meaning
• Tokens that expire in thirty minutes
• Multi-currency and the rate that was used
• Several organisations, one report
• Making it survive
• Limits worth knowing about
• What to set up first
• Frequently asked questions

Three things that make it useful

Tracking categories, which are how a Xero organisation splits figures by department, location, or project. Without them the export answers "how much" and not "how much for what".

Tax rates, which determine whether a figure is inclusive or exclusive and which vary by line rather than by invoice.

Reconciliation status, which distinguishes what the bank has confirmed from what has merely been recorded.

Each is a column somebody will eventually ask for, each requires a deliberate decision when exporting, and each is more awkward to retrofit than to include at the start.

Tracking categories live on the line

The detail that makes or breaks a Xero export.

They are per line item, not per invoice, so one invoice can span several departments and the header total belongs to none of them.

There are up to two active categories, with their own option lists, and an organisation may use one, two, or neither.

Line-level export is required to preserve them, which forces the grain decision below.

Options get renamed and archived, so matching on option names is fragile and the identifiers are what to key on.

Unassigned lines are normal, and how you present them matters — an "unassigned" bucket that quietly grows is a bookkeeping problem worth reporting rather than hiding.

Report the split by category, because that is almost always the report the person asking actually wanted and it is invisible in a header-level export.

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 records, which period, which grain, and which sheet.
3. Authorize the connection to Xero for the specific organisation, plus access to the target spreadsheet.
4. Describe the exceptions: a token that needs refreshing, a line with no tracking category, an invoice voided since the last run.
5. Run it for a period your bookkeeper has already reviewed and check the totals match.

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.

Tax rates and what a total includes

Line amounts can be tax inclusive, tax exclusive, or no tax, set per invoice, and the same numeric total means different things under each.

Tax rates are per line, so an invoice can mix rates and a single tax column on the header is a simplification that loses information.

Export the line amount, the tax amount, and the setting, and let the sheet's reader see all three rather than picking one.

State in the sheet which figure is which. "Total" is ambiguous and somebody will assume the one that suits their question.

Rates change, including their names, so key on the rate's identifier and export its effective percentage rather than only its label.

Reporting to an accountant means matching their basis. Ask which figure they work from before deciding what the sheet's headline column is.

Reconciled or not, which changes the meaning

A recorded transaction is not confirmed money. Reconciliation against the bank is what confirms it.

An export that ignores reconciliation status mixes confirmed and unconfirmed figures, which is fine for some questions and wrong for cash reporting.

Include the status as a column, always, so the reader can filter rather than being handed one interpretation.

Unreconciled items accumulate, and a count of them by age is a genuinely useful weekly report that nobody produces by hand.

Bank transactions and invoices are different objects in Xero, and the relationship between them is what reconciliation establishes — an export of one does not tell you about the other.

Tokens that expire in thirty minutes

The practical detail that trips up every first Xero integration.

Access tokens are short-lived, so any scheduled job must refresh rather than store one.

Refresh tokens rotate. Each refresh issues a new refresh token and invalidates the previous one, so the new one must be stored immediately or the connection is lost.

A refresh token also expires if unused for long enough, which is how a monthly report breaks after a quiet period.

Store tokens somewhere durable and writable, not in configuration that the job cannot update.

Handle the race. Two runs refreshing at once can invalidate each other, which produces an intermittent failure that looks like nothing in particular.

Alert on refresh failure specifically, since the fix is reconnecting the app and nobody will know to do that from a generic error.

Multi-currency and the rate that was used

Transactions are stored in their own currency with a conversion to the base currency at the rate applied when entered.

Today's rate is not the historical rate, so recomputing a converted total gives a different answer from Xero's.

Export both the source amount and the base amount, plus the currency, so the reader can see which they are looking at.

Do not convert yourself for anything that needs to match the books, because Xero's figure is the one the accounts use.

Say which currency the totals are in, in the sheet, since a mixed-currency export with an unlabelled total column is actively misleading.

Several organisations, one report

Anybody running more than one entity in Xero meets this, and it is worth designing for early.

Each organisation is a separate connection with its own consent, so a report spanning three entities needs three authorisations.

Charts of accounts differ between them, often substantially, so combining figures means a mapping from each organisation's accounts to a common set.

Keep the mapping visible, in a sheet of its own rather than buried in the automation, so whoever owns the accounts can maintain it.

Report unmapped accounts rather than dropping them, because a new account in one entity otherwise disappears from the combined figures.

Label every row with its organisation, so a combined sheet can always be split back apart.

Base currencies may differ, which means a combined total needs a stated conversion basis or it means nothing at all.

The alternative — one sheet per organisation and a person combining them — is what most teams do and it is exactly the recurring manual task worth removing.

Making it survive

Export at line level where tracking or tax detail matters, and say in the sheet that the grain is lines rather than invoices.

Match tracking options and tax rates on identifiers, not names.

Replace a named range rather than appending, since invoices are edited and voided after the fact.

Re-export recent periods, because amendments change history.

Report the outcome. Records exported, lines written, unassigned lines, and any token refresh that failed.

Limits worth knowing about

Rate limits apply per organisation and per application, including a daily ceiling, so a full history export needs pacing.

Page sizes are fixed, so large exports mean many requests against that ceiling.

Access tokens are short-lived and refresh tokens rotate, which is the most common cause of a scheduled Xero job failing.

Sheets has a cell limit, which a multi-year line-level export will reach.

Some reports are available as report endpoints and returning them is different from assembling the same figures from transactions — the endpoints are what match the accounts.

What to set up first

A line-level export of the current period with tracking categories, tax amounts, and reconciliation status as columns, plus the currency and the grain stated at the top. Those three columns are what people come back and ask for, and adding them later means re-exporting everything.

Two habits make the difference. Export at line level from the start, since tracking categories cannot be recovered from a header-level export and that is the report people actually want. And store the rotating refresh token somewhere the job can write to, because that single detail is why most scheduled Xero integrations stop working within a month.

Frequently asked questions

Why can I not see tracking categories in my export?

Because they are attached to line items rather than to the invoice header. Export at line level, and match on the option identifiers rather than names, since options get renamed and archived.

Why does my total not match Xero's?

Usually tax treatment — whether line amounts are inclusive or exclusive — or mixing reconciled and unreconciled transactions. Export the components and state which figure the sheet's headline column is.

Why does the integration stop working after a few weeks?

Token refresh. Xero access tokens are short-lived and refresh tokens rotate on each use, so the new one must be stored immediately. A refresh token also expires if unused, which breaks monthly reports after a quiet period.

Should I convert currencies myself?

No, for anything that must match the books. Xero stores the conversion at the rate applied when the transaction was entered, and recomputing at today's rate produces a different and unreconcilable figure.

What should the sheet state about itself?

The grain (lines or invoices), the currency, the period, the tax basis, and whether unreconciled items are included. Each is a decision rather than a fact, and each is a question somebody will ask.

What is a useful weekly report from Xero?

Unreconciled items by age, and lines with no tracking category assigned. Both accumulate quietly, both are painful to clear in bulk later, and neither is produced by anybody by hand.

Report endpoints or transaction queries?

Report endpoints for anything that must match the accounts, since they return what Xero itself reports. Transaction queries for operational detail, where the individual records matter more than the total.

Can one report cover several Xero organisations?

Yes, with a separate connection per organisation and a visible mapping from each one's chart of accounts to a common set. Label every row with its organisation, report unmapped accounts, and state the conversion basis if base currencies differ.

Where should the account mapping live?

In a sheet of its own, not inside the automation, so whoever owns the accounts can maintain it without asking anybody. A mapping nobody can see is a mapping nobody updates when a new account appears.

How do I avoid hitting the daily API ceiling?

Export incrementally on a modified-since basis rather than pulling full history each run, and space multi-organisation exports rather than running them together. A full historical backfill should be a one-off, paced deliberately, not a nightly job.

Related reading

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