Call data to Google sheets

Christina Dechent
Christina Dechent
  • Updated

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.


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 =;
  var from =;
var queue =;
var 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 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:



Was this article helpful?




Please sign in to leave a comment.