Back to Blog

Xero Reports in a Spreadsheet: How to Pull Reports from Xero to Google Sheets

With the Xero Importer by Coupler.io, you can import Invoices, Bank Transactions, Accounts, and tons of other types of raw accounting data. If you’ve missed how, check out our blog post: Xero to Google Sheets Integration. Meanwhile, unreconciled transactions (in Bank Statements), as well as pre-made Xero reports, have not been available to our users… until recently. Now you can pull Profit and Loss, Balance Sheet and other reports from Xero to Google Sheets. Read on to find out how this can be done.

Which Xero reports can you export to spreadsheets?

Note: all the options below include the older versions of reports – those marked with a white star in Xero. New versions (marked with a blue star) are not exportable for now.

Xero Financial Reports

  • Balance Sheet – a balance sheet at the end of the month for the specified date (including the year-to-date values).
  • Profit and Loss – a profit and loss for the specified period.
  • Budget Summary – a monthly budget summary for the specified period.
  • Executive Summary – a brief summary of monthly totals and some common business ratios for the specified date.

Xero Accounting Reports

  • Bank Statement – a bank statement for the specified bank account (including unreconciled transactions).
  • Bank Summary – a summary of balances and cash movements for each bank account.
  • Trial Balance – a trial balance of the month for the specified date (including the year-to-date values).

Xero Sales Reports

  • Aged Receivables (by Contact) – aged receivables for the specified period.

Xero Purchases Reports

  • Aged Payables (by Contact) – aged payables for the specified period.

How to import reports from Xero into Google Sheets

If you found the report your need above, check out how to get it to your spreadsheet. For this, take the following steps:

Install Coupler.io 

You can find Coupler.io on the G Suite Marketplace or install it from the Add-ons menu of your spreadsheet. Here is a direct link to the add-on.

Set up a Xero Reports importer

An importer connects Coupler.io to the data source, such as Xero, QuickBooks, CSV, and so on. You can learn the available importers on the Coupler.io home page. To import reports from Xero, you’ll need to set up a Xero Reports importer. Go to the Add-ons menu => Coupler.io => Open Dashboard, click +Add Importer and select Xero Reports

After that, fill out the required parameters:

  • Title – add the name of your importer.
  • Sheet Name – add the name of the sheet that will be receiving data.
  • 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.

Parameters for Aged Payables by Contact

Parameters for Aged Receivables by Contact

Parameters for Balance Sheet

Parameters for Bank Statement

Parameters for Bank Summary

Parameters for Budget Summary

Parameters for Executive Summary

Parameters for Profit and Loss

Parameters for Trial Balance

You may also want to check out Xero documentation for reports parameters.

  • Automatic data refresh – choose the frequency of automatic data import, so you won’t need to manually run the importer. 

Settings

Optionally, you can play with the Settings fields:

  • Mode – pick the data importing mode:
    • Replace – every new import will fully replace the data on the sheet.
    • Append – every new import will place data below the previously imported data on the sheet.
  • Cell address – specify the first cell where the data range will be imported. A1 is the default value.
  • Last updated – select Yes if you want to add a column with the date of the last data import. No is the default value.

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.

To wrap up: Are Xero reports useful in a spreadsheet?

Xero Reports importer was not a bee in the bonnet for the Coupler.io team. We’ve got multiple requests from Xero importer users who had wanted to import both reconciled and unreconciled transactions. So, Xero Reports is a child of feedback. If you did not find the integration you need in our importer list, share your use case with us by filling out this form. This will help us decide which importer will be created next. Good luck with your data!

Back to Blog

Access your data
in a simple format for free!

Start Free