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.

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!