Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save siliconvallaeys/2f74369c75038a398a33b77be32c2908 to your computer and use it in GitHub Desktop.
Save siliconvallaeys/2f74369c75038a398a33b77be32c2908 to your computer and use it in GitHub Desktop.
This Google App Script will aggregate call data from CallRail on a Google Sheet. You can use Zapier to populate data about individual calls on a Google Sheet. Then use this code to aggregate the individual call data by week or month. You may need to edit the code to match your column headers.
var AGGREGATION_RANGE = "month";
function updateAggregateData() {
var dataSheetName = "Raw Data";
var reportSheetName = "For Reports";
var timeAggregatedData = new Array();
var answeredCalls = 0;
var notAnsweredCalls = 0;
var totalDuration = 0;
var averageDuration = 0;
var totalCalls = 0;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var rawDataSheet = spreadsheet.getSheetByName(dataSheetName);
var reportSheet = spreadsheet.getSheetByName(reportSheetName);
var rawRows = rawDataSheet.getRange(1, 1, rawDataSheet.getLastRow(), rawDataSheet.getLastColumn());
var values = rawRows.getValues();
var headers = values[0];
for(var rowCounter = 1; rowCounter < rawDataSheet.getLastRow(); rowCounter++) {
var date = new Date(values[rowCounter][0]);
if(AGGREGATION_RANGE.toLowerCase().indexOf("month") != -1) {
var monthIndex = date.getMonth() + 1;
var year = date.getYear();
var timeKey = monthIndex + "/" + year;
} else if(AGGREGATION_RANGE.toLowerCase().indexOf("week") != -1) {
var weekIndex = date.getWeek();
var year = date.getWeekYear();
var timeKey = "week " + weekIndex + " of " + year;
}
if(!timeAggregatedData[timeKey]) {
timeAggregatedData[timeKey] = new Object();
timeAggregatedData[timeKey].totalCalls = 0;
timeAggregatedData[timeKey].answeredCalls = 0;
timeAggregatedData[timeKey].notAnsweredCalls = 0;
timeAggregatedData[timeKey].totalDuration = 0;
timeAggregatedData[timeKey].averageDuration = 0;
}
var thisCallDuration = 0;
for(var columnCounter = 1; columnCounter < rawDataSheet.getLastColumn(); columnCounter++) {
var headerName = headers[columnCounter];
var value = values[rowCounter][columnCounter];
// count total call duration
if(headerName.toLowerCase().indexOf("duration") != -1) {
timeAggregatedData[timeKey].totalDuration += value;
var thisCallDuration = value;
}
// count answered and unanswered calls
if(headerName.toLowerCase().indexOf("answered") != -1) {
if(value.toString().toLowerCase().indexOf("true") != -1) {
timeAggregatedData[timeKey].answeredCalls++;
} else {
timeAggregatedData[timeKey].notAnsweredCalls++;
}
}
} // end going through all fields for each call
// count total calls
timeAggregatedData[timeKey].totalCalls++;
// calculate average call duration by adding the most recent call
timeAggregatedData[timeKey].averageDuration = ( timeAggregatedData[timeKey].averageDuration * (timeAggregatedData[timeKey].totalCalls - 1) + thisCallDuration ) / timeAggregatedData[timeKey].totalCalls;
} // end going through each call
// parse through time keys and generate final data sheet
reportSheet.clear();
reportSheet.appendRow(["Date", "Total Calls", "Answered", "Not Answered", "Total Duration (sec.)", "Avg. Duration (sec.)"]);
for(var timeKey in timeAggregatedData) {
var rowData = new Array();
rowData.push(timeKey);
for(var headerName in timeAggregatedData[timeKey]) {
var value = timeAggregatedData[timeKey][headerName];
rowData.push(value);
}
reportSheet.appendRow(rowData);
}
}
// UI and Trigger functions
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Optmyzr Options')
.addItem('Make Public', 'makePublic')
.addSeparator()
.addSubMenu(ui.createMenu('Time Aggregation')
.addItem('Weekly', 'setTimeWeekly')
.addItem('Monthly', 'setTimeMonthly'))
.addToUi();
}
function onChange() {
updateAggregateData();
}
function setTimeWeekly() {
AGGREGATION_RANGE = "week";
SpreadsheetApp.getActive().toast('Your data will now be aggregated by week', "Settings Updated", 4);
updateAggregateData();
};
function setTimeMonthly() {
AGGREGATION_RANGE = "month";
SpreadsheetApp.getActive().toast('Your data will now be aggregated by month', "Settings Updated", 4);
updateAggregateData();
};
function makePublic() {
var id = SpreadsheetApp.getActive().getId();
var file = DriveApp.getFileById(id);
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.alert('You made this file viewable by anyone with the link. This makes it available for inclusion in Optmyzr reports.');
};
// Returns the ISO week of the date.
Date.prototype.getWeek = function() {
var date = new Date(this.getTime());
date.setHours(0, 0, 0, 0);
// Thursday in current week decides the year.
date.setDate(date.getDate() + 3 - (date.getDay() + 6) % 7);
// January 4 is always in week 1.
var week1 = new Date(date.getFullYear(), 0, 4);
// Adjust to Thursday in week 1 and count number of weeks from date to week1.
return 1 + Math.round(((date.getTime() - week1.getTime()) / 86400000
- 3 + (week1.getDay() + 6) % 7) / 7);
}
// Returns the four-digit year corresponding to the ISO week of the date.
Date.prototype.getWeekYear = function() {
var date = new Date(this.getTime());
date.setDate(date.getDate() + 3 - (date.getDay() + 6) % 7);
return date.getFullYear();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment