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
| Data | Format |
|---|---|
| Accounting data | CSV, MYE, TXT |
| Bills to pay (Purchases overview) | CSV |
| Batch payments | |
| Budget | CSV |
| Chart of accounts | CSV, PDF |
| Cheques | CSV |
| Contacts (Customers, Suppliers, Employees) | CSV |
| Fixed assets | CSV |
| 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 data | Supported software | Export file format |
|---|---|---|
| General Ledger | Accountants 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 Accounts | APS Accounting (XPA) | TXT |
| Income Statement and Balance Sheet | CrossLink uTax | CSV CSV |
| Trial Balance | CaseWare 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 Ledger | IRIS | CSV |
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
| Report | Format |
|---|---|
| Balance Sheet | Excel, Google Sheets, PDF |
| Balance Sheet (New) | Excel, Google Sheets, PDF |
| Budget Manager | CSV, Excel, Google Sheets |
| Budget Summary | Excel, Google Sheets, PDF |
| Budget Variance | Excel, Google Sheets, PDF |
| Business Snapshot | |
| Cash Summary | Excel, Google Sheets, PDF |
| Executive Summary | Excel, Google Sheets, PDF |
| Movements in equity | Excel, 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 Flows | Excel, Google Sheets, PDF |
| Tracking Summary | Excel, Google Sheets, PDF |
Tax
| Report | Format |
|---|---|
| Sales Tax | Excel, Google Sheets, PDF |
Accounting
| Report | Format |
|---|---|
| Account Summary | Excel, Google Sheets, PDF |
| Account Transactions | Excel, Google Sheets, PDF |
| Account Transactions (New) | Excel, Google Sheets, PDF |
| Annual Accounts – Exempt | Excel, Google Sheets, PDF |
| Bank Reconciliation Summary | Excel, Google Sheets, PDF |
| Bank Summary | Excel, 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 Transactions | Excel, Google Sheets, PDF |
| Duplicate Statement Lines (New) | Excel, Google Sheets, PDF |
| Foreign Currency Gains and Losses | Excel, Google Sheets, PDF |
| General Ledger | Excel, Google Sheets, PDF |
| Journal Report | Excel, Google Sheets, PDF |
| Management Report | Excel, Google Sheets, PDF |
| Reconciliation Reports | Excel, Google Sheets, PDF |
| Trial Balance | Excel, Google Sheets, PDF |
| Trial Balance (New) | Excel, Google Sheets, PDF |
| Uncoded Statement Lines | PDF, CSV |
Fixed assets
| Report | Format |
|---|---|
| Deprecation Schedule | Excel, Google Sheets, PDF |
| Deprecation Schedule (New) | Excel, Google Sheets, PDF |
| Disposal Schedule | Excel, Google Sheets, PDF |
| Fixed Asset Reconciliation | Excel, Google Sheets, PDF |
| Fixed Asset Reconciliation (New) | Excel, Google Sheets, PDF |
Sales
| Report | Format |
|---|---|
| Aged Receivables | Excel, Google Sheets, PDF |
| Aged Receivables Detail (New) | Excel, Google Sheets, PDF |
| Aged Receivables Summary (New) | Excel, Google Sheets, PDF |
| Customer Invoice Report | Excel, Google Sheets, PDF |
| Income by Contact | Excel, Google Sheets, PDF |
| Receivable Invoice Detail (New) | Excel, Google Sheets, PDF |
| Receivable Invoice Summary (New) | Excel, Google Sheets, PDF |
Purchases
| Report | Format |
|---|---|
| Aged Payables | Excel, 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 Contact | Excel, Google Sheets, PDF |
| Payable Invoice Detail (New) | Excel, Google Sheets, PDF |
| Payable Invoice Summary (New) | Excel, Google Sheets, PDF |
| Supplier Invoice Report | Excel, Google Sheets, PDF |
Inventory
| Report | Format |
|---|---|
| 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 Summary | Excel, Google Sheets, PDF |
| Sales By Item | Excel, Google Sheets, PDF |
Payrun
| Report | Format |
|---|---|
| Pay Run by Employee | Excel, Google Sheets, PDF |
| Pay Run by Pay Item | Excel, Google Sheets, PDF |
| Pay Run by Pay Type | Excel, Google Sheets, PDF |
| Pay Run Summary | Excel, Google Sheets, PDF |
Projects
| Report | Format |
|---|---|
| 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
- Go to Contacts => All Contacts in the drop-down menu.
- Find and click on the contact you need to filter aged receivables data by.
- Copy the
Xero-contactIDfrom 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
- Go to Contacts => All Contacts in the drop-down menu.
- Find and click on the contact you need to filter aged receivables data by.
- Copy the
Xero-contactIDfrom 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
- Go to Accounting => Reports => More reports => Balance Sheet.
Do not confuse the new version of Balance Sheet with the older one that we need.
- Click on More options and filter the Balance Sheet by region you need.
- Click on Update and find
TC1=****************in the page URL. For example,TC1=5e2974a2-097d-4f3b-bfd5-605d78c4a282 - Copy the ID part,
5e2974a2-097d-4f3b-bfd5-605d78c4a282, and replace thetracking-option-IDwith 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
- Go to Business Bank Account => Bank Statements.
- Find
accountID=****************in the page URL. For example,accountID=562555F2-8CDE-4CE9-8203-0363922537A4 - Copy the ID part,
562555F2-8CDE-4CE9-8203-0363922537A4, and replace theinsert-bank-account-IDwith 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– month3– quarter12– 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