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

To connect Xero to Google Sheets with the tax treatment intact, export at line level and include three things: tracking categories, tax amounts, and reconciliation status. Dropping any of them produces a spreadsheet that looks complete and answers the question wrongly. Tracking categories fail most often, because they attach to the invoice line, not to the transaction.

Key takeaways

• Export at line level, because tracking categories, tax rates and amounts belong to lines, not invoice headers.
• Include the line amount, the tax amount and the tax setting as separate columns, and label which figure is which.
• Add reconciliation status as a column so readers can separate bank-confirmed money from recorded money.
• Store the rotating refresh token somewhere the job can write to, or the connection breaks.
• Export the source amount, the base amount and the currency, and never convert currencies yourself.

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

Three things make a Xero export useful: tracking categories, tax rates and reconciliation status. Each is a column somebody will eventually ask for, and each is more awkward to add later than to include at the start.

Tracking categories split figures by department, location or project. Without them the export answers "how much" and not "how much for what".

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

Reconciliation status separates what the bank has confirmed from what has only been recorded.

Tracking categories live on the line

Tracking categories attach to each line item, not to the invoice, so a Xero export must run at line level to keep them. One invoice can span several departments, and the header total belongs to none of them.

An organisation can have up to two active categories, each with its own option list, or one, or none.

Options get renamed and archived, so match on identifiers, not option names.

Unassigned lines are normal. Report the count of them instead of hiding them, because an "unassigned" bucket that quietly grows is a bookkeeping problem.

Report the split by category. That is almost always the report the requester wanted, and a header-level export cannot show it.

Building it with CodeWords

CodeWords connects to 3,000+ integrations (CodeWords pricing page, 2026), and each 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 (lines or invoices), 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 (CodeWords pricing page, 2026). Details are on the pricing page.

Tax rates and what a total includes

A Xero total includes tax only if the invoice is set to tax inclusive, so export the line amount, the tax amount and the setting together. Line amounts can be tax inclusive, tax exclusive or no tax, set per invoice, and the same number means different things under each.

Tax rates are per line. An invoice can mix rates, so a single tax column on the header loses information.

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

Rates change, including their names. Key on the rate's identifier and export its effective percentage as well as its label.

Match your accountant's basis. Ask which figure they work from before choosing the sheet's headline column.

Reconciled or not, which changes the meaning

A recorded transaction is not confirmed money: reconciliation against the bank confirms it. Include reconciliation status as a column, always, so the reader can filter instead of receiving one interpretation.

An export that ignores status mixes confirmed and unconfirmed figures. That suits some questions and is wrong for cash reporting.

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

Bank transactions and invoices are different objects in Xero. Reconciliation links them, so an export of one says nothing about the other.

Tokens that expire in thirty minutes

Xero access tokens are short-lived, so a scheduled job must refresh them instead of storing one. The practical risk is the refresh token, which rotates and breaks the connection if the new one is not saved.

Refresh tokens rotate. Each refresh issues a new refresh token and invalidates the previous one, so store the new one immediately.

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 the job cannot update.

Handle the race. Two runs refreshing at once can invalidate each other, producing an intermittent failure with no obvious cause.

Alert on refresh failure specifically. The fix is reconnecting the app, and a generic error does not tell anyone that.

Multi-currency and the rate that was used

Xero stores each transaction in its own currency, converted to the base currency at the rate applied when it was entered. Export both the source amount and the base amount, plus the currency, so the reader can see which they are looking at.

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

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

Name the currency of the totals in the sheet. A mixed-currency export with an unlabelled total column misleads.

Several organisations, one report

One report can cover several Xero organisations if each has its own connection and a visible mapping to a common chart of accounts. Teams running more than one entity meet this early.

Each organisation needs its own authorisation, so a report spanning three entities needs three.

Charts of accounts differ, so map each organisation's accounts to a common set. Keep the mapping in a sheet of its own, so whoever owns the accounts can maintain it.

Report unmapped accounts instead of dropping them, or a new account in one entity disappears from the combined figures.

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

Base currencies may differ, so state the conversion basis for a combined total.

Most teams keep one sheet per organisation and combine them by hand. That recurring manual task is the one worth removing.

Making it survive

Five practices keep the export trustworthy.

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

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

Replace a named range instead of 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 failed token refresh.

Limits worth knowing about

Five limits apply to a Xero-to-Sheets export.

Rate limits apply per organisation and per application, so a full history export needs pacing. Check the current figures in Xero's developer documentation.

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

Short-lived access tokens and rotating refresh tokens are the most common cause of a scheduled Xero job failing.

Google Sheets allows 20 million cells per spreadsheet (Google Docs Editors Help, 2026), which a multi-year line-level export can reach.

Report endpoints differ from transaction queries. Report endpoints return what Xero itself reports, so use them when figures must match the accounts.

What to set up first

Set up a line-level export of the current period with tracking categories, tax amounts and reconciliation status as columns, and state the currency and the grain at the top of the sheet. People come back and ask for exactly those columns, and adding them later means re-exporting everything.

Two habits make the difference. Export at line level from the start, since a header-level export cannot recover tracking categories. And store the rotating refresh token somewhere the job can write to, because that single detail is why many scheduled Xero integrations stop working.

Frequently asked questions

Why can I not see tracking categories in my export?

Tracking categories attach to line items, not to the invoice header, so a header-level export drops them. Export at line level and match on the option identifiers instead of names, since options get renamed and archived.

Why does my total not match Xero's?

The usual causes are tax treatment, meaning whether line amounts are inclusive or exclusive, and a mix of reconciled and unreconciled transactions. Export the components as separate columns and state which figure is the sheet's headline column.

Why does the integration stop working after a few weeks?

The cause is usually token refresh. Xero access tokens are short-lived and refresh tokens rotate on each use, so the new token 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, not 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 figure that will not reconcile with the accounts.

What should the sheet state about itself?

The sheet should state the grain (lines or invoices), the currency, the period, the tax basis, and whether unreconciled items are included. Each is a decision, not a fact, and each is a question a reader will ask when the numbers look wrong.

What is a useful weekly report from Xero?

Two weekly reports help most: unreconciled items by age, and lines with no tracking category assigned. Both accumulate quietly, both are painful to clear in bulk later, and nobody produces either by hand.

Report endpoints or transaction queries?

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

Can one report cover several Xero organisations?

Yes. Use a separate connection per organisation and a visible mapping from each 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?

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

How do I avoid hitting the API rate limit?

Export incrementally on a modified-since basis instead of pulling full history each run, and space multi-organisation exports apart. Run a full historical backfill once, paced deliberately, not as a nightly job.

Related reading

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