This article provides detailed instructions on using Google Apps Script to retrieve line-item product and service information from your Xero account. Google Apps Script is a cloud-based JavaScript platform powered by Google that lets you integrate with and automate tasks across Google products and various 3rd party services. It is one of a few options available to you to extend the functionality of our platform by seamlessly integrating with other 3rd party systems, as detailed in the article here.

Standard Disclaimer

While we have tested the script presented here and found it safe and reliable, please note that this script is provided “as is” without any warranties or guarantees. We are not responsible for any issues, errors, or damages that may result from its use, including but not limited to data loss, security vulnerabilities, or service disruptions.
Ensure you review and test the script before deploying it in your environment. By using this script, you agree that we are not liable for any consequences arising from its use.

What does this script do?

This script allows you to add a REST-connected datasource to your account that will synchronize products and services from your Xero account for use in any app you create on our platform. As products and services change very rarely, configuring this data as a data source gives you access to your data whenever you need it on our platform, even when your users are offline or in an area with limited Internet access.

This script guides you through the process of setting up the REST-connected datasource to your Xero product and service items. The full integration achieved is configured in a few parts:

1. Google Apps Script Configuration and Authorization
   This is where you will do the initial setup of the script in Google Apps Script, including running a specific function to get the redirect URL that we’re going to use on the Xero side of the integration. You will also need to authorize your script to ensure it runs with the correct permissions.
2. Xero Configuration
   This is where you will need to perform various setup and configuration steps on the Xero platform to ensure that your Google Apps Script can communicate successfully with your account on Xero.
3. Google Apps Script Deployment
   This is where you will be guided through the steps to successfully deploy your script in your Google Apps Script account so that our platform can communicate with it and achieve its functional objectives.
4. Deployment on our Platform
   In this step, you will be guided through the steps to setup and configure your REST-connected datasource so that you can synchronize your Items/Products/Services from your Xero account to a datasource on our platform.

Step-by-step Guide

While there are a fair amount of moving parts in this integration, follow our step-by-step guide and you should be all good.

Google Apps Script Configuration and Authorization

1.1 – Create a new ‘Project’ at https://script.google.com/home

1.2 – Click on the menu item “Libraries” as shown in the screenshot below:

1.3 – In the “Add a Library” text box, enter the script ID 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF and click the “Lookup” button.

1.4 – Next, select a version from the dropdown box (the latest version is recommended) and click the “Add” button.

1.5 – Copy the script below and paste it into the Google Sheets editor(remove any code that is already in the editor).

/* * This sample demonstrates how to configure the library for the Xero API. * Instructions on how to generate OAuth2 credentuals is available here: * https://developer.xero.com/documentation/oauth2/auth-flow /var CLIENT_ID = ”;var CLIENT_SECRET = ”;/* * Authorizes and makes a request to the Xero API. /function run() { var service = getService_(); if (service.hasAccess()) { // Retrieve the tenantId from storage. var tenantId = service.getStorage().getValue(‘tenantId’); // Make a request to retrieve user information. Logger.log(service.getAccessToken()); Logger.log(‘Tenant Id =’ + tenantId); Logger.log(‘Refresh Token =’ + service.getStorage().getValue(‘refresh_token’)); var url = ‘https://api.xero.com/api.xro/2.0/Items’; var response = UrlFetchApp.fetch(url, { headers: { ‘Authorization’: ‘Bearer ‘ + service.getAccessToken(), ‘Accept’: ‘application/json’, ‘method’: ‘get’, ‘Xero-tenant-id’: tenantId }, }); var result = JSON.parse(response.getContentText()); return result; Logger.log(JSON.stringify(result)); } else { var authorizationUrl = service.getAuthorizationUrl(); Logger.log(‘Open the following URL and re-run the script: %s’, authorizationUrl); }}function doPost (e) { var lock = LockService.getScriptLock() lock.tryLock(10000) try { var response = e.postData.contents; var trxType = e.parameter.TrxType; var service = getService(); var res; if (service.hasAccess()) { switch (trxType) { case “CreateCustomer”: res = CreateCustomer(service,response); break; case “CreateInvoice”: res = CreateInvoice(service,response); break; default: break; } return ContentService .createTextOutput(JSON.stringify(res)) .setMimeType(ContentService.MimeType.JSON) } } catch (e) { return ContentService .createTextOutput(JSON.stringify({ ‘result’: ‘error’, ‘error’: e + ‘– ‘ + response})) .setMimeType(ContentService.MimeType.JSON) } finally { lock.releaseLock() }}function doGet(e){ var data = run(); return ContentService.createTextOutput(JSON.stringify(data));}; /* * Reset the authorization state, so that it can be re-tested. /function reset() { getService_().reset();}/* * Configures the service. /function getService_() { return OAuth2.createService(‘Xero’) // Set the endpoint URLs. .setAuthorizationBaseUrl( ‘https://login.xero.com/identity/connect/authorize’) .setTokenUrl(‘https://identity.xero.com/connect/token’) // Set the client ID and secret. .setClientId(CLIENT_ID) .setClientSecret(CLIENT_SECRET) // Set the name of the callback function that should be invoked to // complete the OAuth flow. .setCallbackFunction(‘authCallback’) // Set the property store where authorized tokens should be persisted. .setPropertyStore(PropertiesService.getScriptProperties()) // Set the scopes to request from the user. The scope “offline_access” is // required to refresh the token. Full list of scopes is available here: // https://developer.xero.com/documentation/oauth2/scopes .setScope(‘accounting.settings.read offline_access’);};/* * Handles the OAuth callback. /function authCallback(request) { var service = getService_(); var authorized = service.handleCallback(request); if (authorized) { // Retrieve the connected tenants. var response = UrlFetchApp.fetch(‘https://api.xero.com/connections’, { headers: { Authorization: ‘Bearer ‘ + service.getAccessToken() }, }); var connections = JSON.parse(response.getContentText()); // Store the first tenant ID in the service’s storage. If you want to // support multiple tenants, store the full list and then let the user // select which one to operate against. service.getStorage().setValue(‘tenantId’, connections[0].tenantId); service.getStorage().setValue(‘refresh_token’, ‘testing’); return HtmlService.createHtmlOutput(‘Success!’); } else { return HtmlService.createHtmlOutput(‘Denied.’); }}/* * Logs the redirect URI to register in the Dropbox application settings. */function logRedirectUri() { Logger.log(OAuth2.getRedirectUri());}

1.6 – Click the Run button, as shown in the screenshot below.

This initial run will trigger an error and parse the code you just added so you can run the next function.

1.7 – Select the ‘logRedirectUri‘ function and click ‘Run‘ as shown in the screenshot below:

1.8 – The authorization required window will open; click ‘Review Permissions’.

A sign-in with Google window will open. Once the page reloads, select your account and click continue.

An additional modal window will show, asking you to confirm the access permissions you need to proceed. Click “Continue” as shown in the screenshot below:

1.9 – Your code will run and generate a URL as output, as shown in the screenshot below. Copy the URL in the execution log and save it for later use, as this will be the redirect URL you will use to configure your app in Xero.

At this point you’re done with the initial setup of the integration in Google Apps Script, next we need to configure Xero.

Xero Configuration

2.1 – Create or log in to your Xero account at https://developer.xero.com/ – Once logged in, click the “New app” button to create a new app.

2.2 – When you click the “New App” button, a modal window will show requesting some details for your new app as shown in the screenshot below:

The company URL and Redirect URL will be your Google script URL (the URL you generated from the first step).

2.3 – Once your app has been created, go to the configuration settings on your newly created app to generate a client secret key. Copy the Client ID and Secret key values, as you’ll need them to complete the setup of your Google Apps Script code.

2.4 – Go back to your Google Apps Script and update the part of your code outlined in the screenshot below with the Client ID and Client Secret key you just obtained from your Xero account.

2.5 – Next, select the “run” function from the dropdown list of functions in your Google Apps Script and then click “Run” as shown in the screenshot below:

2.6 – Once the function runs, it will generate output in the execution log. Copy the URL returned in the execution log output (shown in the screenshot below), paste this URL into a new browser tab, and visit the link.

2.7 – When you open the link you copied into a new browser tab and visit the link, this will initiate an authorization process with your Xero script. Select the organization your script will have access to and click ‘Allow access‘

The browser window will be reloaded with a ‘Success’ message. Close this browser window. You are now ready to proceed with the deployment of your app in Google Apps Script.

Google Apps Script Deployment

3.1 – Go back to your Google Apps Script interface. In your App script, click ‘Deploy’ and select ‘Managed deployments’ as shown in the screenshot below.

3.2 – A new window will open. Click ‘Create deployment’ in this window, as shown in the screenshot below:

3.3 – On the next screen, click the cog icon and select ‘Web app’ as shown in the screenshot below:

3.4 – In the drop-down menu for “Who has access”, select Anyone, and click the deploy button.

3.5 – Copy the ‘Web app URL’ (shown in the screenshot below) that is returned. This will be the URL that you will use in your RestAPI to call the Google app script from our platform.

You’re now almost ready for the final steps; well done on making it this far; you’re almost there.

Deployment on our Platform

4.1 – Now that the main steps have been completed, you need to link your data source to your freshly deployed script on Google Apps Script. Log in to your Appenate web portal and create a new Data source. Enter a name for your data source and click ‘Create‘. Click the Save Button to save your datasource and then go to the data source’s ‘Settings‘ to add the Rest API Connector.

The REST Connector modal window will load, allowing you to configure the connection to your Google Apps Script.

4.2 – Configure your REST connection with the details stipulated below:

• Set HTTP Action to ‘GET‘
• Target URL to your App Script Web URL (this is the URL you generated at the end of the last step when you deployed your Google Apps Script).
• Set “Path Row Selector” to Items[*]
• In the Response Mapping, set the values for the three columns to the following ‘Path to Column Value‘ and ‘Column Name‘
  1. ItemID
  2. Code
  3. Description
• Click ‘Save‘
• In the Rest Connector, click the Ellipsis and ‘Run Now‘ to sync your data source to Xero Items endpoints.

Your datasource should successfully connect to your Google Apps Script and retrieve the list of items, saving them in the data source for you to use. Should you have any issues, check the Rest Connector log to see if any data has been loaded/synced from Xero.