Skip to content

Instantly share code, notes, and snippets.

@skinsch

skinsch/Aggregate Spreadsheet Data By Week or Month.js

Forked from siliconvallaeys/Aggregate Spreadsheet Data By Week or Month.js
Created March 27, 2018 01:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save skinsch/2e4668046827158ec91e3313d031d9ae to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save skinsch/2e4668046827158ec91e3313d031d9ae to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @siliconvallaeys siliconvallaeys created this gist Mar 16, 2017.
    154 changes: 154 additions & 0 deletions Aggregate Spreadsheet Data By Week or Month.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,154 @@
    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();
    }