Search API Connector Documentation

Print

Import Xero Data to Google Sheets

premium

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?

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.

xero-img13

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.
    xero-oauth2-img1
  3. You will be directed to Xero. Select your company and click Allow Access.
    xero-oauth-allow
  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 a list of invoices for your Xero account.

  • API root: https://api.xero.com
  • Endpoint: /api.xro/2.0/Invoices

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

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

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.
    xero-url-invoices
  2. Under OAuth, choose Xero from the dropdown menu. You should see the "Connected" badge
    xero-connected
  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 invoice data populate your sheet.
    xero-invoices-result
  6. If you have multiple company accounts in Xero, you can add them by clicking the plus (+) sign, and then toggle between them using the dropdown account selector.

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.

  • 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
  • P&L Report
    https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2021-10-01&toDate=2021-10-31
    for the P&L report, clean up the data with a JMESPath expression of Reports[].Rows[].Rows[].{Category:Cells[0].Value,Values:Cells[1].Value}

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.

xero-oauth2-img10

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
    pagination-page-parameter

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. 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).

12 thoughts on “Import Xero Data to Google Sheets”

  1. Hi there - I've been trying to get a custom OAuth2 connection to work with Workflowmax, which is owned by xero. I see that you have a xero connector but it doesn't seem to apply to workflowmax. Any tips on how I would configure a custom connector?

    So far I have a client id, and secret and I think I have the authentication URL right but i'm not sure how to build the token URL or how to use the token in the header of a subsequent request.

    Reply
    • Hey Jay, if you're using OAuth2 you shouldn't need to manually add any tokens to your headers. The authorization and token URLs should be provided in the Workflowmax docs (I just checked and it looks like authorization URL = https://login.xero.com/identity/connect/authorize?scope=workflowmax&offline_access and token URL = https://identity.xero.com/connect/token). Can you please try that and let me know how it goes?

      Reply
      • Thanks for your quick response!

        The two URLs you've identified are what I have configured so that's good and I think it's working. When I click on the 'connect' button it authenticates against xero and returns to the google sheet as expected.

        Given that i'm using the OAuth2 connector you indicated that I don't need to do the token refresh. And of course, you're right! I just re-read how to make calls to the API and I was missing a header for the tenant id. I've added that and it's working as expected now.

        Thanks for your help!

  2. I am busy working on this and would like to limit the results of the Xero Items API calls. It's working perfectly except for the sales details unit price.

    Can anyone see anything wrong with this
    Items[*].{Code:Code,Description:Description,QuantityOnHand:QuantityOnHand,Xero_Price:SalesDetails[*].unitPrice,IsTrackedAsInventory:IsTrackedAsInventory}

    Thanks

    Reply
    • Hey Andrew, the problem is that SalesDetails is an object, not an array. Please try this instead:
      Items[*].{Code:Code,Description:Description,QuantityOnHand:QuantityOnHand,Xero_Price:SalesDetails.UnitPrice,IsTrackedAsInventory:IsTrackedAsInventory}

      Reply
    • Do you mean the P&L report described here? https://developer.xero.com/documentation/api/accounting/reports#profit-and-loss
      In that case you'd create a request like https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=+++Sheet1!A1+++&toDate=+++Sheet1!A2+++, where you reference the date cells in your sheet as described here.

      As an aside, Xero sends this report data back in a way that doesn't work well with Sheets, so you can also restructure the data with the following JMESPath (just copy and paste this into the JMESPath field before running): Reports[].Rows[].Rows[].{Category:Cells[0],Values:Cells[1]}

      Reply
  3. Hi, can I pull budget info from Xero with this API. I tried setting this up and got this error:

    "1) Budgets API: Completed with errors
    - We received an error from xero.com (401) show response
    {"Type":null,"Title":"Unauthorized","Status":401,"Detail":"AuthorizationUnsuccessful","Instance":"d3d909cf-b207-4449-a8c1-304ab17a6bab","Extensions":{}}"

    Reply
  4. Would be helpful to have a fully worked example for Xero. A typical P&L report pull -- the most common use case -- has 12 months of data:
    https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate=2022-12-01&toDate=2022-12-31&periods=11&timeframe=MONTH

    Other integration tools drop this into Google sheets in a friendly format : Ie one column per month one row per category. Can you supply the GMES path or other Output options settings that would produce this. The sample JMES path here and the one in the documentation provide either no values or just a single month.

    Reply
    • Thanks for the suggestion. Currently API Connector just prints out the data the way it comes back from the API, and they don't send the data back in a table for some reports. We'll addd some custom processing for Xero soon.

      Reply

Leave a Reply to Jay Cancel reply

Jump To...