Creating agent activity reporting

Most managers want to have an overview of their employee's activities. 

This article explains how you can use data that was pushed to Google Sheets via a http request to create reports that look something like this:

We will use some calculated fields and the beauty of it is: it's all 100% automated. You set it up once and it will work until your sheet runs out of cells.

And not only can you create a list view that shows you how long agents stayed in the available presence states, you can also run a pivot table that can be used as a dashboard. Since Google Sheet pivots are updated automatically you also don't need any manual work here.

Before we get started, make sure you are familiar with http requests. You find an extensive article here.

Building your JSON-file

Your JSON-File should at least contain the following two data points:

• Agent name
• Agent presence

For your first trial feel free to use our script:  

// 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 presence = myData.data.presence;
  
  // 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(agent);
  sheet.getRange(lastRow + 1, 2).setValue(timestamp);
  sheet.getRange(lastRow + 1, 3).setValue(presence);

  SpreadsheetApp.flush();
  return HtmlService.createHtmlOutput("post request received");
  
}

Important: to send and process agent presence in Google, you need to use the variable presence = myData.data.presence as this data point is not sent in a default bucket.

Always remember: if you have to adjust your JSON script, you need to delete the old Google project and create a new one. Unfortunately, Google cannot process these changes on the fly.

The babelforce HTTP Request

For your request, select the event "Presence changed" and the Action "HTTP callback". Copy the Google script URL to the Call-back URL field and create one Key-Value for the agent presence. The name of this variable must be the same as in your JSON file! Finally, select the bucket "Include Agent" and you're done! Send some data to your sheet.

Adjusting your sheet

You will quickly realize, if you try to add any calculated field into the sheet we push the data to, the next entry will appear under this calculated field. Google will not add the data to an existing cell but insert a new row. To create your fully automated sheet, you just need to do a couple of steps.

• Create a new sheet tab
• Enter the following formula to your new tab: =indirect("Sheet1!"&"A"&ROW(),true). This retreats the automatically pushed data from the other sheet tab. You need the "indirect" formula as Google is inserting new rows. A simple reference doesn't work as the formulas are dynamic.

• For the other columns just replace the letter "A" with "B" (in case these are the columns you are using), etc.
• Now we create a last column that calculates how long a presence state lasted. You can for instance use this formula: =vlookup(A2,A3:C1000,2,false)-B2. It's very simple - it searches for the next instance the name of this agent appears in the list. If it does it subtracts the the second time from the first and voila, you got your duration. If an agent has stayed in one presence state, you will see an ugly #N/A. Of course, there are many ways to improve the formula, for instance if you use errorif. Feel free to play around with it!