Xero
Google Sheets + Xero Integration
Query from Xero into Google Sheets or Post data from Google Sheets into Xero.
What is Sheet Director?
Sheet Director is a Google Workspace add-on that helps to manage and control bi-directional data flow between Google Sheets and several other applications like Google Calendar, Tasks, Contacts, Sheets, QuickBooks, FreshBooks, Zoho etc.
It is available in the Google Workspace Marketplace. Please check the Installation instructions to install the add-on.
What is Xero?
Xero is a world-leading online accounting software built for small business.
Get a real-time view of your cashflow. Log in anytime, anywhere on your Mac, PC, tablet, or phone to get a real-time view of your cash flow. It’s small business accounting software that’s simple, smart, and occasionally magical.
Run your business on the go. Use our mobile app to reconcile, send invoices, or create expense claims - from anywhere.
Get paid faster with online invoicing. Send online invoices to your customers - and get updated when they’re opened.
Reconcile in seconds. Xero imports and categorizes your latest bank transactions. Just click ok to reconcile.
Find out more or try Xero Accounting Software for free.
Xero to Google Sheets: Use Case(s)
> Pull data from Xero objects and present it into a spreadsheet - Query
> Push data from spreadsheet to Xero - Post
Xero
Sheet Director
Google Sheets
Demo Video: How to integrate Google Sheets & Xero?
Table Of Contents
Mapping and Data flow
Sheet Director facilitates dynamic mapping of any Google Sheet with Xero
Xero entities can be mapped to sheets/tabs in a Google Sheet.
Fields in each entity can be mapped with column in the sheet/tab
Data flow is bi-directional
Xero Entities supported on Sheet Director
Accounts
BankTransactions
BanTransfers
BrandingThemes
ContactGroups
Contacts
CreditNotes
Currencies
Employees
ExpenseClaims
InvoiceReminder
Invoices
Journals
LinkedTransactions
ManualJournals
Payments
Prepayments
PurchaseOrders
Quotes
Receipts
RepeatingInvoices
TaxRates
TrackingCategories
Users
Getting Started
Choose Sheet Director from Xero App Marketplace
Click on "Get this app using Xero Sign in" as seen in the image
This will take you to Sheet Director Installation Page
On the Sheet Director page as seen in the image
Click on the "Install Now" button
Alternately, you can install Sheet Director directly from Google Workspace Marketplace by clicking on "Install Sheet Director" below
For detailed steps on installation of Sheet Director, refer Installation Page
For general setup of any app integration from Sheet Director, refer Setup Page
Continue reading below for setting up specific configuration to integrate Sheet Director and Xero
Setting up Xero in Sheet Director - QUERY
Choose App and Service:
Select Xero from the list of applications.
Select service as Query.
Xero Configuration
Click on connect to Xero to connect to Xero so that you can pull data.
If Xero is connected successfully the following screen appears.
Select the required application.
Select the source as required.
Select the object as required.
Filters & Sorting
Configure Filter and Sort by to filter and sort the executed data.
Destination Settings:
Configure the destination settings to save the executed data.
Scheduler Settings:
Switch on Run automatically to execute services automatically in background.
Notification Settings:
Configure the Notification settings to setup the email templates and add attachments.
Click on execute to pull the data into sheet
Click save to save the service
Sample Output
Setting up Xero in Sheet Director - POST
Choose App and Service:
Select Xero from the list of applications.
Select service as POST.
Xero Configuration:
Click on connect to Xero to connect to Xero so that you can post data.
Click on continue to provide access to the sheetdirector.
Select the organization
Select entity which you want to post
Select Apply Template to add a Default template
or, Select Setup Manually to setup manually
Source Sheet:
If you are choosing Apply template (Default Template) this will choose sheet and header start at cell automatically
If you chose setup manually
Choose the data sheet to post into selected entity on xero
Choose cell, from where the header will start at
Result column:
If you are choose Apply template (Default Template) this will choose status column and result column automatically
If you chose setup manually
Choose the status column
Choose the result column
Mapping :
If you are choose Apply template (Default Template) this will map fields automatically
click '+' icon to add more fields to the sheet to map
If you chose setup manually
click '+' icon to add fields to the sheet to map
Notification Setting:
Enable notification to get notification on email
Execute:
click on execute to validate and save the service
It will show the number of recordes are ready to post
It will ask for post
Click on POST NOW to post the data to the selected entity on xero
Sample Output
Google Sheet to Xero - Sync
Select service as Synchronize Xero.
Select the Max results required.
Synchronize settings:
Synchronization Keys:
The keys will be used for matching rows in Google Sheets with data in Xero.
Conflict Resolution:
When Synchronization happens, there could be a conflict of data between Google Sheets and Xero.
XeroWins
Google Sheet Wins
Xero Wins: Latest data from Xero will be updated in Google Sheets.
Google Sheet Wins: Latest data from Google Sheets will be updated in Xero.
Note: The major requirement in synchronization is the synchronize keys. If any of the keys is not having a value in the Google Sheet then Sheet Director will create it first and then the service will be executed.
Xero Wins
Select Xero.
The latest updated data from Xero will be updated in Google Sheets.
Choose the spreadsheet to store data.
Select the required cell value.
Select the Status column
If status column is not there, simply click the +button to add the status header on your sheet(A status column will be created in Spreadsheet)
Select the Result column
If result column is not there, simply click the +button to add the result header on your sheet(A result column will be created in Spreadsheet)
Click Execute to execute the data.
The number of records that are ready to be synchronized will be shown.
It will also show all the data that are ready to be updated.
Also, the number of data that needs to be updated into Xero will also be updated.
Click on Sync Now to sync all the data and update it into Google Sheets.
Click on the Refresh icon to refresh the data.
If the data are synced successfully the following screen appears.
Click on Close to close the configuration.
Sample Output
The Status column in the Google Sheets shows the status of the data.
The newly updated data will be marked as Synced Successfully.
The old data that already exists will be marked as Already Synchronized.
Google Sheet Wins
Select Google Sheet Wins.
The latest updated data from Google Sheet will be updated into Xero.
Select Google Sheet Wins.
The latest updated data from Google Sheets will be updated in Xero. Select the sheet to store the output.
Select the Status column
If the status column is not there, simply click the +button to add the status header on your sheet(A status column will be created in the Spreadsheet)
Select the Result column
If the result column is not there, simply click the +button to add the result header on your sheet(A result column will be created in the Spreadsheet)
Click Execute to execute the data.
Click Save to save.
The number of records that are ready to be synchronized will be shown.
It will also show all the data that are ready to be updated.
Also, the number of data that needs to be updated into Xero will also be updated.
Click on Sync Now to sync all the data and update it into Google Sheets.
Click on the Refresh icon to refresh the data.
If the data are synced successfully the following screen appears.
Click on Close to close the configuration.
Sample Output
The Status column in the Google Sheet shows the status of the data.
The newly updated data will be marked as Synced Successfully.
The old data that already exists will be marked as Already Synchronized.
FAQ
Can I connect with multiple Xero organisations from single Google Sheets using Sheet Director?
Yes.
You can connect with multiple organisations from single Google Sheets using Sheet Director. You need to create new services in Sheet Director for every connection that you want to make with Xero. You can learn more about managing multiple services on Multiple services page
How do I automatically post rows from Google Sheet into Xero?
You need to configure a "Post" type service in Sheet Director and schedule that service. You can refer Scheduler for details on how to schedule the service. An hourly schedule can be very useful to post new or matching records automatically every hour.