Back to Blog

How to Export All Data from Xero to Google Sheets and Other Formats

What we love about Xero is that it allows small businesses to monitor their financial health on one dashboard. However, Xero users may have different reasons for exporting their data from this software. Some just need to do this once – for example, if they’re going to migrate to another accounting app such as QuickBooks. Others need to export their data recurrently to build and share reports with stakeholders.

Depending on the purpose, you may choose different ways to pull data from Xero. Below, we’ll explore how you can connect Xero to Google Sheets and other data exporting options that are available.

Why import data from Xero to Google Sheets? 

Xero is a great online accounting solution. With it, you can handle bank reconciliation, prepare payroll, adjust the opening balance, and more. Google Sheets, in turn, enables versatile transformation of raw accounting data to gain insights, such as:

  • build a custom accounts receivable dashboard
  • build an expenses and revenue monitor in a spreadsheet
  • as a backup for Xero
  • set up a table to reconcile invoices and expenses (fact vs. plan)
  • visualize and format data 
  • calculate versatile sales metrics
  • and so on.

You likely have your own grounds to move data from Xero to Google Sheets. Feel free to share them in the comments section. 

Can you export all data from Xero at once?

Unfortunately, there is no Export All button to do the job in one step. So, you’ll have to export data separately from individual areas of Xero such as accounting data, invoices and bills, contacts, etc.

Integrate Xero and Google Sheets for automatic data export

You can automate data export from Xero to Google Sheets. To do this, you’ll need Coupler.io, a solution for importing data from Xero, QuickBooks, Airtable and many other sources. You can install Coupler.io from Google Workspace Marketplace.

After that open your Google Sheets doc, go to Add-ons menu => Coupler.io => Open dashboard and click Add Importer. Choose Xero from the list.

Then you’ll need to set up the Xero importer depending on the data you want to import to Google Sheets.

Set up a Xero importer to connect Xero to Google Sheets

An importer is an integration that connects Google Sheets to a specific app, such as Airtable, Pipedrive or, in our case, Xero. Complete the following steps to set it up:

Title 

Name your importer.

Source

Set up the Source parameters:

  • Source account – choose your Xero account and Company
  • Data entity – select the data category to import. You can choose from a number of options: Accounts, Bank Transactions, Bank Transfers, and others.
  • Report type – pick the report type based on your needs: 
    • A simple report imports default fields. Available for all entities.
    • A detailed report imports an extended list of fields. Available for Invoices, Contacts, Bank Transactions, and Manual Journals.

Click Show Advanced to expand optional parameters for source:

  • Where –  to retrieve a filtered set of elements that don’t have explicit parameters.
  • Order – to return results in ascending or descending order.
  • Split by – to split rows by a specific field that contains multiple values into separate lines. 

Destination

Set up the Destination parameters:

  • Destination account – a Google account to connect Xero to.
  • Sheet name – add the name of the sheet which will be receiving data.

Click Show Advanced to expand an optional parameter – Cell address. This lets you specify the first cell where the Xero data will be imported to.

Settings

In the Settings section, you can enable Automatic data refresh: Customize the schedule for automatic data exports from Xero to Google Sheets. For more about the optional parameters in the Settings section, refer to the Coupler.io knowledge base

Once you’ve set up your Xero importer, click Save or Save & Run to initiate the first data export.

What data you can export from Xero to Google Sheets with Coupler.io

  • Contacts (Customers, Suppliers, Employees)
  • Bank Accounts
  • Bank Transactions (reconciled data only)
  • Bank Transfers
  • Branding Themes
  • Contact Groups
  • Credit Notes
  • Currencies
  • Employees
  • Expense Claims
  • Invoices
  • Items (Products and services)
  • Journals
  • Manual Journals
  • Organisation
  • Overpayments
  • Payments
  • Prepayments
  • Purchase Orders
  • Receipts
  • Repeating Invoices
  • Tax Rates
  • Tracking Categories
  • Users

Manual Xero data export 

If you don’t need to automate your data export for Xero to Google Sheets, you can make do with the native Xero export options. As a rule, you should do the following to export data from Xero:

  • Select the data entity you want to export
  • Click the export button

After that, a CSV, TXT, PDF or MYE file will be downloaded to your device. Here is how it looks in the example of Xero invoices export:

Data and its formats for export from Xero

DataFormat
Accounting dataCSV, MYE, TXT
Bills to pay (Purchases overview)CSV
Batch paymentsPDF
BudgetCSV
Chart of accountsCSV, PDF
ChequesCSV
Contacts (Customers, Suppliers, Employees)CSV
Fixed assetsCSV
Invoices (Sales overview)CSV
Items (Products and services)CSV, PDF

How to export accounting data from Xero

In brief, to export specific accounting data, you need to go to the Accounting menu, choose the data entity you need, and find the Export button. Check this out in the example of Fixed Assets. 

How to export fixed assets from Xero

Go to Accounting => Fixed Assets.

Next, click the Export button and welcome a CSV file with your Xero fixed assets data.

Export data from Xero to other accounting software

Xero allows you to export certain accounting data in the format importable for another accounting software, such as MYOB Accountants Office or uTax. Here is the table with the accounting data available for export, software you can import it to, and the export file format:

Accounting dataSupported softwareExport file format
General LedgerAccountants Enterprise (MAS)
BGL Simple Fund
CaseWare
MYOB Accountants Office
Sage HandiLedger
Xero General Ledger
MYE
CSV
TXT
MYE
CSV
TXT
General Ledger and Chart of AccountsAPS Accounting (XPA)TXT 
Income Statement and Balance SheetCrossLink
uTax
CSV
CSV
Trial BalanceCaseWare
CCH Accounts Production
CCH Prosystem fx Engagement
Forbes Accounts
Keytime
Lacerte Trial Balance Utility/SmartMap
TaxACT
TaxCalc
Thomson Reuters Accounting CS
Thomson Reuters Workpapers CS
CSV
CSV
CSV
CSV
CSV

CSV
CSV
CSV
XLSX
XLSX
Trial Balance and General LedgerIRISCSV

Let’s see how it works in the example of General Ledger and Chart of Accounts for APS Accounting (XPA).

How to export a chart of accounts and general ledger from Xero

First, go to Accounting => Advanced.

Find and click on Export accounting data.

In the open window, select the product to export data for. In our case, this is APS Accounting (XPA).

After that, you’ll be able to select the data range for the chosen accounting data and download two TXT files: one for Charts of Accounts, and one for General Ledger.

Then you can import these files to APS Accounting (XPA).

How to export a Balance Sheet from Xero

You may think that the export of other accounting data looks similar. Not exactly. For example, Balance Sheet is a report, which means you have more exporting options: Google Sheets, Excel, and PDF. Go to Accounting => Balance Sheet and then click the Export button.

For more about this, jump to the How to export reports section. 

How to export business data from Xero

Xero business data includes invoices, bills, cash flow, etc. Let’s discover the exporting options available.

How to export invoices and bills from Xero

Go to Business => Invoices.

Click the Export button and you’ll get a CSV file with your invoices data downloaded to your device.

You’ll need to repeat the flow to export bills as well. 

How to export Xero invoices and bills to Google Sheets

With Coupler.io, you can export invoices and bills in one Google Sheets file (and even automate recurring data export on a schedule). To do this, select Invoices in the Data Entity field and run the Xero importer.

The structure of the data exported to Google Sheets will differ from the structure of the manually exported data. However, you’ll have information about both invoices (ACCREC) and bills (ACCPAY) in one file.

How to export a batch payments file from Xero

A batch deposit allows you to bundle multiple bills or invoices into one payment transaction. It’s quite useful if you’re going to bank cash and cheques for multiple invoices as one deposit. You cannot include more than 50 invoices in one batch payments file. Do the following to create and export a batch payments file from Xero:

Go to Business => Invoices and click Awaiting Payment.

Select the invoices you want to include in the batch deposit, and click Deposit.

In the next window, fill out the Reference field, and choose the Payment Date and Bank Account. Click Deposit.

After that, you can download the batch payments data as a PDF or CSV file.

How to export cash flow from Xero

In the Business menu, you can use “Short-term cash flow” to get a snapshot of cash coming in or bills due. However, you can’t export this data. Instead, you can benefit from cash flow reports provided by Xero:

  • Statement of Cash Flows 
  • Cash Summary 

Go to Accounting => Reports and select the cash flow report you need. After that, you can export the data to Excel or Google Sheets, or export it as a PDF.

How to export purchase orders from Xero with payments

If you go to Business => Purchase orders, you’ll see your orders with payments, but there is no export button.

The only export option here is to use Coupler.io and its Xero importer. Select “Purchase Orders” as the Data entity and run the importer to export the data to Google Sheets.

How to export a budget from Xero

You can export different budget data from Xero:

  • Overall Budget (from Budget Manager)
  • Budget Summary
  • Budget Variance

All these are reports in the Accounting menu.

You can export them by clicking the Export button.

How to export a budget from Xero to Google Sheets

With Coupler.io, you can also automate export of Budget Summary reports to Google Sheets. To do this, use the Xero Reports importer and select the corresponding report.

How to export inventory items from Xero

You can find information about Xero inventory in Business => Products and Services.

Click the Export button and choose either CSV or PDF format to download to your device.

How to export inventory items from Xero to Google Sheets

Again, Coupler.io is what you need to retrieve data about inventory items to Google Sheets on a timely basis. Set up a Xero importer and choose “Items” as a Data entity. Run the importer and welcome your Xero data in Google Sheets.

How to export contacts from Xero?

Contacts in Xero include Customers, Suppliers and Employees. To export all contacts, go to Contacts => All contacts.

Click the Export button, and a CSV file with this data will be downloaded to your device.

How to export contacts from Xero to Google Sheets

Xero importer by Coupler.io lets you automate export of Contacts, Groups of Contacts, Employees and Users from Xero to Google Sheets. To do this, select the corresponding type of data in the “Data entity” field and run or schedule the importer. 

Can you export a client list from Xero to Google Sheets?

If you want to only export customers from Xero, you only need to select Customers, and click the Export button.

The downloaded CSV file will only contain data about clients. In a similar way, you can export Suppliers, Employees and other groups of contacts.

How to export reports from Xero to Google Sheets manually

Xero provides you with a variety of reports that you can export in different formats (Excel, Google Sheets and PDF) depending on the type of report.

For example, here is how you can export Movements in Equity (New) to Google Sheets:

Go to Accounting => Reports.

Select Movements in Equity (New).

Select the date range and comparison period(s) to generate the report and click Export.

Let’s export this Xero report to Google Sheets. Xero will ask you to choose a Google Account to sign in and provide access to the chosen account.

Once you click Allow, your Movements in Equity report will be exported to Google Sheets.

Xero reports and formats for export

Take a look at the list of reports that you can export from Xero.

Financial 

ReportFormat
Balance SheetExcel, Google Sheets, PDF
Balance Sheet (New)Excel, Google Sheets, PDF
Budget ManagerCSV, Excel, Google Sheets
Budget SummaryExcel, Google Sheets, PDF
Budget VarianceExcel, Google Sheets, PDF
Business SnapshotPDF
Cash SummaryExcel, Google Sheets, PDF
Executive SummaryExcel, Google Sheets, PDF
Movements in equityExcel, Google Sheets, PDF
Movements in equity (New)Excel, Google Sheets, PDF
Profit and Loss Excel, Google Sheets, PDF
Profit and Loss (New)Excel, Google Sheets, PDF
Statement of Cash FlowsExcel, Google Sheets, PDF
Tracking SummaryExcel, Google Sheets, PDF

Tax 

ReportFormat
Sales TaxExcel, Google Sheets, PDF

Accounting 

ReportFormat
Account SummaryExcel, Google Sheets, PDF
Account TransactionsExcel, Google Sheets, PDF
Account Transactions (New)Excel, Google Sheets, PDF
Annual Accounts – ExemptExcel, Google Sheets, PDF
Bank Reconciliation SummaryExcel, Google Sheets, PDF
Bank SummaryExcel, Google Sheets, PDF
Blank Report (New)Excel, Google Sheets, PDF
Cash Validation Customer Report (New)Excel, Google Sheets, PDF
Contact Transactions – Summary (New)Excel, Google Sheets, PDF
Detailed Account TransactionsExcel, Google Sheets, PDF
Duplicate Statement Lines (New)Excel, Google Sheets, PDF
Foreign Currency Gains and LossesExcel, Google Sheets, PDF
General LedgerExcel, Google Sheets, PDF
Journal ReportExcel, Google Sheets, PDF
Management ReportExcel, Google Sheets, PDF
Reconciliation ReportsExcel, Google Sheets, PDF
Trial BalanceExcel, Google Sheets, PDF
Trial Balance (New)Excel, Google Sheets, PDF
Uncoded Statement LinesPDF, CSV

Fixed assets

ReportFormat
Deprecation ScheduleExcel, Google Sheets, PDF
Deprecation Schedule (New)Excel, Google Sheets, PDF
Disposal ScheduleExcel, Google Sheets, PDF
Fixed Asset ReconciliationExcel, Google Sheets, PDF
Fixed Asset Reconciliation (New)Excel, Google Sheets, PDF

Sales 

ReportFormat
Aged ReceivablesExcel, Google Sheets, PDF
Aged Receivables Detail (New)Excel, Google Sheets, PDF
Aged Receivables Summary (New)Excel, Google Sheets, PDF
Customer Invoice ReportExcel, Google Sheets, PDF
Income by ContactExcel, Google Sheets, PDF
Receivable Invoice Detail (New)Excel, Google Sheets, PDF
Receivable Invoice Summary (New)Excel, Google Sheets, PDF

Purchases

ReportFormat
Aged PayablesExcel, Google Sheets, PDF
Aged Payables Detail (New)Excel, Google Sheets, PDF
Aged Payables Summary (New)Excel, Google Sheets, PDF
Billable Expenses – Outstanding (New)Excel, Google Sheets, PDF
Expense Claim Detail (New)Excel, Google Sheets, PDF
Expenses by ContactExcel, Google Sheets, PDF
Payable Invoice Detail (New)Excel, Google Sheets, PDF
Payable Invoice Summary (New)Excel, Google Sheets, PDF
Supplier Invoice ReportExcel, Google Sheets, PDF

Inventory

ReportFormat
Inventory Item Details (New)Excel, Google Sheets, PDF
Inventory Item List (New)Excel, Google Sheets, PDF
Inventory Item Summary (New)Excel, Google Sheets, PDF
Inventory Items SummaryExcel, Google Sheets, PDF
Sales By ItemExcel, Google Sheets, PDF

Payrun

ReportFormat
Pay Run by EmployeeExcel, Google Sheets, PDF
Pay Run by Pay ItemExcel, Google Sheets, PDF
Pay Run by Pay TypeExcel, Google Sheets, PDF
Pay Run SummaryExcel, Google Sheets, PDF

Projects

ReportFormat
Detailed Time (New)Excel, Google Sheets, PDF
Project Details (New)Excel, Google Sheets, PDF
Project Summary (New)Excel, Google Sheets, PDF

Here are a few examples of how Xero reports export works.

How to export transactions from Xero

You can find all transactions from a single account or multiple accounts in the Account Transactions report. You need to choose the account(s), as well as the data range, and click Update. Once the report is generated on your requirements, you can export it to Excel, Google Sheets, or as a PDF file.

How to export expenses from Xero to Excel or Google Sheets

In Xero, you can find data about expenses in three different reports:

  • Billable Expenses – Outstanding (New)
  • Expense Claim Detail (New)
  • Expenses by Contact

Choose the report you need and export is by clicking the Export button.

How to automatically export reports from Xero to Google Sheets

Coupler.io also provides a Xero Reports importer to automate export of reports from Xero to Google Sheets.

All you need to do is set it up as we did with the Xero importer. The only difference is the Source section, where you need to specify two parameters:

  • Report Name – pick the type of Xero report to import.
  • Report Parameters – specify the parameters for the report type you’ve picked above. For multiple parameters, add each of them in a new line. 

You can leave this field blank for the following reports: Balance Sheet, Bank Summary, Budget Summary, Executive Summary, Profit and Loss, and Trial Balance. In this case, the imported data will be as of the date of the import.

Xero Reports importer supports the following types of Xero reports:

  • Balance Sheet 
  • Profit and Loss 
  • Budget Summary 
  • Executive Summary
  • Bank Statements 
  • Bank Summary
  • Trial Balance
  • Aged Receivables (by Contact)
  • Aged Payables (by Contact)

Report Parameters

You can type the parameter values both with and without double quotation marks (""). For example, date: "2020-01-01" and date: 2020-01-01 will do.

Parameters for Aged Payables by Contact

Aged Payables in Xero

Aged Payables by Contact imported into Google Sheets

Required parameters

  • contactID: "Xero-contactID"

Example of what you must have in the Report Parameters field: 

contactID: "c01292e3-1a1a-4a70-b120-1218f8f71096"

How to get Xero-contactID

  1. Go to Contacts => All Contacts in the drop-down menu.
  2. Find and click on the contact you need to filter aged receivables data by.
  3. Copy the Xero-contactID from the URL.

Optional parameters

  • date: "YYYY-MM-DD"

Imports payables up to the specified date. The default value is the end of the current month.

  • fromDate: "YYYY-MM-DD"

Imports payables from the specified date.

  • toDate: "YYYY-MM-DD" 

Imports payables to the specified date.

Example of what you can have in the Report Parameters field: 

contactID: "c01292e3-1a1a-4a70-b120-1218f8f7109"
date: "2020-03-28"
fromDate: "2020-01-01"

Parameters for Aged Receivables by Contact

Aged Receivables in Xero

Aged Receivables by Contact imported into Google Sheets 

Required parameters

  • contactID: "Xero-contactID"

Example of what you must have in the Report Parameters field: 

contactID: "c01292e3-1a1a-4a70-b120-1218f8f71096"

How to get Xero-contactID

  1. Go to Contacts => All Contacts in the drop-down menu.
  2. Find and click on the contact you need to filter aged receivables data by.
  3. Copy the Xero-contactID from the URL.

Optional parameters

  • date: "YYYY-MM-DD"

Imports receivables up to the specified date. The default value is the end of the current month.

  • fromDate: "YYYY-MM-DD"

Imports receivables from the specified date.

  • toDate: "YYYY-MM-DD" 

Imports receivables to the specified date.

Example of what you can have in the Report Parameters field: 

contactID: "c01292e3-1a1a-4a70-b120-1218f8f7109"
date: "2020-03-28"
fromDate: "2020-01-01"

Parameters for Balance Sheet

Balance Sheet in Xero

Balance Sheet imported into Google Sheets 

Required parameters

No

Optional parameters

  • date: "YYYY-MM-DD"

Imports the balance sheet up to the specified date. The default value is the date of import.

  • trackingOptionID1: "insert-tracking-option-ID" 

Imports the balance sheet filtered by Region.

How to get tracking-option-ID

  1. Go to Accounting => Reports => More reports => Balance Sheet

Do not confuse the new version of Balance Sheet with the older one that we need. 

  1. Click on More options and filter the Balance Sheet by region you need.
  2. Click on Update and find TC1=**************** in the page URL. For example, TC1=5e2974a2-097d-4f3b-bfd5-605d78c4a282
  3. Copy the ID part, 5e2974a2-097d-4f3b-bfd5-605d78c4a282, and replace the tracking-option-ID with it. 

  • trackingOptionID2: "insert-tracking-option-ID" 

Imports the balance sheet filtered by another tracking option. 

For more about Tracking Options, check out Xero official documentation.

  • standardLayout: "true/false" 

If true, custom report layouts will not be imported.

  • paymentsOnly: "true/false"

If true, only cash transactions will be imported.

Parameters for Bank Statement

Bank Statements in Xero

Bank Statement imported into Google Sheets 

Required parameters

  • bankAccountID: "insert-bank-account-ID"

Insert the bank account ID to import Bank Statements. For example: 

bankAccountID: "562555F2-8CDE-4CE9-8203-0363922537A4"

How to get bankAccountID

  1. Go to Business Bank Account => Bank Statements
  2. Find accountID=**************** in the page URL. For example, accountID=562555F2-8CDE-4CE9-8203-0363922537A4
  3. Copy the ID part, 562555F2-8CDE-4CE9-8203-0363922537A4, and replace the insert-bank-account-ID with it. 

Optional parameters

  • fromDate: "YYYY-MM-DD"

Imports the bank statement from the specified date.

  • toDate: "YYYY-MM-DD" 

Imports the bank statement to the specified date.

Parameters for Bank Summary

Bank Summary in Xero

Bank Summary imported into Google Sheets 

Required parameters

No

Optional parameters

  • fromDate: "YYYY-MM-DD"

Imports the balances and cash movements for each bank account from the specified date.

  • toDate: "YYYY-MM-DD" 

Imports the balances and cash movements for each bank account to the specified date.

Parameters for Budget Summary

Budget Summary in Xero

Budget Summary imported into Google Sheets  

Required parameters

No

Optional parameters

  • date: "YYYY-MM-DD"

Imports the budget summary FROM the specified date. The default value is the current month.

  • periods: "insert-integer" 

Imports the budget summary for the number of periods to compare. Insert an integer between 1 and 12.

  • timeframe: "period-size"

Specifies the size of the period used in the periods parameter: 

  • 1 – month
  • 3 – quarter
  • 12 – year

Parameters for Executive Summary

Executive Summary in Xero

Executive Summary imported into Google Sheets  

Required parameters

No

Optional parameters

  • date: "YYYY-MM-DD"

Imports the executive summary (monthly totals and some common business ratios) up to the specified date. The default value is the date of import.

Parameters for Profit and Loss

Profit and Loss in Xero

Profit and Loss imported into Google Sheets  

Required parameters

No

Optional parameters

  • fromDate: "YYYY-MM-DD"

Imports the P&L data from the specified date.

  • toDate: "YYYY-MM-DD" 

Imports the P&L data to the specified date.

  • periods: "insert-integer" 

Imports the P&L data for the specified number of periods to compare. Insert an integer between 1 and 11.

  • timeframe: "period-size"

Specifies the size of the period used in the periods parameter. Available values are MONTH, QUARTER, and YEAR.

Note: periods and timeframe work if used together. Here is how the P&L report imported into Google Sheets will look with the following parameters:

periods: 4
timeframe: MONTH

  • trackingCategoryID: "insert-tracking-category-ID" 

Imports Profit and Loss report filtered by a tracking category. A Xero organisation can have a maximum of two ACTIVE tracking categories. To learn yours, as well as their IDs and tracking options, import them with the Xero importer (NOT Xero Reports). Use the following parameters:

Data Entity: Tracking Categories
Report type: Simple
Split By: Options

Here is the trackingCategoryID that you can use as a parameter value. In our case, this is the filter by Region with four tracking options: Eastside, North, South, and West Coast. 

  • trackingOptionID: "insert-tracking-option-ID" 

Use the tracking option imported along with trackingCategoryID.

  • trackingCategoryID2: "insert-tracking-category-ID2" 

Use the second tracking category if available.

  • trackingOptionID2: "insert-tracking-option-ID2" 

Use the second tracking option. 

  • standardLayout: "true/false" 

If true, custom report layouts will not be imported.

  • paymentsOnly: "true/false"

If true, only cash transactions will be imported.

Parameters for Trial Balance

Trial Balance in Xero

Trial Balance imported into Google Sheets  

Required parameters

No

Optional parameters

  • date: "YYYY-MM-DD"

Imports the trial balance for the current month up to the specified date. The default value is the date of import.

  • paymentsOnly: "true/false"

If true, only cash transactions will be imported.

Do you export data from QuickBooks?

To sum up this guide, we’d like to ask you whether you also need to export data from QuickBooks? If you do, you should check out how you can connect QuickBooks to Google Sheets using Coupler.io. With this tool in your toolbox, you’ll be able to significantly optimize your workflow. Good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free