Calendar App Script

gistfile1.txt

/**
 * This is based off of another gist I found but I honestly don't recall where. That script was old 
 * and didn't work with updates to the API. I'm confident that will happen to this script in the future.
 * This script will populate a spreadsheet with your meetings. Start by opening a Google spreadsheet and 
 * then go into App Scripts. You can copy paste this into there and then start making edits. The primary 
 * function to run is ProcessThisWeek. Have fun.
 *
 * BEGIN CONFIGS
 */
// Calendar to Search access. You can get this from "Settings & Sharing" page in calendar.
var gblCalendarName = "LDAP@DOMAIN";

// Excludes events you don't want to count e.g. do not schedule meetings or holds.
// Make sure it's a valid regex expression. 
var startsWithList = ['^\\[DNS\\]', '^\\[Optional\\]'];

// For the "ProcessFixedSpan" function. Put the dates you want to run here.
var gStartDate = new Date("03/21/2022");
var gEndDate = new Date("03/25/2022");

// Define your Time Zone
const gblTimeZone = "America/New York";

/**
 * Group Event Names by Regular Expressions
 * You can define as many groups as you want just "push" more "Names"/RegularExpressions into the array
 * gblRegExGrp.push(["Name of the Group",new RegExp('Regular Expression','i')]);
 * You can test your regular expression here for example: https://regex101.com/
 */
var gblRegExGrp = [];
//gblRegExGrp.push(["Events That Start with torn ",new RegExp('^torn','i')]);

/**
 * END CONFIGS
 */


// Free text search terms to find events that match these terms in any field, except for extended properties.
// Leave Blank "" to match all events in the Calendar
var gblEventQuery = "";

// Max number of Events to Match, By default the value is 250 events. max is 2500 events.
const MAX_EVENT_QUERY_RESULTS = 1000;

// Although more than 2500 events is possible this script does not handle multi-"page" Results.

// Define Number of hours to count for an All-Day Event
const gblAllDayHours = 8;

// Internally used global constat variables
const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

var gblOutput = [];

// Here are the headers definition for our sheet
gblOutput.push(["Event", "Start", "End", "Durration"]);

// A global variable for the totalizer of hours (Should be same size as previous)
var gblLastLine = ["Total", "", "", 0];

// Event Specific Totalizer
var gblEventTotal = {};

// The Event Output
var gblEventOutput = [];
gblEventOutput.push(["Same Event", "Total Days", "Total Hours", "Total Mins"]);

var gblRegexEventTotal = {};
var gblRegexEventOutput = [];
gblRegexEventOutput.push(["Regex Event", "Total Days", "Total Hours", "Total Mins"]);

var gblZeroDayHours = {};

// This is updated by the calendar search results to calendar Time Zone, if available
// Used for Allday events
var gblCalendarTimeZone = gblTimeZone;


// Sets start and end based on the current day of the week.
function ProcessWorkWeek() {
    var now = new Date();
    var today = now.getDay();
    var startDate;
    var endDate;

    switch(today) {
      case 0: {
        startDate = new Date(now.getTime() + MILLIS_PER_DAY);
        endDate = new Date(now.getTime() + (5 * MILLIS_PER_DAY));
        break;
      }
      case 1: {
        startDate = now;
        endDate = new Date(now.getTime() + (4 * MILLIS_PER_DAY));
        break;
      }
      case 2: {
        startDate = new Date(now.getTime() - (MILLIS_PER_DAY));
        endDate = new Date(now.getTime() + (3 * MILLIS_PER_DAY));
        break;
      }
      case 3: {
        startDate = new Date(now.getTime() - (2 * MILLIS_PER_DAY));
        endDate = new Date(now.getTime() + (2 * MILLIS_PER_DAY));
        break;
      }
      case 4: {
        startDate = new Date(now.getTime() - (3 * MILLIS_PER_DAY));
        endDate = new Date(now.getTime() + (MILLIS_PER_DAY));
        break;
      }
      case 5: {
        startDate = new Date(now.getTime() - (4 * MILLIS_PER_DAY));
        endDate = now;
        break;
      }
      case 6: {
        startDate = new Date(now.getTime() - (5 * MILLIS_PER_DAY));
        endDate = new Date(now.getTime() - (MILLIS_PER_DAY));
        break;
      }
    }
    startDate.setHours(0);
    endDate.setHours(23);
    fillMySheetWithCalendarEventDetails(
        gblCalendarName,
        gblEventQuery,
        startDate,
        endDate
    );
}

function ProcessToday() {
    var startDate = new Date();
    var endDate = new Date();
    startDate.setHours(0);
    endDate.setHours(23);
    
    fillMySheetWithCalendarEventDetails(
        gblCalendarName,
        gblEventQuery,
        startDate,
        endDate
    );
}

function ProcessFixedSpan() {
    fillMySheetWithCalendarEventDetails(
        gblCalendarName,
        gblEventQuery,
        gStartDate,
        gEndDate 
    );
}

var gblRegExpExclusions = [];
function addRegExp(item) {
  gblRegExpExclusions.push(new RegExp(item, 'i'));
}
startsWithList.forEach(item => addRegExp(item));

// API Ref: Sheet : https://developers.google.com/apps-script/reference/spreadsheet/sheet
// API Ref: Range : https://developers.google.com/apps-script/reference/spreadsheet/range
function fillMySheetWithCalendarEventDetails(myCalendarName, myEventName, myFromDate, myToDate) {

    var mySheet = SpreadsheetApp.getActiveSheet();
    Logger.log("%s <-> %s", myFromDate.toString(), myToDate.toString());
    var eventsInTime = getMyCalendarEventsInTime(myCalendarName, myEventName, myFromDate, myToDate);

    // Default Range : set to size required by Error Report, will be redfined if no "error".
    var range = mySheet.getRange(mySheet.getActiveCell().getRow(), mySheet.getActiveCell().getColumn(), 2, 1);
    if (eventsInTime.length > 0) {

        processDetailsFromMyEvents(eventsInTime);

        range = mySheet.getRange(mySheet.getActiveCell().getRow(), mySheet.getActiveCell().getColumn(), 1, 1);
        if (gblRegexEventOutput.length > 1) {
            gblRegexEventOutput.push(["", "", ""]); //Create an empty line after
            range = range.offset(range.getNumRows(), 0, gblRegexEventOutput.length, gblRegexEventOutput[0].length);
            range.setNumberFormat("General");
            range.clearFormat(); //Clears all except Number Formatting
            range.setValues(gblRegexEventOutput);
        }

        range = range.offset(range.getNumRows(), 0, gblEventOutput.length, gblEventOutput[1].length);
        range.setNumberFormat("General");
        range.clearFormat(); //Clears all except Number Formatting
        
        range.setValues(gblEventOutput);

        
        // Since most meetings will be less than a day, lets format them as a percent.
        // The total could be less than one.
        // TODO: Nice to have: see if I can apply a formula using a condition.
        prange = range.offset(1, 1, gblEventOutput.length - 2, 1);
        prange.setNumberFormat("#.#%"); 
        prange.clearFormat(); 

        // We offset the current range selection by its height and set the selection to next output
        range = range.offset(range.getNumRows() + 1, 0, gblOutput.length, gblOutput[0].length);
        range.setNumberFormat("General");
        range.clearFormat(); //Clears all except Number Formatting
        range.setValues(gblOutput);
        
    } else {
        range.setValues([
            ["Script Errors"],
            [Logger.getLog()]
        ]);
    }
}
//Pre: Requires an existing event
//Post: Returns an array of values
function getDetail1FromEvent(myEvent) {
    var myDetails = [];
    var myStartDate, myEndDate;
    var myDiff = {
        days: 0,
        hours: 0,
        mins: 0
    };
    var myStrDate = {
        start: "",
        end: ""
    };

    if (myEvent.getStartTime()) {
        // myEvent.start.date The date,in the format "yyyy-mm-dd", *iif* this is an all-day event.
        myStartDate = myEvent.getStartTime();
        myEndDate = myEvent.getEndTime();
        myDiff.days = Number.parseFloat(((myEndDate - myStartDate) / (1000 * 60 * 60 * 8)).toFixed(3));

        myDiff.hours = Number.parseFloat(((myEndDate.getTime() - myStartDate.getTime()) / (1000 * 60 * 60)).toFixed(2));
        myDiff.mins = Number.parseFloat(((myEndDate.getTime() - myStartDate.getTime()) / (1000 * 60)).toFixed(2));


        myStrDate.start = Utilities.formatDate(myStartDate, gblTimeZone, "MM/dd/YYYY");
        myStrDate.end = Utilities.formatDate(myEndDate, gblTimeZone, "MM/dd/YYYY");
    } else {

        myStartDate = myEvent.getStartTime();
        myEndDate = myEvent.getEndTime();

        myDiff.hours = Number.parseFloat(((myEndDate.getTime() - myStartDate.getTime()) / (1000 * 60 * 60)).toFixed(2));
        myDiff.mins = Number.parseFloat(((myEndDate.getTime() - myStartDate.getTime()) / (1000 * 60)).toFixed(2));


        myStrDate.start = Utilities.formatDate(myStartDate, gblTimeZone, "MM/dd/YYYY HH:mm");


        myDiff.days = Number.parseFloat(((myEndDate - myStartDate) / (1000 * 60 * 60 * 8)).toFixed(3));
        // Since % is the remainder little tweak to get the modulo
        // Discussion here : https://stackoverflow.com/questions/11720656/modulo-operation-with-negative-numbers
        if (myDiff.days < 0)
            myDiff.days += 7;

        // if myDiff .days = 1 & .hours < 24 --> Overnight Activity
        // An Overnight activity is counted as a day, there could be the case where there are multiple events on
        // startDay and/or endDay which would cause the day counter to indicate a day more than reality
        // if myDiff .days = 0 then we could have multiple events on that day
        // Here we keep track of hours accumulated each day for zero day events
        // We later go through this array of objects to increment the gblEventTotal day counter accordingly
        if (myDiff.days == 0) {
            var myTmp = Utilities.formatDate(myStartDate, gblTimeZone, "MM/dd/YYYY");
            if (!(myEvent.summary in gblZeroDayHours)) {
                gblZeroDayHours[myEvent.summary] = {};
            }
            if (!(myTmp in gblZeroDayHours[myEvent.summary])) {
                gblZeroDayHours[myEvent.summary][myTmp] = myDiff.hours;
            } else {
                gblZeroDayHours[myEvent.summary][myTmp] += myDiff.hours;
            }
        }

        if (myDiff.days > 0) {
            myStrDate.end = Utilities.formatDate(myEndDate, gblTimeZone, "MM/dd/YYYY HH:mm");
        } else {
            myStrDate.end = Utilities.formatDate(myEndDate, gblTimeZone, "HH:mm");
        }
    }

    // We Record the first matched Event Date as start for the last line
    if (gblLastLine[1] == "")
        gblLastLine[1] = Utilities.formatDate(myStartDate, gblTimeZone, "MM/dd/YYYY");
    // Logger.log('%s (%s) : %s hours', myEvent.getTitle(), Utilities.formatDate(myStartDate, gblTimeZone, "MM/dd/yyyy"), myDiff.hours);

    myDetails = [myEvent.getTitle(), myStrDate.start, myStrDate.end, myDiff.mins];

    //gblLastLine[3] is my hours Totalizer
    gblLastLine[3] += myDiff.mins;
    //This following gets overwritten with each new entry found
    gblLastLine[2] = Utilities.formatDate(myEndDate, gblTimeZone, "MM/dd/YYYY");

    // Here we keep track of sameName Event Totalizers
    if (!(myEvent.getTitle() in gblEventTotal)) {
        gblEventTotal[myEvent.getTitle()] = myDiff;
    } else {
        gblEventTotal[myEvent.getTitle()].days += myDiff.days;
        gblEventTotal[myEvent.getTitle()].hours += Number.parseFloat(myDiff.hours);
        gblEventTotal[myEvent.getTitle()].mins += Number.parseFloat(myDiff.mins);
    }

    return myDetails;
}
// Chcek : https://developers.google.com/calendar/v3/reference/events#resource for a list
function processDetailsFromMyEvents(myEvents) {
    Logger.log('number of events: ' + myEvents.length);
    if (myEvents && myEvents.length > 0) {
        if (myEvents.timeZone) gblCalendarTimeZone = myEvents.timeZone; //In String format, e.g. Europe/Madrid
        for (var i = 0; i < myEvents.length; i++) {
            var event = myEvents[i];
            var process = true;
            // check each exclusion regex to see if we should exclude the event.
            for (var j = 0; j < gblRegExpExclusions.length; j++) {
                var re = gblRegExpExclusions[j];
                if (re.exec(event.getTitle()) !== null) {
                    process = false;
                    break;
                }
            }
            if (process) {
                var details1 = getDetail1FromEvent(event);
                if (details1.length > 0) {
                    gblOutput.push(details1);
                }
            }
        }
        if (gblOutput.length > 1) {
            gblOutput.push(gblLastLine);

            // We fill the output array for the Event Totalizer
            for (const [key, value] of Object.entries(gblEventTotal)) {
                var myDays = value.days;

                //Here is where we count accumulated Zero Day hours on same day as a day
                if (key in gblZeroDayHours) {
                    // We need to iterate over every object just to count them.
                    for (const [myZkey, myZvalue] of Object.entries(gblZeroDayHours[key])) {
                        myDays++;
                    }
                }
                //We add the values to the output

                gblEventOutput.push([key, myDays, Number.parseFloat(value.hours), Number.parseFloat(value.mins)]);

                //Regexp Event Capture Totalizer
                for (const [grp, re] of gblRegExGrp) {
                    if ((key).match(re)) {
                        if (!(grp in gblRegexEventTotal)) {
                            gblRegexEventTotal[grp] = {
                                days: myDays,
                                hours: Number.parseFloat(value.hours),
                                mins: Number.parseFloat(value.mins)
                            };
                        } else {
                            gblRegexEventTotal[grp].days += myDays;
                            gblRegexEventTotal[grp].hours += Number.parseFloat(value.hours);
                            gblRegexEventTotal[grp].mins += Number.parseFloat(value.mins);
                        }
                    }
                }
            }
            var totalDays = 0;
            var totalHours = 0;
            var totalMins = 0;
            for (var i = 1; i < gblEventOutput.length; i++) {
              totalDays += Number.parseFloat(gblEventOutput[i][1]);
              totalHours += Number.parseFloat(gblEventOutput[i][2]);
              totalMins += Number.parseFloat(gblEventOutput[i][3]);
            }
            gblEventOutput.push(["Total", totalDays, totalHours, totalMins]);

            // We fill the output array for the Regex Event Totalizer
            for (const [key, value] of Object.entries(gblRegexEventTotal)) {
                //We add the values to the output
                gblRegexEventOutput.push([key, value.days, value.hours]);
            }
            Logger.log(gblRegexEventOutput);
        }
    } else {
        Logger.log('No events found.');
    }
}

// Details Here : https://developers.google.com/calendar/v3/reference/events/list
function getMyCalendarEventsInTime(myCalendarName, myEventName, myFromDate, myToDate) {
    Logger.log("%s <-> %s", myFromDate.toString(), myToDate.toString());
    Logger.log("looking for calendar " + myCalendarName);

    var calendars = CalendarApp.getCalendarsByName(myCalendarName);
    var myCalendar;
    if (calendars.length <= 0) {
        Logger.log("Could not find the clandar " + myCalendarName);
        Logger.log("You may not have access or are using the wrong calendar name.");
        return '';
    } else {
        myCalendar = calendars[0];
        var myEvents = myCalendar.getEvents(myFromDate, myToDate);
        return myEvents;
    }
}