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 Automation

This data will only be available if 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 Automation 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 Automations runs, send an email to yourself which contains the expression list (use the {core.expression} placeholder).

Building a script

Now that you have tested your global Automation, 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 for 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 Automation

Go back to babelforce manager app. Open the Automation you created before to send the email to yourself to test the core expressions. Now change the action to HTTP callback. Ensure 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:

Need a helping hand?

Related articles