buildNewMonth.gs

/***************************************************************************    
 * Build-a-month  
 * Dan Oswalt 2016
 ***************************************************************************/

/***************************************************************************
 * Show Sidebar to get the Month, Year, and Date_Cell from user
 * (See NewMonthSidebar.html file)
 ***************************************************************************/
 
function showSidebar(filename) {
  var file = filename ? filename : 'NewMonthSidebar';
  var html = HtmlService.createHtmlOutputFromFile(file)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Settings')
      .setWidth(300);
  SpreadsheetApp.getUi()
      .showSidebar(html);
}

/***************************************************************************
 * Automatically create a time-based trigger (function called once newMonth
 * successfully completes)
 ***************************************************************************/

function createTimer() {
    ScriptApp.newTrigger("updateAllSheets")
             .timeBased()
             .everyHours(1)
             .create();
};

/***************************************************************************
 * Delete the time-based trigger (at end of month)
 ***************************************************************************/

function deleteAutoUpdateTrigger() {
    var triggers = ScriptApp.getProjectTriggers();
    Logger.log(triggers);
    ScriptApp.deleteTrigger(triggers[0]);
}

/***************************************************************************
 * For each day of the month: 
 * 1. make a duplicate of the proper weekday template sheet
 * 2. insert sheet at end of the line
 * 3. write out the sheet_name and date_cell
 ***************************************************************************/

function createScheduleSheetsFromTemplate() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var documentProperties = PropertiesService.getDocumentProperties(); 
  var month_name = documentProperties.getProperty("month_name");
  var year = documentProperties.getProperty("year");
  var date_cell = documentProperties.getProperty("date_cell");
  var month_names = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];
  var weekday_names = ["SUN","MON","TUE","WED","THU","FRI","SAT"];
  var month_index = month_names.indexOf(month_name);
  var date = new Date(year, month_index, 1);
  var weekday = date.getDay();
  var month_num = month_names.indexOf(month_name);
  var days_in_month = new Date(year, month_num + 1, 0).getDate();
  
  ss.toast(month_name + " " + year + ": Creating a sheet for each day from the weekday templates.", "Build-A-Month", 600000);
  
  //generate each day from template
  for(var day = 1; day <= days_in_month; day += 1) {
      ss.insertSheet(weekday_names[weekday] + " " + month_name + " " + day, day + 6, {template: sheets[weekday]})
        .getRange(date_cell)
        .setValue(date);
      
      //increment date and weekday
      date = new Date(year, month_index, day + 1);
      weekday = weekday === 6 ? 0 : weekday += 1;
  }
  
  deleteTemplates();
  ss.toast(month_name + " " + year + ": Sheet creation success! Templates removed. Importing events from Google Calendars (this may take up to a minute)", "Build-A-Month", 600000);
  updateAllSheets();
  createTimer();
  ss.toast(month_name + " " + year + ": Success! This sheet is now live, and will update agendas hourly.", "Build-A-Month", 600000); 
  
};

function deleteTemplates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for(var i = 0; i < 7; i++) {
    ss.deleteSheet(sheets[i]);
  }
};

function buildNewMonth(month_name, year, date_cell) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var documentProperties = PropertiesService.getDocumentProperties(); 
  documentProperties.setProperties({ 
                                     month_name: month_name, 
                                     year: year, 
                                     date_cell: date_cell,
                                     first_half_finished: false
                                  });
                                  
  try { 
    createScheduleSheetsFromTemplate();                                 
  } catch (e) {
    
    //This is a known very annoying issue that just started happening in 2015.
    //Google will stop and throw a completely non-descript 'Service Error' after the 30 or 31 sheet that it duplicates.
    //Workarounds like breaking the work up into bits and calling them separately don't resolve the issue.
    //User will just need to finish the process manually be making the remaining days by hand, then deleting the templates, 
    //and starting the hourly auto-import trigger. -do
    //
    //https://code.google.com/p/google-apps-script-issues/issues/detail?id=3009
    //http://stackoverflow.com/questions/38903977/optimizing-google-scripts-inserting-sheets-from-template-getting-service-error
    ss.toast("Google quit before the script finished! Please visit the help manual for 'Known NewMonth Script Error' to finish the process. Schedule Tools > Help!", "Build-A-Month", 600000);
  }
  
  return true;
    
};

findChanges.gs

/***************************************************************************    
 * Build-a-month  
 * Dan Oswalt 2016
 ***************************************************************************/
 
 /***************************************************************************    
 * Loop through each day to see if the FLAG_CELL has an '*'. If yes, log the date
 * in a message.
 ***************************************************************************/

function findChanges() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();  
  var msg = '';
  
  sheets.forEach(function(sheet, index) {
    if(sheet.getRange(FLAG_CELL).getValue()) {
      msg += (index + 1) + ', ';
    }
  });
  
  msg = msg.substr(0, msg.lastIndexOf(",")); 
  
  if(msg.length === 0) {
    msg = "No changes found"
  };
  
  ss.toast(msg, "Dates with possible updates:", 600000);
    
};

NewMonthSidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <style>
    .container {
      margin: 0 10px;
    }
  </style>
  <body>
    <div class='container'>
      <h1>Build A Month</h1>
      <p>Please select the month, year, and the cell on the sheet where the date will be located</p>
      <div class="inline form-group">
        <label for="select">Month</label>
        <select id="month">
          <option selected>Jan</option>
          <option>Feb</option>
          <option>Mar</option>
          <option>Apr</option>
          <option>May</option>
          <option>Jun</option>
          <option>Jul</option>
          <option>Aug</option>
          <option>Sep</option>
          <option>Oct</option>
          <option>Nov</option>
          <option>Dec</option>
        </select>
      </div>
      <div class="inline form-group">
        <label for="select">Year</label>
        <select id="year">
          <option selected>2016</option>
          <option>2017</option>
          <option>2018</option>
          <option>2019</option>
          <option>2020</option>
          <option>2021</option>
          <option>2022</option>
          <option>2023</option>
          <option>2024</option>
          <option>2025</option>
          <option>2026</option>
          <option>2027</option>
          <option>2028</option>
          <option>2029</option>
          <option>2030</option>
        </select>
      </div>
      <div class="inline form-group">
        <label for="state">Date Cell</label>
        <input type="text" id="date_cell" style="width: 40px;" value="c1">
      </div>
      <br>
      <br>
      <button class="create">Create</button>
    </div>
  </body>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <script>
  $(function(){
  
    function newMonth() {
      var month_name = $('#month').val();
      var year = parseInt($('#year').val());
      var date_cell = $('#date_cell').val();
      google.script.run
        .withSuccessHandler(google.script.host.close)
        .withFailureHandler(function(msg,element){
          console.log("failed"); 
        })
        .buildNewMonth(month_name, year, date_cell);
    };    
    
    $('.create').on('click', function(){      
      newMonth();
    });

  });
    
  </script>
</html>

onOpen.gs

/***************************************************************************    
 * Build-a-month  
 * Dan Oswalt 2016
 ***************************************************************************/

/***************************************************************************
 * -If this file is the Master, a message will slide up to remind to make a copy. (Custom menus will be hidden).
 *
 * -If this file is a copy, but the buildNewMonth function hasn't run (i.e. it just has the 7 empty template sheets), then show
 *     an instructional reminder, and show the temporary New Month menu option.
 *
 * -If this file is a copy and the buildNewMonth function has been run, the scheduler tools menu will display, the New Month
 *     option will disappear.
 ***************************************************************************/

function onOpen() {

    gotoCurrentDay();
    
    var ui = SpreadsheetApp.getUi();
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var filename = ss.getName();
    var num_sheets = ss.getNumSheets();
    
    //if file is Master
    if(filename === "NWL Build-a-Month"){
        ss.toast("To get started, go to File > Make a copy", "Build-A-Month", 600000);
    
    //if file is not Master
    } else {    
  
    ui.createMenu("Schedule Tools")
      .addItem("Refresh today's agendas", "updateActiveSheet")
      .addItem("Refresh this month's agendas", "updateAllSheets")
      .addItem("Check for dates with changes", "findChanges")
      .addSeparator()
      .addItem("Add event to staff cal", "popIntoStaffCal")          
      .addItem("Add event to volunteers cal", "popIntoVolCal")
      .addItem("Add event to meeting room cal", "popIntoMtgRmCal")
      .addSeparator()           
      .addItem("Edit title of selected event", "modifyTitle")
      .addItem("Edit duration", "modifyDuration")
      .addItem("Delete event", "deleteEvent")
      .addSeparator()       
      .addItem("Redraw center line", "redrawCenterLine")
      .addItem("Obliterate range", "clearEverything")
      .addSeparator()       
      .addItem("Help!", "helpManualUrl")
      .addItem("Delete Templates", "deleteTemplates")
      .addItem("Start automatic import", "createTimer")
      .addToUi();    
    };
    
    //if buildNewMonth() has not been run
    if(filename !== "NWL Build-a-Month" && num_sheets === 7) {
        ui.createMenu("New Month")
          .addItem("Build New Month", "showSidebar") 
          .addToUi();      
        ss.toast("Make sure weekday templates are correct, then select 'New Month' from the menu bar above. You may be prompted to authorize the script.", "Build-A-Month", 600000);
        
    };

};

// On open, makes the sheet of the current date the active sheet (if applicable) 

function gotoCurrentDay() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var current_day = new Date().getDate();
    var current_month = new Date().getMonth();
    var date = sheets[0].getRange(DATE_CELL).getValue();  
    var month = new Date(date).getMonth();    

    if(month === current_month) {
        var sheet = sheets[current_day - 1];
        sheet.activate();
    };
  
}

//Show a message box with URL (Google doesn't allow link to url directly from menu)

function showURL(href){
  var ss = SpreadsheetApp.getActive();
  var app = UiApp.createApplication().setHeight(50).setWidth(200);
  app.setTitle("Help manual link:");
  var link = app.createAnchor("https://docs.google.com/document/d/1ZZAYQ_CYVpkTR66P9l7wRx1gWITMafxfUSRizMAK6tc/edit", href).setId("link");
  app.add(link);  
  ss.show(app);
}

function helpManualUrl(){
  showURL("https://docs.google.com/document/d/1ZZAYQ_CYVpkTR66P9l7wRx1gWITMafxfUSRizMAK6tc/edit")
}

OtherTools.gs

/***************************************************************************    
 * Build-a-month  
 * Dan Oswalt 2016
 ***************************************************************************/

function redrawCenterLine() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var bottom_row = vlookup(sheet,1,"Volunteers") + 1;
    var line_length = bottom_row - 3;
    var line_range = sheet.getRange(3,5,line_length,1);
    
    line_range.setBackground("#c27ba0");
      
}

function clearEverything() {

    var selection = SpreadsheetApp.getActiveRange();
    
    selection.clearContent();
    selection.setBackground("#ffffff");
    redrawCenterLine();

}

popIntoCal.gs

/***************************************************************************    
 * Build-a-month  
 * Dan Oswalt 2016
 ***************************************************************************/
 
 /***************************************************************************    
 * Function calls from 'Schedule Tools' menu 
 ***************************************************************************/

function popIntoStaffCal() {
    popInEvent("multco.us_3236353735383435393736@resource.calendar.google.com");
}

function popIntoVolCal() {
    popInEvent("multco.us_3231323136303830383832@resource.calendar.google.com");
}

function popIntoMtgRmCal() {
    popInEvent("multco.us_32383534383832322d373036@resource.calendar.google.com");
}

function deleteEvent() {
    modifyEvent("delete");
}

function modifyTitle() {
    modifyEvent("title");
}

function modifyDuration() {
    modifyEvent("duration");
}


function popInEvent(cal) {

    var ss = SpreadsheetApp.getActiveSpreadsheet(),
        sheet = ss.getActiveSheet(),
        sheet_date = sheet.getRange(DATE_CELL).getValue(),
        duration_string = "",
        sub_note = "",
        title = "";
    
    title = Browser.inputBox("Event Title", "", Browser.Buttons.OK_CANCEL);    
    if (title === "cancel") {return};
    
    duration_string = Browser.inputBox("Event Duration", "Start time - end time (Leave blank if no times needed)", Browser.Buttons.OK_CANCEL);    
    if (duration_string === "cancel") {return};
    
    createEventWithDurationString(duration_string,cal,title,sheet_date);  
    updateActiveSheet(); 

}

 /***************************************************************************    
 * parse the user time string and try to create valid date
 * if user omits info like 'am' or 'pm', this will make a reasonable assumption
 ***************************************************************************/

function makeValidTime(string, date) {
       
    string = string.trim().toUpperCase();
    var pmIndex = string.indexOf("P");
    var amIndex = string.indexOf("A");
    var semicolon_index = string.indexOf(":");
    var hasSemicolon = semicolon_index === -1 ? false : true;
    var noAmPm = false;
    var pm = true;
    var hours = -1;
    var minutes = 0;    
    
    //if no A or P, then no am/pm text has been entered, we will assume all times are pm except 9, 10, 11
    //if an A has been entered, set pm to false and trim off the AM text
    //else, assume PM
    
    if(pmIndex === amIndex) {
        noAmPm = true;
    } else if (amIndex !== -1) {
        pm = false;
        string = string.substr(0,amIndex);
    } else {
        string = string.substr(0,pmIndex);
    }
    
    //extract the actual hour by using the    
    if(hasSemicolon) {        
        hours = parseInt(string.substr(0,semicolon_index));       
    }
    
    hours = parseInt(string);
    
    //if the user hasn't set am or pm, hours 9-11 will be assumed to be am.    
    if(noAmPm) {    
        if(hours === 9 || hours === 10 || hours === 11) {
            pm = false;
        }
    }    
    
    //make it military time
    if(pm) {
        if(hours !== 12) {
            hours += 12;
        }    
    } else {
        if(hours === 12) {
            hours = 0;
        }
    };
   
    if(hasSemicolon) {       
        minutes = parseInt(string.substr(semicolon_index + 1, semicolon_index + 3));
    };
    
    date.setHours(hours);    
    date.setMinutes(minutes);
    
    return date;

}

 /***************************************************************************    
 * deleteEvent() tries to match the text in the active cell with an event in the calendar
 * on that day.  If it finds it, and it is not a recurring event, it will delete from the sheet
 * and the google calendar.
 *
 * It will apprpriately delete a 'sub' event from the sheet and remove the bracketed substring from title of
 * the event in the staff calendar (cal1)
 ***************************************************************************/

function modifyEvent(modification) {
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var date = sheet.getRange(DATE_CELL).getValue();
    var active_cell = sheet.getActiveCell();
    var string_to_search = active_cell.getValue();
    var substring_index = -1;
    var wasFound = false;
    var replaceEvent = modification === "title" || modification === "duration" ? true : false;
    var deleteEvent = modification === "title" || modification === "duration" ? true : false;
    var events = [];
    var titles = [];
    var recurs = [];
    var ids = [];    
    var cal1_events = CalendarApp.getCalendarById("multco.us_3236353735383435393736@resource.calendar.google.com").getEventsForDay(date, {search: "-nolist"});
    var cal2_events = CalendarApp.getCalendarById("multco.us_32383534383832322d373036@resource.calendar.google.com").getEventsForDay(date, {search: "-nolist"});
    var cal3_events = CalendarApp.getCalendarById("multco.us_3231323136303830383832@resource.calendar.google.com").getEventsForDay(date, {search: "-nolist"});
    
    events = cal1_events
    events.push.apply(events, cal2_events);
    events.push.apply(events, cal3_events);
    
    Logger.log("Modification: " + modification);
    Logger.log("Replace: " + replaceEvent);
    Logger.log("Delete: " + deleteEvent);
    Logger.log("Length: " + events.length);
    
    //search each title string for match in string_to_search
          
    for(var i = 0; i < events.length; i++) {

        substring_index = string_to_search.indexOf(events[i].getTitle());
        wasFound = substring_index !== -1 ? true : false;
        Logger.log("Search for " + events[i].getTitle() + " within " + string_to_search); 
        Logger.log("First Search, found? " + wasFound);
        Logger.log("Replace: " + replaceEvent);
        //if title is not in string_to_search, try looking for string_to_search in the title
        //if found here, then "deleting" is actually modifying the original title
        
        if(wasFound === false) {
            
            var trimmedString = string_to_search.substr(2, string_to_search.length);
            substring_index = events[i].getTitle().indexOf(trimmedString);
            wasFound = substring_index !== -1 ? true : false;
            
            if(wasFound) {
                replaceEvent = true;
            }    
            
            Logger.log("Search for " + string_to_search + " within " + events[i].getTitle());
            Logger.log("Second Search, was it found? " + wasFound);
            Logger.log("Replace: " + replaceEvent);
        }
    
        //if the title is found in the string, check if recurring
        if(wasFound) {
            
            //if it is recurring, give up and tell user
            if(events[i].isRecurringEvent()) {
                Browser.msgBox("Instances of recurring events can only be deleted in the google calendar app.");
                return;
                
            } else {
                
                //make sure we are deleting without replacing                
                if(modification === "delete") {
                    var response = Browser.msgBox("Delete Event", "Delete this event from sheet and google calendar?\\n\\n" + string_to_search, Browser.Buttons.OK_CANCEL);
                    deleteEvent = response === "ok" ? true : false;
                }
                
                //if we are just deleting this sheet:
                if(deleteEvent === true && replaceEvent === false) {
                    Logger.log("Delete and do not replace.");
                    events[i].deleteEvent();
                    updateActiveSheet();
                    return;
                
                //if we are deleting and replacing the event:
                
                } else if (deleteEvent === true && replaceEvent === true) {

                    //grab the title, slice out the substring, trim
                    Logger.log("User says delete it and replace it.");
                    var old_title = events[i].getTitle();
                    var new_title;
                    var new_duration_string;
                    var isAllDay = events[i].isAllDayEvent();
                    var cal = events[i].getOriginalCalendarId();
                    
                    if(modification === "delete") {
                        Logger.log("Remove any bracketed substring.");
                        var bracketed_substr = "[" + string_to_search + "]";
                        new_title = old_title.replace(bracketed_substr,"").trim();                    
                    }
                    
                    if(modification === "title") {
                        Logger.log("Change the title.");
                        new_title = Browser.inputBox("Change Title",old_title, Browser.Buttons.OK_CANCEL);
                        
                        if(new_title === "cancel") {return;}

                        if(new_title === "") {
                        Logger.log("No title left, assume deletion");
                            events[i].deleteEvent();
                            updateActiveSheet();
                            return;                    
                        };
                        
                        if(isAllDay) {                        
                            CalendarApp.getCalendarById(cal).createAllDayEvent(new_title, date);                        
                        } else {                       
                            CalendarApp.getCalendarById(cal).createEvent(new_title, events[i].getStartTime(), events[i].getEndTime());                       
                        }                        
                    }
                    
                    if(modification === "duration") {
                       Logger.log("Change duration");
                       new_duration_string = Browser.inputBox("Duration", "Start time - end time (Leave blank if no times needed)", Browser.Buttons.OK_CANCEL);
                       
                       if(new_duration_string === "cancel") {return;}
                       
                       new_title = events[i].getTitle();
                       createEventWithDurationString(new_duration_string,cal,new_title, date);  

                    };

                    Logger.log("Delete Event");
                    events[i].deleteEvent();
                    updateActiveSheet();
                    return;
                
                //if the user selects cancel and/or deleteEvent is false:
                } else {                
                    return;               
                }
            }//end 
        }//end condition loop
    }
    
    //if all events are searched both ways and there is no match, let the user know    
    Browser.msgBox("Error: Couldn't find the event in the calendar"); 
    
}

 /***************************************************************************    
 * create event with a properly formatted date, call to Calendar service
 ***************************************************************************/

function createEventWithDurationString(duration_string,cal,title,sheet_date) {

    //if duration_string is empty, simply create an all day event
    if (duration_string === "") { 
    
        CalendarApp.getCalendarById(cal).createAllDayEvent(title, sheet_date);
    
    //if duration_string has text, parse for valid dates and then create an event if successful
    } else {
        
        //split into two time strings, find hyphen first
        var hyphen_index = duration_string.indexOf("-");
        
        //if no hyphen, give up
        if(hyphen_index === -1) {
            Browser.msgBox("Invalid start and end times, must be separated by a hyphen. Try, again.");
            return;
        };
        
        //split it up
        var start_time_string = duration_string.substr(0,hyphen_index);
        var end_time_string = duration_string.substr(hyphen_index + 1, duration_string.length);
        
        //make a valid time object
        var start_time = makeValidTime(start_time_string, sheet_date);
        
        //why am i having to redefine the date? revisit this?
        sheet_date = new Date(sheet_date);
        
        var end_time = makeValidTime(end_time_string, sheet_date);

        CalendarApp.getCalendarById(cal).createEvent(title, start_time, end_time);
        
    }
    
} 
  

updateAgendas.gs

/***************************************************************************    
 * Build-a-month  
 * Dan Oswalt 2016
 ***************************************************************************/

//Global Variables, placed here to be with other configurations

var DATE_CELL = "C1";
var WEEKDAY_CELL = "A1";
var FLAG_CELL = "G1";
var ERROR_CELL = "H1";

///////////////////////////*Configuration for agenda ranges below*////////////////////////////////                                      
// 
// Create an AgendaRange instance for each range on the sheet that will have events imported.
//
// source : Google Calendar ID (this is found in its Calendar Settings > Calendar Address.)
// header : The title of the range; agenda items will be set directly underneath.       
// column : The column number where the agenda items will be set.                               
// rows : The number of rows in that one-column range; the likely maximum # of agenda items.
// notify : True/false; denotes whether changes in the schedule should be 'flagged' for the scheduler
// 
// Each AgendaRange has a process() method that takes a source, sheet, and a day as required paramaters 
// and returns an array of unformatted Calendar Event objects.
// 
// The process() method does several things in the meantime though:
//
// 1. Gets an array of Calendar Event objects, either directly from a call to Google Calendars
//    App with getEventsForDay for the Google Calendar ID listed as the source, or from an   
//    array of Calendar Event objects.
//
// 2. Formats the Google Calendar Events by adding title with times.
//
// 3. Locates the range to write the array of agenda items into by the value in 'header'.
//    (This relies on ArrayLib.find, from imported library called 'ArrayLib')
//
// 4. The 'column' and 'rows' values determine the index and size of AgendaRange's actual range.
//    This range is cleared of all values, and rewritten over with the new agenda, even if it
//    is the same as the old agenda.
//
///////////////////////////////////////////////////////////////////////////////////////////////

    /* Example: 
    
    var AgendaRange = new AgendaRange(source,
                                    header,
                                    column,
                                    rows
                                    notify); */
                                    
function updateAgendaRangesForSheet(sheet, day) {                        

    var AgendaRange1 = new AgendaRange("multco.us_3231323136303830383832@resource.calendar.google.com",
                                     "Volunteers",
                                     1,
                                     5,
                                     false);
                                     
    AgendaRange1.process(AgendaRange1.source, sheet, day, AgendaRange1.notify);  
                                 
    var AgendaRange2 = new AgendaRange("multco.us_32383534383832322d373036@resource.calendar.google.com",
                                     "Events",
                                     6,
                                     5,
                                     false);
                                     
    AgendaRange2.process(AgendaRange2.source, sheet, day, AgendaRange2.notify);                                     
                                 
    var AgendaRange3 = new AgendaRange("multco.us_3236353735383435393736@resource.calendar.google.com",
                                     "Staff",
                                     10,
                                     10,
                                     true);
                                     
    AgendaRange3.process(AgendaRange3.source, sheet, day, AgendaRange3.notify);

    var AgendaRange4 = new AgendaRange(AgendaRange3.events,
                                     "Subs/SWAT", 
                                     1,
                                     4,
                                     true);
                                     
    AgendaRange4.process(AgendaRange4.source, sheet, day, AgendaRange4.notify);
    
    var AgendaRange5 = new AgendaRange("multco.us_vi7t7tpp0169s6mn7vm9cpk4r4@group.calendar.google.com",
                                     "SRP Volunteers",
                                     6,
                                     5,
                                     false);
                                     
    //To use SRP calendar, delete the 'comment' slashes in front of the next line                                 
                                     
    //AgendaRange5.process(AgendaRange5.source, sheet, day, AgendaRange5.notify);
}

//Constructor for new AgendaRanges

function AgendaRange(source, header, column, rows, notify) {

    this.source = source;
    this.header = header;
    this.column = column;
    this.rows = rows;
    this.notify = notify;
    
    this.process = function(source, sheet, day, notify) {
    //1. Collect calendar events
    
        //check if source is an array or string.  If from an array, just call that array events
      
        if (typeof source === "object") {
        
            var events = source;
            var isFromArray = true;
        
        //otherwise, the source should be the Google ID, collect the events with this CalendarApp call.
        //the {search: } part of the getEventsForDay method can filter which events it returns.
        //for example: {search: "-nolist"} will not return events that have the string 'nolist' in the title
          
        } else {    
    
            var events = CalendarApp.getCalendarById(source).getEventsForDay(day, {search: "-nolist"});
            var isFromArray = false;        
        };
        
        this.events = events;
      
    //2. Take those raw event objects and turn them into a list of relevant, readable agenda items
      
        var agenda = createFormattedAgenda(events, day, isFromArray);
        
        //define the correct range by finding the row the header is on (the header row can vary
        //because the floor schedule is editable; rows could have been added or subtracted) 
        
        var index_of_header = vlookup(sheet, this.column, this.header);
                  
        //if vlookup returns -1, it couldn't find the header, so it will not try to write to sheet  
     
        if(index_of_header === -1) {
        
            var err = "One or more agendas could not be updated; make sure the agenda's header is correct";
            var error_cell = sheet.getRange(ERROR_CELL);
            error_cell.setValue(err);
        
        return;
        }
        
        var row_under_header = index_of_header + 2;          
        var whole_range = sheet.getRange(row_under_header, this.column, this.rows, 1);
                
    //3. Check for changes, set flag 
    
        //check if we want to flag this agenda  
        if(notify === true) {
        
            //check if flag is up
            var flag;
            if(sheet.getRange(FLAG_CELL).getValue() === "*") {flag = true} else {flag = false};
        
            //if no flag, look for items
            if (flag === false) {
           
                //grab old agenda; this includes blank cells
                var old_agenda = whole_range.getValues();
                var len = 0;
            
                for(var j = 0; j < old_agenda.length; j++) {
                    if (isEmpty(old_agenda[j][0]) === false) {
                        len++;
                    };    
                };
            
                old_agenda.length = len;
            
                //.equals extension, revisit this later
            
                Array.prototype.equals = function (array) {
                // if the other array is a falsy value, return
                if (!array)
                    return false;
    
                // compare lengths - can save a lot of time 
                if (this.length != array.length)
                    return false;
    
                for (var i = 0, l=this.length; i < l; i++) {
                    // Check if we have nested arrays
                    if (this[i] instanceof Array && array[i] instanceof Array) {
                        // recurse into the nested arrays
                        if (!this[i].equals(array[i]))
                            return false;       
                    }           
                    else if (this[i] != array[i]) { 
                // Warning - two different object instances will never be equal: {x:20} != {x:20}
                        return false;   
                    }           
                }       
                return true;
                };
            
                var theyre_equal = old_agenda.equals(agenda);
    
                //if they're equal, return.  If false, set the flag.
                if(theyre_equal) {
                    return;
                } else { 
                    sheet.getRange(FLAG_CELL).setValue("*")
                }    
            };
        
        };
        
        //clear range and set
        whole_range.clearContent();
        
        if (typeof agenda[0] !== 'undefined') {
            var agenda_range;
            
            //make sure # of agenda items doesn't overflow the dimensions of agendaRange
                        
            if(agenda.length > this.rows) {
              sheet.getRange(ERROR_CELL).setValue("Too many events in agenda! Some events are not appearing. (Delete this message after fixing)");
              agenda = agenda.slice(0,this.rows);
              agenda_range = sheet.getRange(row_under_header,this.column,this.rows,1);
            } else {
              agenda_range = sheet.getRange(row_under_header,this.column,agenda.length,1);
            }
            
            agenda_range.setValues(agenda);
        };
    }
}    

// createFormattedAgenda takes an array of event objects and formats them

function createFormattedAgenda(events, day, isFromArray) {
      
    var agenda = [];
    var date_num = new Date(day).getDate();
    var agenda_index = 0;
      
    for (var i=0;i<events.length;i++) {
           
        var start_date = new Date(events[i].getStartTime());
        var start_num = start_date.getDate();
                      
        if(date_num === start_num)  { 
               
            var title = events[i].getTitle();
            var titleLength = title.length;
            var firstChar = title.charAt(0);
            var lastChar = title.charAt(titleLength - 1);

            //IF string is in quotes, delete first and last characters
            //(It is useful to wrap a string in quotes when using Google's quick add feature to make
            // sure that it doesn't guess wrong and misinterpret numbers in your event as dates or times)
            
            if(firstChar === "\"" && lastChar === "\"") {
            
                title = title.slice(1,titleLength - 1);
                Logger.log(title);
            
            };
            
            //Check for brackets
            
            var firstBracketIndex = title.indexOf("[");
            var lastBracketIndex = title.lastIndexOf("]");            
           
            //IF item from array has a bracketed subtring, 
            //THEN trim off the brackets and all but the substring, log into agenda[], and go to the next item

            if(isFromArray && firstBracketIndex >= 0) {
            
                var sub_item = ["--" + title.slice(firstBracketIndex + 1, lastBracketIndex)];
                agenda[agenda_index] = sub_item;
                agenda_index++;
                continue;
    
            };
            
            //IF no bracket found in an array item, OR
            //IF no characters before a bracketed substring,
            //THEN skip it, don't log anything into the agenda[], just go the next item 
            
            if((isFromArray && firstBracketIndex === -1) || (isFromArray === false && firstBracketIndex === 0)) {
            
                continue;
                
            };
            
            //IF not from an array AND there at least one character before a bracketed item 
            //THEN trim off the bracketed substring and then 'fall through' to format the item
            
            if(isFromArray === false && firstBracketIndex > 0) {  
                            
                title = title.slice(0,firstBracketIndex);
            
            };
            
            //ELSE, there must not be a bracket in the calendar event item                
         
            var starttime = new Date(events[i].getStartTime());
            var endtime = new Date(events[i].getEndTime());
            
            if(events[i].isAllDayEvent()){  
   
                var item= ["--" + String(title)];

            } else {  

                var starthour = String(correctHour(starttime.getHours())) + ":" + String(correctMinutes(starttime.getMinutes()));
                var endhour = String(correctHour(endtime.getHours())) + ":" + String(correctMinutes(endtime.getMinutes()));
                var item = ["--" + String(title) + " " + String(starthour) + "-" + String(endhour)];

            };

            agenda[agenda_index] = item;
            agenda_index++;
        };
    };
 
    return agenda;
};

// Runs updateAgendaRangesForSheet just for active sheet (user accesses from Scheduler Tools menu)

function updateActiveSheet() { 
        
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var date = new Date(sheet.getRange(DATE_CELL).getValue());
    
    updateAgendaRangesForSheet(sheet,date);
    
  
};

// Runs updateAgendaRangesForSheet for each day in month (user accesses from Scheduler Tools menu)

function updateAllSheets() { 
        
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var dates = [];
    var this_day = new Date();
    var start_day = new Date(sheets[0].getRange(DATE_CELL).getValue());
    var month = start_day.getMonth();
    var year = start_day.getFullYear();
    var days_in_month = new Date(year, month + 1, 0).getDate();
    var tz_offset = 0;
    
    dates[0]= start_day;
  
    for(var i = 0; i < days_in_month; i++) {    
        dates[i] = new Date(year, month, i + 1);
        updateAgendaRangesForSheet(sheets[i],dates[i]);
    }
    
    //if we're not in the current month, delete the automatic trigger
    if (this_day > dates[dates.length]) {
        deleteAutoUpdateTrigger();
    }
    
};

function correctHour(checkhour) {  
     if(checkhour > 12) {
         return checkhour - 12;
     } else {
         return checkhour;
     };
};
  
function correctMinutes(checkminutes) {
    if(checkminutes < 10) {
        return "0" + checkminutes;
    } else {
        return checkminutes;
    };
};

function vlookup(sheet, column, value) {
    var column_data = sheet.getRange(1,column,100,1).getValues();    
    var row = ArrayLib.find(column_data,0,value);
        
    return row;
}

function isEmpty(str) {
    return (!str || 0 === str.length);
}