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 two steps:

• Coupler.io installation
• Setting up a Xero importer

Install Coupler.io

First, install Coupler.io from the G Suite Marketplace. Here is a direct link.

• 

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

Set 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. For this, 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.

Required fields

Title – add the name of your importer. 

Sheet Name – add the name of the sheet which will be receiving data.  

Data Entity – select the data category to import. You can choose from a number of options: Accounts, Bank Transactions, Bank Transfers, and others.

Report Type – pick the report type based on your needs: 

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

Automatic data refresh – choose the frequency of automatic resync of data. If you don’t need this feature, disable it by clicking on the toggle.

Additional fields and Settings

Additional fields are optional and meant for additional manipulations of data:

• Where –  to retrieve a filtered set of elements that don’t have explicit parameters.
• Order – to return results in ascending or descending order.
• Split By – to split rows by a specific field that contains multiple values into separate lines.

For more about the Additional fields, as well as the Settings section, refer to Coupler.io knowledge base. 

Once you’ve filled in all the required fields, click Connect to connect your Xero account (data source) to Coupler.io. When connected, click Add Importer and run your Xero importer afterwards to pull data to a spreadsheet. Here is how it looks:

• 

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 ARRAYFORMULA and IF functions, as follows:

• 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 data refresh in your Xero importer, 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!

Back to Blog