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: Create Your Xero Request URL
• Part 3: Pull Xero API Data into Sheets
• Part 4: More Example API URLs
• Part 5: Handle Pagination
• Part 6: 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, and accounting.contacts.read.

Before You Begin

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

Part 1: Connect to the Xero API

If you haven’t connected to the Xero API before, you’ll first need to initiate the connection.

1. Open up Google Sheets and click Extensions > API Connector > Manage Connections.
2. In the list of available connections, find Xero and click Connect.
   
3. You will be directed to Xero and asked to Allow Access.
   
4. You’ll then be returned to your Google Sheet, and can verify that your Xero connection is active in the Connections screen.
   

Part 2: Create Your Xero API Request URL

For our first request, we’ll get your Xero tenant ID, since you’ll need this to create subsequent requests.

• API root: https://api.xero.com
• Endpoint: /connections

Putting it all together, we get the full API Request URL.

https://api.xero.com/connections

Part 3: Pull Xero API Data into Sheets

Now let’s enter that URL into API Connector.

1. Back in the Create Request interface, enter the Request URL we just created above.
   
2. Under OAuth, choose Xero from the dropdown menu. You should see the “Connected” badge
   
3. You don’t need any headers for this request so just leave that section blank.
4. Create a new tab and click Set current to use that tab as your data destination.
5. Name your request and click Run. A moment later you’ll see some information about your Xero account populate your sheet. Pay special attention to the value located in the tenantId field as you’ll need it in future requests.
   
   
6. All subsequent requests need to add this tenant ID as a header. The key is xero-tenant-id and the value is your tenant ID, like this:
   
7. We’ll show more on that in the next section. Let’s start pulling in data about your Xero account!

Part 4: Example API URLs

You can access the Xero Accounting API documentation here, but if you just want to get started, you can try the following example URLs.

You can also access these example URLs directly in API Connector through the API Library (just search for “Xero” in the API field).

• Invoices
  

  https://api.xero.com/api.xro/2.0/Invoices

  

• Accounts
  

  https://api.xero.com/api.xro/2.0/Accounts

  

• Bank Transactions
  

  https://api.xero.com/api.xro/2.0/BankTransactions

• Contacts
  

  https://api.xero.com/api.xro/2.0/Contacts

• Items
  

  https://api.xero.com/api.xro/2.0/Items

• Payments
  

  https://api.xero.com/api.xro/2.0/Payments

Part 5: 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:

• API URL: enter your request URL
• Pagination type: page parameter
• Page parameter: page
• Number of pages: enter the number of pages you’d like to fetch
  

Part 6: API Documentation

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

FAQ

• How do I disconnect from Xero?
  To disconnect from Xero, click the Manage Connections link and click Disconnect next to the Xero connection.
• 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 and applies to all Sheets add-ons.
• 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).