Import Xero Data to Google Sheets

Last Updated OnJune 7, 2020

byAna

Xero provides accounting software for small businesses. Their API is only accessible via Oauth 2.0, which is considerably more complex than using an API key. Therefore, you should probably consider using specialized add-ons or software for accessing Xero data. However, if you are willing to give it a go, or if you’re for some reason curious how to manually work through an OAuth 2.0 process (e.g. to test out the API in Postman or other no-code API clients), this guide is for you! Below we will navigate through the entire process of retrieving and refreshing tokens to make OAuth requests from Xero’s REST API. We’ll then pull Xero data into Google Sheets, using the API Connector add-on for Sheets.

Part 1: Get your Xero Credentials
Part 2: Get your Xero Oauth 2.0 Access Details
Part 3: Get your initial access token
Part 4: Get your refresh token
Part 5: Get your tenant ID
Part 6: Make a request

PART 1: GET YOUR XERO CREDENTIALS

While logged into your Xero account, navigate to https://my.xero.com/. For this guide we’ll enable the demo company.
Now we need to create an OAuth2 app to receive the credentials we need for querying the Xero API. Navigate to https://developer.xero.com/myapps and click the New app button.
Give your app a name and add in URLs as requested. It can be any URL but make sure they’re https. Click “Create app”.
You’ll now be prompted to generate a secret.
Once you’ve done this, you’ll have access to a client ID and client secret. Copy these and keep them handy as we’ll need them in a minute. For convenience, you can add them into an “Inputs” tab in your Google Sheet so you can easily reference them later.

PART 2: GET YOUR XERO OAUTH2 ACCESS DETAILS

Because the Xero API requires Basic Authentication, we need to encode our authentication info to base 64. You can do this by entering your your credentials in the format YOUR_CLIENT_ID:YOUR_CLIENT_SECRET into this form (i.e. your client ID, then a colon, and then your client secret). Your encoded credentials will appear underneath.
The encoding script runs in your browser, and none of your credentials are seen or stored by this site.

(If you have any problems with the above, please check this post for some alternate methods of encoding your credentials).

Now, in a new browser tab, navigate to the following URL, making sure to match the values from your app:

https://login.xero.com/identity/connect/authorize?response_type=code&client_id=YOUR_CLIENT_ID&redirect_uri=YOUR_REDIRECT_URL&scope=offline_access openid profile email accounting.transactions&state=123

Choose your company and click Allow Access
In the URL bar, you’ll see the query string code. Copy this entire value.

PART 3: GET YOUR INITIAL XERO ACCESS TOKEN

We’ll now start entering values into API Connector. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
In the Create Request interface, choose POST and enter the following values:
- API URL path:
```
https://identity.xero.com/connect/token
```
- Headers: (Note that ‘Basic {encoded value}’ refers to the value retrieved just above in part 2, step 1)
  Authorization Basic {encoded value}
  content-type application/x-www-form-urlencoded
- Post Body:
```
{
"grant_type":"authorization_code",
"redirect_uri":"YOUR_REDIRECT_URL",
"code":"YOUR_CODE"
}
```
All together, it will look like this:
Create a new sheet called XeroToken. Click Set to set this sheet to receive the output from your request.
Name and save your request. Click Run and you’ll see some tokens printed onto your sheet.
NOTE: the code from part 2 expires after approximately 1 minute, so you may need to repeat that step and plug in a new code if too much time has passed.

Congrats, you now have your first access and refresh tokens! You can use these to make requests to the Xero API. (Credit for much of the above process goes to this helpful article).

PART 4: REFRESH YOUR XERO ACCESS TOKEN

The initial access token lasts only 30 minutes. To lengthen the time you have available without having to start from scratch, you can keep refreshing your token, which will give you another 30 minutes each time. Refresh your token as follows:
In the Create Request interface, choose POST and enter the following values:
- API URL path:
```
https://identity.xero.com/connect/token
```
- Headers:
  grant_type refresh_token
  content-type application/x-www-form-urlencoded
- Post Body:
```
{
"grant_type":"refresh_token",
"refresh_token":"YOUR_REFRESH_TOKEN",
"client_id":"YOUR_CLIENT_ID",
"client_secret":"YOUR_CLIENT_SECRET"
}
```
- For convenience, we’ll reference these cells in our request so we don’t need to type them in again. When you hit Run, you’ll get a new refresh token, which you can then reference and refresh in your subsequent request. All together, it will look like this:

PART 5: GET YOUR TENANT ID

Xero requires that we attach a tenant ID to our requests, which represents the organization we’re querying.
To get the tenant ID, enter the following GET request into API Connector:

API URL path:
```
https://api.xero.com/connections
```
Headers:
Authorization Bearer YOUR_ACCESS_TOKEN
As before, we’ll reference the access token so we don’t need to enter it manually. You should now see your tenant ID in the sheet, which we’ll use in the next request.

PART 6: MAKE A REQUEST

You may have thought this point would never come… but we’re finally ready to make a request for actual data! Let’s call the Invoices endpoint as follows:

- API URL path:
```
https://api.xero.com/api.xro/2.0/Invoices
```
- Headers:
  xero-tenant-id YOUR_TENANT_ID
  Authorization Bearer YOUR_ACCESS_TOKEN

Referencing cells as before, we’ll see the returned data in our sheet:

Now, go ahead and explore the documentation for other endpoints. For example, https://api.xero.com/api.xro/2.0/BankTransactions will produce a list of transactions. Note that you’ll be limited by the scopes you declared when creating the app, so add more scopes if you need them.