Import Xero Data to Google Sheets

In this guide, we’ll walk through how to pull Xero accounting data data directly into Google Sheets, using the API Connector add-on for Sheets.

Contents

• What Does This Integration Do?
• Before You Begin
• Part 1: Connect to the Xero API
• Part 2: Pull Xero API Data into Sheets
• Part 3: Create a Custom Xero Request
• Part 4: Handle Pagination
• Part 5: API Documentation
• FAQ

What Does This Integration Do?

This integration pulls accounting data from Xero's Accounting API into Google Sheets. It enables the following scopes: openid, email, profile, offline_access, accounting.reports.read, accounting.transactions.read, accounting.settings.read, accounting.contacts.read, and accounting.journals.read.

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the Xero API

The easiest way to get started with the Xero API is through API Connector’s built-in integration.

1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
2. Select Xero from the drop-down list of applications
   
3. Under Authorization, click Connect to Xero
   
4. You'll be asked to select an organization and authorize the connection. Click Allow access. If you want to access multiple organizations, you can click Connect again to select additional organizations.
   
5. You'll then be returned to your Google Sheet, and can verify that your Xero connection is active.

Part 2: Pull Data from Xero to Sheets

Now that we’re connected, let’s pull some data into Sheets.

1. Under Endpoint, select the resource that you'll be querying. For this example, we'll select /Invoices.
   
2. Under Headers, select the organization for which you'd like to fetch data from the dropdown list.
   
   
3. Under Request parameters, optionally set the order parameter to set the order of results, and the where parameter to filter them.
4. Choose a destination sheet, name your request, and hit Run to see the report in your sheet.
   

Note: Besides the standard API endpoints, Xero provides special Reports endpoints to retrieve the most commonly viewed reports (Balance Sheets, Budget Summary, Profit & Loss, etc). You can view these reports in the Endpoint dropdown by scrolling down or searching for "Reports".

Part 3: Create a Custom Xero Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the API documentation. 

Here's a sample request setup to demonstrate how it works.

1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
2. In the request form enter the following:
   • Application: Custom
   • Method: GET
   • Request URL: https://api.xero.com/api.xro/2.0/Invoices
   • OAuth: Xero
3. When you select Xero from the OAuth menu, you'll see a blue Connect button if you haven't already authorized the connection. Click through to enable the connection.
4. If you have multiple company accounts in Xero, you can add them by clicking the +tenant button, and then toggle between them using the dropdown account selector.
5. Create a new tab and click Set current to use that tab as your data destination.
6. Name your request and click Run. A moment later you’ll see invoice data populate your sheet.
   

Notes:

• Date parameters are optional. If you leave them off, the current month will be returned.
• An earlier version of this article recommended adding a JMESPath snippet of  Reports[].Rows[].Rows[] to restructure Reports data into a tabular format for Sheets. As of 2023-05-13, API Connector will automatically restructure the data such that JMESPath is no longer needed. Requests created prior to the cutoff date will be unaffected.

Part 4: Handle Pagination

Xero limits the number of records returned in each request. By default, only 100 records will be returned unless you use the 'page' parameter as described in their documentation.

To access more than 100 records, loop through pages automatically with pagination handling, like this:

• Pagination type: page parameter
• Page parameter: page
• Run until: choose when to stop fetching data
  

Part 5: API Documentation

Official API documentation: https://developer.xero.com/documentation/api/api-overview

Reports documentation: https://developer.xero.com/documentation/api/accounting/reports

FAQ

• How do I disconnect from Xero?
  To disconnect from Xero, click the Manage Connections link and click Disconnect next to the Xero connection. You can also navigate to https://apps.xero.com/!XJG5x/ca/connected (or click your company dropdown > App Store > Connected Apps) and disconnect there.
• What doesn’t your integration do?
  This integration does not push data from Google Sheets to Xero. It is a one-way connection that pulls data from Xero to Sheets.
• When and how does data get pulled into Sheets?
  Data can be pulled into Sheets by opening your request and manually clicking Run, which will refresh your sheet with the latest data from Xero's API. Alternatively, you can enable scheduling and automatically refresh requests at the cadence you select (up to 1x an hour).
• Can I run scheduled updates more than once an hour?
  Scheduled triggers can not run more than once an hour. This limit is set by Google.
• How much data can I pull into Sheets?
  There is no specific limit on the amount of data you can pull from Xero's API into Google Sheets. However, Google Sheets only lets requests run for 6 minutes per execution, so very large requests may time out and fail to complete. If you experience issues, please limit requests where possible (e.g. use append mode to fetch new data instead of fetching the entire data set each time).