Updated: April 2018
In February 2016, we partnered with Pratham Books to bring the pure joy of a bedtime story to kids in some of the unprivileged parts of Delhi. What if we could just get these kids to give a missed call and get a call on which they can listen to a story?
What we did was to leverage the power of a simple phone call.
What started off as a pilot soon turned into an explosive success due to the response we received from the kids. We hoped to do 25,000 calls over a week – but we did 36,000 calls in 5 days! The enthusiasm of the kids made us want to understand more about what was happening.
We wanted to have a real-time view of things that were happening – who was calling in, what story were they were listening to, how many stories they listened to, how long they spent on a call, etc. We wanted to build a quick hack to make this happen. That led to our discovery that Google Sheets could be used extensively along with Exotel. The possibilities were endless.
Google App Scripts is one of the simplest tools to work with to simulate web requests. You can do some cool stuff with it and simple Spreadsheets!
Let’s get to it
Here’s a list of use-cases that we’ve identified that can be deployed using Exotel and Google Sheets:
- Missed call
- Capture DTMF codes entered by the user and store them against the customer’s
number - Customer feedback (where customer has to give a rating between 1 to 10)
- Dynamic Greeting
- Extension-based routing
- Condition-based routing with Switch Case applet
- Automated Call Campaign
- Automated Call Campaign and collect customer feedback/survey
- Find out the switch on/off duration for all your agents
This post lists down the scripts for some of the most common use-cases that we’ve come across.
Let’s begin. To start off, follow the steps given below:
1. Create or open a spreadsheet in Google Sheets.
2. Select the menu item Tools > Script editor. You will be presented with a new script file – give
it a project name and save it.
3. Delete any code in the script editor.
4. Select the menu item File > Save.
5. Enter the code for the specific use-case from this post.
6. Publish or deploy the script as a webapp.
Deploying the Script as a Webapp
1. Once the script is done, select the menu item Publish > Deploy as webapp. A window will
pop-up on your screen
2. Under Project Version type the name of the project
3. Under Execute the app as section, select Me (Email)
4. Under Who has access to the app, select Anyone, even anonymous. This helps make the webapp accessible to Exotel.
5. You can then click on Deploy to get the webapp URL, which can be used in the Exotel flow
Google’s official documentation has more on this: https://developers.google.com/apps-script/guides/web
1. Missed Call
The missed call use-case is relatively simple. The customer gives a missed call to an Exophone, and their number gets saved in a spreadsheet.
The code can also be used for other use-cases such as capturing the DTMF codes entered by the customer and any single entry use-case. This means using this script you will be able to capture a single parameter and write it into a spreadsheet.
How to implement the missed call use-case using Exotel and spreadsheets:
1. Open a new spreadsheet.
2. Upload the code below in the script editor of the spreadsheet.
3. Publish the code to get the webapp URL.
4. Once deployed, copy the webapp URL which will be used later in the Exotel flow.
5. Open your Exotel account and create the flow below in the App Builder.
Applets to be used:
- Passthru
- Hangup
Creating the flow:
1. Drag and drop the Passthru Applet.
2. In the Passthru applet, use the webapp URL that was created earlier.
3. Drag and drop the Hangup Applet.
4. Save the flow.
5. Assign an Exophone to the newly created flow. Click here to understand how to assign a
number to a flow.
//Identifying the spreadsheet using the spreadsheet ID
var ID = "18seDjkMgS4vG7KkYvYiCG8FeS8la6K3M6V_b00PUKIY"
var SHEET_NAME = "Sheet1"
//Nme of the specific sheet inside the spreadsheet where data to be dumped
//Nme of the specific sheet inside the spreadsheet where numbers are available
function doGet(e) {
var params = JSON.stringify(e.parameters);
var jsonMapping = JSON.parse(params)
var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME)
sheet.appendRow([jsonMapping["CallSid"][0], jsonMapping["From"][0],jsonMapping["CurrentTime"][0]])
return ContentService.createTextOutput(e.parameters)
}
//Identifying the spreadsheet using the spreadsheet ID var ID = "18seDjkMgS4vG7KkYvYiCG8FeS8la6K3M6V_b00PUKIY" var SHEET_NAME = "Sheet1" //Nme of the specific sheet inside the spreadsheet where data to be dumped //Nme of the specific sheet inside the spreadsheet where numbers are available function doGet(e) { var params = JSON.stringify(e.parameters); var jsonMapping = JSON.parse(params) var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME) sheet.appendRow([jsonMapping["CallSid"][0], jsonMapping["From"][0],jsonMapping["CurrentTime"][0]]) return ContentService.createTextOutput(e.parameters) }
2. Customer Feedback:
In this use-case, the customer gives a call and enters a DTMF code to rate the service. In this script, both the customer’s number and the DTMF codes are tabulated in the sheet.
This can also be repurposed for capturing multiple parameters and write them onto a spreadsheet.
How to implement customer feedback use-case using Exotel and spreadsheets:
1. Open a new spreadsheet.
2. Upload the below code in the script editor of the spreadsheet.
3. Publish the code to get a webapp URL.
4. Once deployed, copy the webapp URL which will be used later in the Exotel flow.
5. Open your Exotel account and create the flow below in the App Builder.
Applets to be used:
- Gather
- Passthru
- Hangup
Creating the flow:
1. Drag and drop the Gather Applet. The digits entered at the Gather applet will be captured
and tabulated in the spreadsheet.
2. Drag and drop the Passthru Applet.
3. Use the webapp URL, that was created earlier, in the Passthru applet.
4. Drag and drop the Hangup Applet.
5. Save the flow.
6. Assign an Exophone to the newly created flow. Click here to understand how to assign a number to a flow.
//Id of the spreadsheet
//for spreadsheet https://docs.google.com/spreadsheets/d/1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa"
// has id as 1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa
var ID = "1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa"
//Nme of the specific sheet inside the spreadsheet where data to be dumped
var SHEET_NAME = "number_sheet"
//Nme of the specific sheet inside the spreadsheet where numbers are available
function doGet(e) {
var params = JSON.stringify(e.parameters);
var jsonMapping = JSON.parse(params)
var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME)
sheet.appendRow([jsonMapping["digits"][0], jsonMapping["From"][0]])
return ContentService.createTextOutput(e.parameters)
}
//Id of the spreadsheet //for spreadsheet https://docs.google.com/spreadsheets/d/1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa" // has id as 1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa var ID = "1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa" //Nme of the specific sheet inside the spreadsheet where data to be dumped var SHEET_NAME = "number_sheet" //Nme of the specific sheet inside the spreadsheet where numbers are available function doGet(e) { var params = JSON.stringify(e.parameters); var jsonMapping = JSON.parse(params) var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME) sheet.appendRow([jsonMapping["digits"][0], jsonMapping["From"][0]]) return ContentService.createTextOutput(e.parameters) }
3. Dynamic Greeting
You can play a personalised greeting to your customer based on the incoming number. You’ll need to fill a spreadsheet with a list of customers’ numbers and the corresponding greeting URL (a WAV file) in the adjacent column. Then, open the script editor of that sheet and paste the below code. Whenever the webapp is triggered, (during the GET request) this code will be executed and the corresponding greeting for that calling number will be returned.
This code can be used for multiple other use-cases as well such as Extension-based routing, condition-based routing with Switch Case applet, etc. These use-cases can be achieved by changing the return value and playing around with the spreadsheet.
This is a read and write script – the data from spreadsheet is read, some logic applied, and an appropriate output returned. You can experiment with the script based on your requirements.
How to implement dynamic greeting use-case using Exotel and spreadsheets:
1. Open a new spreadsheet.
2. Based on the use-case enter the deciding factor in the first column and the corresponding
parameter to be returned in the second column.
For example: in the dynamic greeting use-case, the “From” numbers are to be
entered in the first column and the corresponding link to the greeting file is added in the second column.
3. Upload the code below in the script editor of the spreadsheet.
4. Publish the code to get a webapp URL.
5. Once deployed, copy the webapp URL which will be used later in the Exotel flow.
6. Open your Exotel account and create the flow below in the App Builder.
Applets to be used:
- Greeting
- Connect
Based on your requirement, add any other applet you need to continue the flow.
Creating the flow:
1. Drag and drop the Greeting Applet.
2. Paste the webapp URL that was created previously into the Type a text portion of the
Greeting applet.
3. Drag and drop the Connect applet.
4. Drag and drop any other applet you need.
5. Save the flow.
6. Assign an Exophone to the newly created flow. Click here to understand how to assign a
number to a flow.
Extension-based Routing:
Applets to be used:
- Gather
- Connect
Based on your requirement, add any other applet you need to continue the flow.
Creating the flow:
1. Drag and drop the Gather Applet.
2. Drag and drop the Connect Applet
3. Use the webapp URL that was created earlier in the Connect applet.
4. Drag and drop any other applet you need.
5. Save the flow.
Based on the extension entered by the user, the corresponding number in the spreadsheet will be returned by the URL which in turn will be called by Exotel. Assign an Exophone to the newly created flow. Click here to understand how to assign a number to a flow.
Condition-based Routing with Switch Case:
Applets to be used:
- Passthru
- Switch Case
Based on your requirement, add any other applet you need to continue the flow.
Creating the flow:
1. Drag and drop the Passthru Applet.
2. Use the webapp URL, that was created earlier, in the Passthru Applet.
3. Drag and drop the Switch Case Applet.
4. Drag and drop any other applet you need.
5. Save the flow.
Based on the requirement, specific actions can be set for various keywords predefined in the Switch Case applet (which is returned in the Passthru applet based on the ‘From’ number specified in the spreadsheet).
Assign an Exophone to the newly created flow. Click here to understand how to assign a number to a flow.
//Id of the spreadsheet
//for spreadsheet https://docs.google.com/spreadsheets/d/1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa"
// has id as 1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa
var ID = "1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa"
//Nme of the specific sheet inside the spreadsheet where data to be dumped
var SHEET_NAME = "number_sheet"
//Nme of the specific sheet inside the spreadsheet where numbers are available
function doGet(e) {
Var params = e.parameters;
var abc = e.parameters["digits"][0]
var output = retrieveData(abc)
return ContentService.createTextOutput(output)
}
//retrieveData : Function to retrieve data from sheet
function retrieveData(paramm) {
var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME)
var data = sheet.getDataRange().getValues();
for (var j in data) {
if (paramm == data[j][0]) {
return data[j][1]
}
}
return "NO DATA FOUND"
}
//Id of the spreadsheet //for spreadsheet https://docs.google.com/spreadsheets/d/1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa" // has id as 1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa var ID = "1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa" //Nme of the specific sheet inside the spreadsheet where data to be dumped var SHEET_NAME = "number_sheet" //Nme of the specific sheet inside the spreadsheet where numbers are available function doGet(e) { Var params = e.parameters; var abc = e.parameters["digits"][0] var output = retrieveData(abc) return ContentService.createTextOutput(output) } //retrieveData : Function to retrieve data from sheet function retrieveData(paramm) { var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME) var data = sheet.getDataRange().getValues(); for (var j in data) { if (paramm == data[j][0]) { return data[j][1] } } return "NO DATA FOUND" }
4. Automated Call Campaign
In this case, you’ll be presented with a spreadsheet with an extra menu tab (we’re calling it Exotel, but you can call it anything – but for this post that’s what we’re referring to it as).
All you’ll have to do is upload the list of numbers in the spreadsheet and navigate to menu item Exotel > Call all numbers. Additionally, if you’d like to do an automated call campaign and collect customer feedback conduct a survey, you can combine this and the customer feedback script that was mentioned earlier to get the customer’s responses also in the sheet.
How to implement automated call campaigns using Exotel and spreadsheets:
In this case, an automated call will be made to the given list of numbers in the spreadsheet and the required flow will be played to them. This flow has to be created in the Exotel dashboard.
1. Open a new spreadsheet.
2. Navigate to the script editor, upload the code, and save it (replace the Exotel SID, API token
and other parameters with your Exotel credentials!).
3. Open the spreadsheet, and populate the required numbers in the first column.
4. Select Call all numbers option from the Exotel tab on the spreadsheet.
The above-mentioned procedure connects the list of numbers in the spreadsheet to a predefined flow in the Exotel dashboard. The flow can be defined by mentioning the flow ID in this code:
//Id of the spreadsheet
//for spreadsheet https://docs.google.com/spreadsheets/d/1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa"
// has id as 1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa
var ID = "1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa"
//Nme of the specific sheet inside the spreadsheet where data to be dumped
var SHEET_NAME = "number_sheet"
//The id of the exotel call flow to with which calls may be made
var FLOW_ID = 1234
//The Exotel number with which calls has to be made
var EXOTEL_VN = "08907965331"
//The exotel account sid
var EXOTEL_SID = "spartansaccount"
//The exotel API token which you may find in the API section of https://my.exotel.in
var EXOTEL_TOKEN = "spartanstokenblahblahblah"
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("Exotel")
.addItem("Call All Numbers", "callCampaign")
.addToUi()
}
//callCampaign: Main function to call the numbers given in the sheet
function callCampaign() {
var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME)
var data = sheet.getDataRange().getValues();
for (j in data) {
makeCall("" + data[j][0]) //Calling the makeCall function that makes calls.
Logger.log("" + data[j][0])
}
}
function makeCall(number) { // Fuction to trigger a post request to Exotel endpoint with the
//required prameters
var data = {
"From": number,
"Url": "http://my.exotel.in/exoml/start/" + FLOW_ID,
"CallType": "trans",
"CallerId": EXOTEL_VN,
};
// var payload = JSON.stringify(data);
var headers = { //Defining all the headers for the post request
'Accept-language': 'en',
'accept': 'application/json',
'content-type': 'application/x-www-form-urlencoded',
"Authorization": "Basic " + Utilities.base64Encode(EXOTEL_SID + ":" + EXOTEL_TOKEN)
};
var url = "https://twilix.exotel.in/v1/Accounts/" + EXOTEL_SID + "/Calls/connect.json"
var options = {
'method': 'post',
'headers': headers,
'payload': data,
'muteHttpExceptions': true
};
var response = UrlFetchApp.fetch(url, options);
Logger.log(response.getContentText());
return response.getContentText()
}
//Id of the spreadsheet //for spreadsheet https://docs.google.com/spreadsheets/d/1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa" // has id as 1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa var ID = "1BsalnnI5_yBRke6brWk5G54Qwm5dgonN_YnLXJa" //Nme of the specific sheet inside the spreadsheet where data to be dumped var SHEET_NAME = "number_sheet" //The id of the exotel call flow to with which calls may be made var FLOW_ID = 1234 //The Exotel number with which calls has to be made var EXOTEL_VN = "08907965331" //The exotel account sid var EXOTEL_SID = "spartansaccount" //The exotel API token which you may find in the API section of https://my.exotel.in var EXOTEL_TOKEN = "spartanstokenblahblahblah" function onOpen() { SpreadsheetApp.getUi() .createMenu("Exotel") .addItem("Call All Numbers", "callCampaign") .addToUi() } //callCampaign: Main function to call the numbers given in the sheet function callCampaign() { var sheet = SpreadsheetApp.openById(ID).getSheetByName(SHEET_NAME) var data = sheet.getDataRange().getValues(); for (j in data) { makeCall("" + data[j][0]) //Calling the makeCall function that makes calls. Logger.log("" + data[j][0]) } } function makeCall(number) { // Fuction to trigger a post request to Exotel endpoint with the //required prameters var data = { "From": number, "Url": "http://my.exotel.in/exoml/start/" + FLOW_ID, "CallType": "trans", "CallerId": EXOTEL_VN, }; // var payload = JSON.stringify(data); var headers = { //Defining all the headers for the post request 'Accept-language': 'en', 'accept': 'application/json', 'content-type': 'application/x-www-form-urlencoded', "Authorization": "Basic " + Utilities.base64Encode(EXOTEL_SID + ":" + EXOTEL_TOKEN) }; var url = "https://twilix.exotel.in/v1/Accounts/" + EXOTEL_SID + "/Calls/connect.json" var options = { 'method': 'post', 'headers': headers, 'payload': data, 'muteHttpExceptions': true }; var response = UrlFetchApp.fetch(url, options); Logger.log(response.getContentText()); return response.getContentText() }
5. Downloading agent on/off information from Exotel
We get a lot of requests from our customers asking for agent on/off duration information in a downloadable format. While this isn’t one of the reports available on our system yet, we understand how this information can be of great use to companies that want to understand how their customer communication is working.
While this report is in the product pipeline, we wanted to figure a way to make it simple for our customers to download this information in the meanwhile.
Solution using email notification, GMail filters & Google Spreadsheets
Every time an agent turns themselves on or off, we send our customers an email notification. Using that email, a simple Gmail filter and sheets, this report can be generated anytime you want.
1. Go to the Exotel dashboard > Notification Settings > Alert me when an agent switches on/off (switch on)
2. Import Gmail filters (or you may create one as needed) to tag the alerts received from step 1 (by default, tag them as ‘Upload’)
3. Open Google Spreadsheets and label the columns as below. Rename the spreadsheet as required –
Email Subject (A1)
DateTime (B1)
Agent First Name (C1)
Agent Full Name (D1)
Status (E1)
Date (F1)
Time (G1)
4. Navigate to ‘Tools’ > Script editor & replace the existing code with the one mentioned below
function extractData(subject) {
var nameRegex = /(\S+) (\S+) has/;
stateRegex = /(ON|OFF)/
Logger.log(subject)
name = nameRegex.exec(subject)
state = stateRegex.exec(subject)
return {firstName : name[1] , lastName :name[2], state :state[1]}}
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
var label = GmailApp.getUserLabelByName("Upload");
var threads = label.getThreads();
for (var i=0; i<threads.length; i++)
{var messages = threads[i].getMessages();
for (var j=0; j<messages.length; j++)
{var sub = messages[j].getSubject();
var dat = Utilities.formatDate(messages[j].getDate() ,"IST", "M/dd/yyyy HH:mm:ss");
var details = extractData(sub);
ss.appendRow([sub, dat,details.firstName,details.firstName + " " + details.lastName,details.state,dat.split(' ')[0],dat.split(' ')[1]])
}threads[i].removeLabel(label);}}
function extractData(subject) { var nameRegex = /(\S+) (\S+) has/; stateRegex = /(ON|OFF)/ Logger.log(subject) name = nameRegex.exec(subject) state = stateRegex.exec(subject) return {firstName : name[1] , lastName :name[2], state :state[1]}} function myFunction() { var ss = SpreadsheetApp.getActiveSheet(); var label = GmailApp.getUserLabelByName("Upload"); var threads = label.getThreads(); for (var i=0; i<threads.length; i++) {var messages = threads[i].getMessages(); for (var j=0; j<messages.length; j++) {var sub = messages[j].getSubject(); var dat = Utilities.formatDate(messages[j].getDate() ,"IST", "M/dd/yyyy HH:mm:ss"); var details = extractData(sub); ss.appendRow([sub, dat,details.firstName,details.firstName + " " + details.lastName,details.state,dat.split(' ')[0],dat.split(' ')[1]]) }threads[i].removeLabel(label);}}
5. Save the script > Run it > Review / allow permissions (wait for the script to fully execute; it would timeout automatically after 6 mins.)
6. To automate the task, click on ‘Edit’ > ‘All your triggers’ & the set the events at a frequency with which it’s desired
You can refer to the following link to learn more about Google appscript here, use this GitHub link to access the code, or you call us on +91 8088 919 888.
Disclaimer: If you’re an Exotel customer and are interested in trying this out, you can talk to us to understand how you can implement this for your specific scenario. Please write to hello@exotel.in, or get in touch with your account manager to work out the specifics.