Call data to Google sheets

You have the possibility to send all kind of data from a call to a Google sheet (or to any other online accessible database). This can be very useful for a number of reasons (they might be overlapping):

  • You do not have extensive reporting functionalities in your CRM or helpdesk
  • You do not want to have a ticket created for each call
  • You do not use any CRM or helpdesk
  • You want to avoid downloading and analyzing the babelforce reporting sheet
  • The reporting information provided by babelforce is not enough and you need a different more individualized set of data
  • All your reporting is done in Google sheets anyway and you want to have everything in one place

In this article, we will only focus on Google sheets, for other solutions the implementation will be different and you will have to build your own script.

Preparation

First, make a list of the data points you want to have in your sheet, for instance:

  • Date & Time
  • Call from number
  • Queue name
  • Call duration
  • Agent who took the call

What data is send by the HTTP request?

We have a standard set of

Building and testing Trigger and Event

This data will only be available when the inbound call is complete so you will have to create a trigger that fires under the following conditions:

  • Call type is Inbound
  • Call is complete
  • Call source is queue

Next, build a global event that fires on Call finished and that uses the trigger you built. To test if all the data you want to have pushed to your sheet is stored at the moment the event fires, send an email yourself containing the expression list (use the {core.expression} placeholder).

 

Building a script

Now that you have tested your global event, you have to build your script for Google sheets. We already prepared an easily adjustable blueprint below. You can freely add babelforce POST call data (these correlate with the core expressions) and insert them to the sheet.  

// this is a test function: visit the web app URL in the browser to check if it's working
function doGet(e) {
  return HtmlService.createHtmlOutput("Yep, it works!");
}

// this is a function that fires when the webapp receives call data
function doPost(e) {
  var params = JSON.stringify(e.postData.contents);
  params = JSON.parse(params);
  
  // define the data to put in our spread sheet
  
  var timestamp = new Date();
  var myData = JSON.parse(e.postData.contents);
  
  // babelforce POST call data specific fields
  var agent = myData.agent.name;
  var from = myData.call.from;
var queue = myData.queue.name;
var duration = myData.call.duration;
// get current sheet state var sheet = SpreadsheetApp.getActiveSheet(); var lastRow = Math.max(sheet.getLastRow(),1); // insert data sheet.insertRowAfter(lastRow); sheet.getRange(lastRow + 1, 1).setValue(timestamp); sheet.getRange(lastRow + 1, 2).setValue(agent); sheet.getRange(lastRow + 1, 3).setValue(from);
sheet.getRange(lastRow + 1, 4).setValue(queue);
sheet.getRange(lastRow + 1, 5).setValue(duration);
SpreadsheetApp.flush(); return HtmlService.createHtmlOutput("post request received"); }

There is one thing you need to know before adjusting your Google script. You cannot copy the expressions to the Json-file in all cases. Only data sent via the default buckets is received by Google in the standard babelforce format (e.g. call.duration). In case you don't know what these default buckets are, we suggest you read this article before continuing.

If you create a new Key Value that is not included in the default bucket on certain events, you must change the naming slightly in the Google Json-file. For instance, instead of myData.agent.presence you would add the field myData.data.presence in the section for babelforce POST call data specific fields. You simply replace the first part 

Add the script to Google sheets

Next, open a new Google sheet and name the first five rows, e.g.:

  • Date/Time
  • Agent
  • From
  • Queue
  • Duration (mill. secs) 

To add the script, go to Tool > Script editor.

Now insert your code, go to Publish > Deploy as web app. Allow access to anyone. 

Next, You will have to review the permissions and allow the script to access the sheet. After the permission was granted, you can test your code by clicking on the link "latest code". Copy the URL to your script.

Complete the babelforce event

Go back to babelforce manager app. Open the event you created before to send the email to yourself to test the core expressions. Now change the action to HTTP callback. Check that you selected the correct trigger and insert the URL from the google sheet. You may add a key value if you want, enable all event buckets.

Testing your setup

Now make a test call. If all goes well, after the call was hung-up there will be a log in your sheet that would have data included that resembles the data from the screenshot below:

 

Have more questions? Submit a request