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: Pull Xero API Data into Sheets
• Part 2: More Example API URLs
• Part 3: Handle Pagination
• Part 4: 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: Pull Xero API Data into Sheets

For this example, we'll get a list of invoices.

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. 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 invoice data populate your sheet.
   
   

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.

Part 2: Example API URLs

Besides the standard API endpoints, Xero also provides special Reports endpoints to retrieve the most commonly viewed reports (1099, Balance Sheets, Budget Summary, Profit & Loss, etc). Depending on your reporting needs, it may be more convenient to use the Reports endpoints to fetch data rather than retrieve individual data points and aggregate them yourself.

Standard Endpoints

• 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

Reports Endpoints

Xero's Reports endpoints send back data as a collection of rows and cells, where each row is labeled by its type (e.g. header, section, summary, etc). This format doesn't easily convert into the tabular format used by Sheets, so we recommend tidying it up with the following JMESPath expression: Reports[].Rows[].Rows[]. (In the future we'll add some automatic pre-processing for Xero.)

• P&L Report (one month)
  

  https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2023-01-01&toDate=2023-01-31

• P&L Report (one year, month by month)
  

  https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2023-01-01&toDate=2023-01-31&periods=11&timeframe=month

• Balance Sheet
  

  https://api.xero.com/api.xro/2.0/Reports/BalanceSheet?fromDate=2023-01-01&toDate=2023-01-31

• Budget Summary
  

  https://api.xero.com/api.xro/2.0/Reports/BudgetSummary?fromDate=2023-01-01&toDate=2023-01-31

Note: date parameters are optional. If you leave them off, the current month will be returned.

Part 3: 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 4: 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).