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. We’ll be connecting using API Connector’s built-in OAuth2 integration for Xero (this is a paid feature, please upgrade to access).

CONTENTS

PART 1: CONNECT TO 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 Add-ons > 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 and asked to Allow Access.
    xero-oauth2-img2
  4. You’ll then be returned to your Google Sheet, and can verify that your Xero connection is active in the Connections screen.
    xero-oauth2-img3

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

We can now enter our values into API Connector and start importing Xero data into Google Sheets.

  1. Back in the Create Request interface, enter the Request URL we just created above.
    xero-oauth2-img4
  2. You don’t need any headers so just leave that section blank.
  3. Under Authentication, choose Xero from the dropdown menu.
    xero-oauth2-img5
  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.
    xero-oauth2-img6
  6. All subsequent requests need to add the tenant ID as a header, where the key is xero-tenant-id and the value is your tenant ID, like this:
    xero-oauth2-img7
  7. We’ll show that again 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.

  • Invoices
    https://api.xero.com/api.xro/2.0/Invoices
    xero-oauth2-img8
  • Accounts
    https://api.xero.com/api.xro/2.0/Accounts
    xero-oauth2-img9
  • 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

2 thoughts on “Import Xero Data to Google Sheets”

  1. do you have to keep re-doing this every day? it seems that I have to repeat step 2 part 4 again the next day? is there any way to avoid this

    Reply
    • Hi there, you could set the request to refresh on a schedule, so you wouldn’t need to manually do it, but yes, basically you need to refresh the token every time you make a request. I’m planning to rewrite this entire article soon because API Connector now supports OAuth2, which takes care of that refresh process for you.
      10/9 UPDATE: article is now rewritten.

      Reply

Leave a Comment