Google Sheets Integration with Decision Trees

—

by

Emily Newman
in
Google Sheets integration with Yonyx Guides

Interactive decision trees are used to create cold calling scripts that guide call center agents. Authors use decision tree maker tools to create step by step instructions for call center agents to follow. The integration of Google Sheets with decision trees helps with personalizing call scripts at each step and recording important data collected during customer interactions in the appropriate cells of the Google Sheet.

By integrating with Google Sheets, Decision trees can personalize call scripts and update captured data back in respective cells of Google Sheet.

This Google Sheets integration relies on the use of Placeholders. Placeholders serve as variables capable of storing values. A Placeholder can store a customer’s name, a phone number, a date, or a list of items checked in a checkbox list. A placeholder may also store an entire page of html. Call scripts in each step of a decision tree are personalized using Placeholder values. Placeholders also form the core of integration with third-party systems. This article outlines the process of setting up Google Sheets integration with Yonyx decision trees using Placeholders.

Yonyx decision trees can read and write data from Google Sheets.

Step 1: You need to be the owner of the Google Sheet to setup integration using the following steps.

Step 2: Make sure the Column names for each column of the Google Sheet has no special characters or spaces. User _ or – instead – e.g. name column headings as Business-Name, or Customer_Name.

Step 3: Give read-only access to all Agents in your team who will be traversing the Yonyx decision trees integrated with Google Sheets.

Step 4: Create a Yonyx Placeholder corresponding to each Column of the Google Sheet that you expect to read or write – e.g. customer-name, phone-number etc. In addition create following placeholders required for this integration:

  • api-url
  • api-result
  • api-message
  • data-result
  • data-message
  • unique-key-name
  • unique-key-value
  • number-of-retries

There needs to be a designated column of the Google sheet that identifies the unique key for each record. In the example below, the first column RecNo is a unique key. Once the integration with Google Sheets is finished, Yonyx platform will read all cells of the row (chosen based on the value of this unique key – e.g. 10009) into corresponding Yonyx Placeholders. The values of cells in this “unique key” designated column should be unique. You should choose a column such as Lead-ID, Customer-ID, Ticket-ID etc. for your unique key. Note, if the value of a cell under the unique key column repeats across multiple rows (e.g. if 10009 were to appear in multiple rows below) Yonyx platform will always read and write data from the first matching row.

Each column of a Google Sheet should have a corresponding Yonyx Placeholder, in order to read/write data between Google Sheets and Yonyx decision trees.

Step 5: On Google Sheets window, click on Extensions tab, and then choose Apps Script (as shown below).

To add script to Google Sheets, choose Extensions and then Apps Script. Now you can add the code provided to connect your Google Sheets with Yonyx decision trees, via Placeholders.

Step 6: Delete any existing code there and copy/paste the code shown below:

function fixLineReturns(e){return e.match(/"[^"|:]+"/g).forEach((t=>{/[^",{1,}\s{1,}"]/g.test(t)&&(e=e.replace(t,t.replace(/\n/g,"\\n")),Logger.log("match is: "+t),Logger.log("replacement is: "+t.replace(/\n/g,"\\n")))})),Logger.log(e),e}function getHeaders(e){return e.getRange(1,1,1,e.getLastColumn()).getValues()[0]}function findFirstMatch(e,t,r){var n=getColumnIndex(e,t);return e.getRange(1,n,e.getLastRow(),1).createTextFinder(r).matchEntireCell(!0).findNext()}function findRowIndex(e,t,r){var n=findFirstMatch(e,t,r);return null!==n?n.getRow():-1}function getColumnIndex(e,t){for(var r=getHeaders(e),n=0;n<r.length;n++)if(r[n]===t)return n+1;return-1}function findRowByKeyValue(e,t,r){var n=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e),a=findFirstMatch(n,t,r);if(null!==a){var s=a.getRow(),o=a.getColumn();Logger.log("Index: Row: "+s+", Column: "+o);var g=n.getRange(s,o).getValue();Logger.log("Cell Value: "+g);var c=n.getRange(s,1,1,n.getLastColumn()).getValues()[0];return Logger.log("Row Values: "+c),{success:!0,message:"",data:rowToObject(n,c)}}var u="Row not found";return console.error(u),{success:!1,message:u}}function rowToObject(e,t){var r=getHeaders(e),n={};if(t.length===r.length)for(let e=0;e<r.length;e++)n[r[e]]=t[e];return n}function updateSingleRow(e,t,r,n){var a=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e),s=findFirstMatch(a,r,n);if(null!==s){var o=s.getRow(),g=a.getRange(o,1,1,a.getLastColumn()).getValues()[0],c=(rowToObject(a,g),getHeaders(a));for(let e=0;e<c.length;e++)console.log(c[e]),console.log(t[c[e]]),t[c[e]]&&a.getRange(o,e+1).setValue(t[c[e]]);return Logger.log("Data updated successfully."),{success:!0,message:""}}var u="Row not found.";return Logger.log(u),{success:!1,message:u}}function insertSingleRow(e,t,r,n){var a=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e),s=a.getLastRow()+1,o=a.getRange(s,1,1,a.getLastColumn()).getValues()[0],g=(rowToObject(a,o),getHeaders(a));for(let e=0;e<g.length;e++)console.log(g[e]),console.log(t[g[e]]),t[g[e]]&&a.getRange(s,e+1).setValue(t[g[e]]);Logger.log("Data updated successfully.");return{success:!0,message:""}}function upsertSingleRow(e,t,r,n){var a=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e),s=findFirstMatch(a,r,n);if(null!==s){var o=s.getRow(),g=a.getRange(o,1,1,a.getLastColumn()).getValues()[0],c=(rowToObject(a,g),getHeaders(a));for(let e=0;e<c.length;e++)console.log(c[e]),console.log(t[c[e]]),t[c[e]]&&a.getRange(o,e+1).setValue(t[c[e]]);Logger.log("Data updated successfully.");return{success:!0,message:""}}insertSingleRow(e,t,r,n)}function doGet(e){try{var t;if("read"===e.parameter.method){const r=e.parameter.sheetName,n=e.parameter.keyName;return(t=findRowByKeyValue(r,n,e.parameter.keyValue)).success,ContentService.createTextOutput(JSON.stringify(t)).setMimeType(ContentService.MimeType.JSON)}var r="Invalid method";Logger.log(r);var n={success:!1,message:r};return ContentService.createTextOutput(JSON.stringify(n)).setMimeType(ContentService.MimeType.JSON)}catch(e){r="Error processing the request:"+e.message;console.error(r);n={success:!1,message:r};return ContentService.createTextOutput(JSON.stringify(n)).setMimeType(ContentService.MimeType.JSON)}}function doPost(e){try{var t,r=JSON.parse(fixLineReturns(e.postData.contents));switch(console.log(JSON.stringify(r)),e.parameter.method){case"write":{const n=e.parameter.sheetName,a=e.parameter.keyName;return(t=updateSingleRow(n,r,a,e.parameter.keyValue)).success,ContentService.createTextOutput(JSON.stringify(t)).setMimeType(ContentService.MimeType.JSON)}case"update":{const n=e.parameter.sheetName,a=e.parameter.keyName;return(t=updateSingleRow(n,r,a,e.parameter.keyValue)).success,ContentService.createTextOutput(JSON.stringify(t)).setMimeType(ContentService.MimeType.JSON)}case"insert":{const n=e.parameter.sheetName,a=e.parameter.keyName;return(t=insertSingleRow(n,r,a,e.parameter.keyValue)).success,ContentService.createTextOutput(JSON.stringify(t)).setMimeType(ContentService.MimeType.JSON)}case"upsert":{const n=e.parameter.sheetName,a=e.parameter.keyName;return(t=upsertSingleRow(n,r,a,e.parameter.keyValue)).success,ContentService.createTextOutput(JSON.stringify(t)).setMimeType(ContentService.MimeType.JSON)}default:var n="Invalid method";Logger.log(n);var a={success:!1,message:n};return ContentService.createTextOutput(JSON.stringify(a)).setMimeType(ContentService.MimeType.JSON)}}catch(e){n="Error processing the request:"+e.message;console.error(n);a={success:!1,message:n};return ContentService.createTextOutput(JSON.stringify(a)).setMimeType(ContentService.MimeType.JSON)}}

If you are a developer, this code supports the following methods that we will use in subsequent steps.

6.1 Read

  • Takes in the following params
  • method=read
  • sheetName=
  • keyName=
  • keyValue=<case sensitive value of a cell in that col>

6.2 Update

  • Takes in the following params:
  • method=update
  • sheetName=
  • keyName=
  • keyValue=<case sensitive value of a cell in that col>
  • body=JSON without keyName : keyValue

6.3 Insert – Inserts a new row regardless of whether a row with the key already exists or not – Important to pass Key in the body

  • Takes in the following params:
  • method=insert
  • sheetName=
  • keyName=
  • keyValue=
  • body=JSON with keyName : keyValue

6.4 Upsert – Updates if a row with the Key exists, else Inserts a new row – Important to pass Key in the body

  • Takes in the following params:
  • method=insert
  • sheetName=
  • keyName=
  • keyValue=<case sensitive value of a cell in that col>
  • body=JSON with keyName : keyValue

Step 7: Click on Deploy button, then choose “New Deployment”

Click on Deploy and then new deployment. This will now start API read/write relationship between Google Sheets and Yonyx.

Step 8: Now choose Web app on the “New deployment” screen.

When installing Yonyx Google Sheets integration, you need to choose "Web App" on this screen.

Step 9: Now setup the Deployment screen as shown below:

Setup google sheets deployment screen to enable read/write access from Yonyx decision trees.
  • A – Name the Deployment V1 for Version 1.
  • B – Make sure you choose “Execute.as” as yourself (i.e. the owner of the Google Sheet).
  • C – Select “Anyone” for Who has access
  • Now click on Deploy button. It will take a few seconds to deploy.

Step 10: Now, a form will appear, requesting authorization to access. Please click on the button displayed. It will prompt you to select a Google Account. Please choose your account – specifically, the one that owns the Google Sheet.

Step 11: You will now see a warning message, “Google has not verified this app”. Click on Advanced

Step 12: Now click on “Go to Untitled Project – Unsafe”

Step 13: The screen below will pop-up. Click on Allow

Step 14: Copy the Web App URL as shown below. Keep it safe in your records. Now click on Done.

Step 15: Now click Edit under the guide called, “Read from Google Sheets”. You should find this guide under “My Guides” if you are the Administrator who signed up for your Yonyx subdomain. If not, and you are a Yonyx Admin, you will need to add yourself as a co-author on this guide from the Administration Console. Once the guide appears in Map View, click “x” to expand.

Step 16: Now edit the root node and scroll down in the interactive view (right hand section) to the “Set Placeholders” section. Set the value of the following placeholders in this root node.

  • api-url = Web App URL that you copied in Step 13 above.
  • unique-key-name = Column name of the unique key column – e.g. case-number, lead-id, phone-number etc.
  • number-of-retries = 1; This is the number of times you want your users to be able to retry reading from Google sheets, if the first attempt was unsuccessful.
Set placeholder values to configure Read from Google Sheets

Step 17: Now edit the next guidance step with the title, “HTTP Read from Google Sheets”. Scroll down in the interactive view (right hand) panel and click on the down arrow “v” to the right of “HTTP Request” function, to expand. Now scroll down all the way and configure the following:

  • In the left hand column, replace Column1 with the title of the first column – e.g. First_Name
  • In the right hand column, replace gs-1 with the placeholder (e.g. first-name) you created in step 4, corresponding this column.
  • Similarly configure all Google sheets columns to be read with its name and placeholder values.
  • Click on + sign next to a row in the screenshot shown below to add more rows.
Configure JSON fields from Google Sheets integration to be read into Yonyx Placeholders.

Step 18: Now click Edit under the guide called, “Write to Google Sheets”. You should find this guide under “My Guides” if you are the Administrator who signed up for your Yonyx subdomain. If not, and you are a Yonyx Admin, you will need to add yourself as a co-author on this guide from the Administration Console. Once the guide appears in Map View, click “x” to expand.

Step 19: Now edit the root node and scroll down in the interactive view (right hand section) to the “Set Placeholders” section. Set the value of the following placeholders in this root node.

  • number-of-retries = 1; This is the number of times you want your users to be able to retry writing to Google sheets, if the first attempt was unsuccessful.
Configure placeholder values for Write to Google Sheets.

Step 20: Now edit the next guidance step with the title, “HTTP Write to Google Sheets”. Scroll down in the interactive view (right hand) panel and click on the down arrow “v” to the right of “HTTP Request” function, to expand. Now scroll down half way and configure the body section as per the description below:

  • Replace Column1 with the title of the first column – e.g. First_Name
  • Replace gs-1 with the placeholder (e.g. first-name) you created in step 4, corresponding this column.
  • Similarly configure all Google sheets columns to be written back with their respective name and placeholder values.
  • Add as many rows to this field as required to support all columns to be written back.
Configure placeholders that need to be written back to Google Sheets

Step 21: Now that read and write to Google Sheets is configured, you can this functionality in any Yonyx interactive guide that you create.

To read from Google Sheets, add a handoff to the root node of a campaign guide.

  1. Click on Tools drop-down
  2. Choose Insert Handoff
  3. Search for Read
  4. Click on down arrow to insert the handoff
Adding a Handoff to "Read from Google Sheets" guide is all you need to do to start reading from Google Sheets.

Step 22: When a Handoff to “Read from Google Sheets” is inserted in the root node of a campaign guide, a code snippet gets inserted in the root node as shown below. This code snippet is not visible in the User View of the campaign guide. As soon as user starts traversing through the campaign guide, the user is transparently handed off to the “Read from Google Sheets” guide, where the placeholder values are updated based on the configuration set in steps 15. and 16 above.

Step 23: Likewise, include a Handoff to “Write to Google Sheets” in the campaign guide. This should be added at each guidance step where, upon user arrival, all placeholder values captured or updated up to that point are written back to Google Sheets.

Step 24: Now install Yonyx Google Chrome Extension and contact your Account Manager to obtain a key for the same.

Develop interactive decision trees for troubleshooting, cold calling scripts, medical appointments, or process automation. Enhance sales performance and customer retention across your call centers. Lower costs with customer self-service.

Start 30-day trial
Request a Demo
Interactive Decision Tree