How to Integrate Xero and Google Sheets

On By Zakhar Yung

What we love about Xero is that it allows small businesses to monitor their financial health on one dashboard. However,  it is not an almighty tool. And many accounting and finance specialists leverage spreadsheets over and above anything else. How do they connect Xero to Google Sheets? Laymen specialists export data manually, while professionals opt for automated solutions. One of those is a GSheets add-on, Coupler.io. Read further and learn how to join the cohort of professionals.

Why import data from Xero to Google Sheets? 

Xero is a great online accounting solution. With it, you can handle bank reconciliation, prepare payroll, adjust the opening balance, and more. Google Sheets, in turn, enables versatile transformation of raw accounting data to gain insights, such as:

  • build a custom accounts receivable dashboard
  • build an expenses and revenue monitor in a spreadsheet
  • set up a table to reconcile invoices and expenses (fact vs. plan)
  • visualize and format data 
  • calculate versatile sales metrics
  • and so on.

You likely have your own grounds to move data from Xero to Google Sheets. Feel free to share them in the comments section. 

How to integrate Xero and Google Sheets

The integration process consists of three steps:

  • Coupler.io installation
  • Setting up a Xero importer
  • Running the importer 

Coupler.io installation

First, install Coupler.io from G Suite Marketplace with this link.

Click Install, choose your Google account and grant Coupler.io the rights required to enable data importing from Xero:

For more information about the add-on and its available integrations, please visit Coupler.io home page.

Setting up a Xero importer

An importer is an integration that connects Google Sheets to a specific app, such as Airtable, Pipedrive, or, in our case, Xero. So, for data import, we need to set up a Xero importer.

Open Coupler.io in the Add-ons tab: 

Click +Add Importer and select Xero. After that, you’ll need to fill in a few fields to set it up.

Required fields

Title – add the name of your data source. 

Sheet Name – type in the name of a sheet to which you will import your data.  

Entity – a Xero group from which the data will be imported. You can choose from a number of options: Accounts, Bank Transactions, Bank Transfers, and others.

Report Type – the chosen type of report affects the data fields to be imported. 

  • A simple report imports default fields. Available for all entities.
  • A detailed report imports an extended list of fields. Available for Invoices, Contacts, Bank Transactions, and Manual Journals.

Additional fields and Settings

Additional fields are meant for additional manipulations of data, such as filtering, ordering, and splitting rows of the retrieved data. The Settings field allows you to choose a data import mode: replace or append. For more on this refer to Coupler.io knowledge base. 

Once you’ve filled in all the required fields, click Connect and perform the requested manipulations to connect your Xero account (data source) to Coupler.io. Click Add Importer once Xero has been connected.

Running the importer

At the end of the importer installation, you’ll see a new sheet with your Xero data. Unless you enabled the Re-import automatically feature, you’ll have to run the Xero importer each time you want to update the data.

What data can you export from Xero?

With Coupler.io, you can export all data from Xero including: Invoices, Contacts, Bank Transactions, Purchase Orders, and many more.

A real-life use case: create an automatically updating sales dashboard

Let’s say you own a software development company that specializes in consultancy, web and mobile development. You have Xero in your toolbox and leverage it for accounting. The Xero dashboard is informative but you need a custom one that will give you another perspective on sales performance. For example, comparison by years and/or business units. Besides, it would be cool to have this custom dashboard updated automatically. It may look as follows:

To tailor such a dashboard, use Coupler.io to import the Invoices data from Xero to Google Sheets. 

Note: Since we want to show sales performance by business unit/project, we have to import a detailed report, which contains the required column titled LineItems.Tracking.

With the raw accounting data in a spreadsheet, create a separate sheet titled Dashboard. Here, we’ll extract the values required to build our sales performance dashboard, which consists of the following charts:

  • Cumulative revenue by years
  • Revenue by business units
  • Separate charts with revenue from consultancy, web, and mobile development

You can download the ready-to-use dashboard template for free and adjust it for your project. 

Besides formulas in the spreadsheet, you’ll have to make an additional manipulation. Create two additional columns in the sheet with raw data to enable filtering by month and year. Use the following array formulas:

  • Month: =ARRAYFORMULA(IF(ISBLANK(T2:T),FALSE,MONTH(T2:T)))
  • Year: =ARRAYFORMULA(IF(ISBLANK(T2:T),FALSE,YEAR(T2:T)))

Now, you can tweak the dashboard according to your requirements. If you enable automatic re-import via Coupler.io, your dashboard will be updated automatically. No manual data import anymore! 

To wrap up: export from Xero to Google Sheets or not?

You’re the only one who knows the answer to this question. If you can go with Xero only for your accounting and reporting, then OK. However, experience has shown that data-centered specialists do not abandon spreadsheets entirely. In this case, Coupler.io will become a reliable partner. It will optimize Xero to Google Sheets integration and let you automate data import. Make your accounting enjoyable. Good luck!

Leave a Reply