A Calendar tracking app.

This small application is useful if you have some shared calendars and if you want to be notified if someone has changed something without telling you.
I use it at work for classrooms since a few people have write access to these calendars and communication is not their priority ;-)

The script runs from a spreadsheet, is very easy to setup and is fully automatic once setup is completed.
The example (shared) spreadsheet is here , just make a copy of it , change the mail adress in the script  (see screen capture below) to choose who will receive the notification emails.
You have to change the list of 'authorized persons that the script will NOT monitor, only a part of their email is necessary, use the format as in the illustration :


This app is also available in french, it uses french messages and date display.

Then run the last function in the script 'callist()'* to get a list of all your calendars. Go to the first sheet (master) and delete the calendar names ans ID that you don't want to monitor -DON'T LEAVE ANY EMPTY ROW IN THE LIST !
* note that the test calendar that is on the model spreadsheet is really a test Calendar, nothing personal in there ! you can use it for testing purposes if you want

And that's it, you're ready to run it for the first time.

It will ask for authorization on Calendars, spreadsheet and Mail, that is normal .  The first email you will receive will be empty since it takes data from the script properties (to 'see' events that has been removed) and script properties is empty on startup.

The last thing you have to do is to setup a trigger for this to work automatically : from the script editor goto ressources > current script triggers and follow the example below.


Now the script will monitor every change on your chosen calendar and send you an email if someone 'not authorized' adds or removes or changes any event... it will check on every hours but you can change that to a shorter time if you want (be careful though not to explode your quota or max execution time). Do not use trigger periodicity of more that 115 minutes because the script uses a trick to detect the 'natural end' of events without generating alerts.

Here is the script :

  var nom = String(Session.getUser().getUserLoginId());
  var authorized = ['admin','webmaster','mary','william'];// define a list of 'authorized event creators'
  var owner = 'mail@xxx.yyy';// email to whom the email will be sent
  var collaborator = 'a collaborator email';// email of an additional collaborator (optional, see line 93)
  var ss = SpreadsheetApp.getActiveSpreadsheet();// this spreadsheet
  var sh = ss.getSheetByName('master');
  var calList = sh.getDataRange().getValues();
  var logsheet = ss.getSheetByName('logger');
  var last = logsheet.getLastRow();
  var FUS1=new Date().toString().substr(25,6)+":00";

function onOpen() {
  var menuEntries = [ {name: "Manual test", functionName: "checkCal"},
                      {name: "Lauch autoTest", functionName: "manualcheck"},
                      {name: "delete created sheets", functionName: "delsheets"}
                     ];
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    sheet.addMenu("Tracking utilities",menuEntries);//
//  Logger.log(FUS1)
  SpreadsheetApp.setActiveSheet(sheet.getSheetByName('logger'));// start on logger page  
}

function manualcheck(){
    autoCheckAllCals();
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    if(sheet.getSheets()>1){
    SpreadsheetApp.setActiveSheet(sheet.getSheets()[2]);// this works from the menu when ss is open
}
}

function autoCheckAllCals(){
    var eventarray = new Array();
    var logarray = new Array();
    var line = new Array();
    ss.setActiveSheet(logsheet)
    line.push('Events created by someone else','start ','end','location','Creator','Modification','event length','invited');
    eventarray.push(line);
    var today =  new Date(); // maintenant
    var startDate = new Date(today.getTime()-120*60*1000);// 2h before now
    var endDate = new Date("june 30, 2013 23:59:00"); // choice every year
    var sendMail = true    
    var mailSent = false
    logsheet.insertRowAfter(last)
    var sheetName = "Control from "+ Utilities.formatDate(startDate, FUS1, "MMM-dd-yy")
      + "-to-" + Utilities.formatDate(endDate, FUS1, "MMM-dd-yy")  
  for(nn=0;nn<calList.length;++nn){
    var calName = calList[nn][0];
    var calId = calList[nn][1];   
// Logger.log(calList)   
// Logger.log(startDate+'  |  '+endDate)   
// Logger.log(calName+' | '+calId)
    var Calendar = CalendarApp.getCalendarById(calId)
        var events = Calendar.getEvents(startDate , endDate);
    if (events[0]) {
    for (var i = 0; i < events.length; i++) {
      if(events[i].getTitle().match('week nr')=='week nr'){continue};// this condition to ignore week numbers that we add in all calendars.... might be anything else that should be ignored
      var skip = false
      for(var aaa in authorized){
//Logger.log(events[i].getCreators())
      if(events[i].getCreators().join().match(authorized[aaa])==authorized[aaa]){skip=true;continue};// check if creator is in the 'authorized' list
        }
        if(skip){continue}
//Logger.log(events[i].getStartTime()+'   '+events[i].getEndTime());      
      if(events[i].getStartTime()<today||events[i].getEndTime()<today){Logger.log('condition < today = true '
         +events[i].getTitle()+' start='+Utilities.formatDate(events[i].getStartTime(), FUS1, "HH:mm")+' end='+Utilities.formatDate(events[i].getEndTime(), FUS1, "HH:mm"));sendMail=false;continue}
// if condition = true update script properties so it won't generate an alert when finishing
      var line = new Array();
      var logline = new Array();
      FUS1=events[i].getStartTime().toString().substr(25,6)+":00";
      line.push(calName +' : '+events[i].getTitle());
      logline.push(calName +' : '+events[i].getTitle()+'|');
      line.push(Utilities.formatDate(events[i].getStartTime(), FUS1, "MMM-dd-yy")+' @ ' +Utilities.formatDate(events[i].getStartTime(), FUS1, "HH:mm"));
      logline.push(Utilities.formatDate(events[i].getStartTime(), FUS1, "MMM-dd-yy")+' @ ' +Utilities.formatDate(events[i].getStartTime(), FUS1, "HH:mm")+'|');
      line.push(Utilities.formatDate(events[i].getEndTime(), FUS1, "MMM-dd-yy")+' @ ' +Utilities.formatDate(events[i].getEndTime(), FUS1, "HH:mm"));
      line.push(events[i].getLocation());      
      line.push(events[i].getCreators().join());
      logline.push(events[i].getCreators().join()+'∏');
      line.push('on '+Utilities.formatDate(events[i].getLastUpdated(), FUS1, "MMM-dd-yy"))
      line.push(((events[i].getEndTime() - events[i].getStartTime()) / 3600000)+' hours');
      var invitelist='';
      var list = events[i].getGuestList()
          for(n=0;n<list.length;++n){invitelist+=list[n].getName()+', '}
      line.push(invitelist)
      eventarray.push(line);
      logarray.push(logline)
    }
//    Logger.log(logarray)
    }    
}
      if(eventarray.length>1){     
  var message = logarray.toString().replace(/,/g,'')
  var formertest = ScriptProperties.getProperty('lastTest')
      if(formertest!=message){
Logger.log('changement ...')        
  var html = createMsg(message)
  if(sendMail){
      MailApp.sendEmail(owner,'Events created by someone in your calendar', "", {"htmlBody" : html});
//      MailApp.sendEmail(collaborator,'Events created by someone in your calendar', "", {"htmlBody" : html});// supprimer ou commenter cette ligne si nécessaire
Logger.log('send mail')
mailSent = true
  }
    ScriptProperties.setProperties({'lastTest': message}, true);   
      try{ 
    var newsheet = ss.insertSheet(sheetName,2);
      }catch(error){
        FUS1=new Date().toString().substr(25,6)+":00";
        var newsheet = ss.insertSheet(sheetName+' - '+Utilities.formatDate(new Date(), FUS1, "HH:mm:ss"),2);
        }
    newsheet.getRange(1,1,eventarray.length,eventarray[0].length).setValues(eventarray);
    newsheet.getRange(1,1,1,eventarray[0].length).setBackgroundColor('#ffffcc');
    newsheet.setColumnWidth(1, 350).setColumnWidth(2, 150).setColumnWidth(3, 150).setColumnWidth(4, 150).setColumnWidth(5, 150).setColumnWidth(6, 105).setColumnWidth(7, 95).setColumnWidth(8, 450);
    newsheet.setFrozenRows(1)
}
    ScriptProperties.setProperties({'lastTest': message}, true);    // even if no mail, write to script properties       
      }
  FUS1=new Date().toString().substr(25,6)+":00";
  var logmsg = 'Auto test launched on '+Utilities.formatDate(new Date(), FUS1,"MMM-dd-yy @ HH:mm");
  if (mailSent){logmsg+=" - mail sent"}else{logmsg+=" - no alert"}
   logsheet.getRange(last+1,1).setValue(logmsg);
}


function createMsg(logarray){
  var formertest = ScriptProperties.getProperty('lastTest');
  if(!formertest){return}
  var testitems = formertest.split('∏');
  var items = logarray.split('∏');
  if(items.length>testitems.length){
  var color='#CCFFCC'
  if(testitems.toString().indexOf(items[0])==-1){color='#FFBBBB'}
    var html = " Digest of Events created by someone else :<B> new events in RED </B><BR><BR><TABLE border = 1 cellpadding = 5><EVENTS></table>"    
  var table = "<tr valign='top' bgcolor='"+color+"' cellpadding=5>"
      color='#CCFFCC'
      for(zz=0;zz<items.length-1;++zz){
//  Logger.log(testitems.toString().indexOf(items[zz]))  
        var subitems = items[zz].split('|')
        if(testitems.toString().indexOf(items[zz+1])==-1){color='#FFBBBB'}
        for(tt=0;tt<subitems.length;++tt){
         table+= '<td>'+subitems[tt]+'</td>'
    }
        table+="</tr><tr valign='top' bgcolor='"+color+"' cellpadding=5>"   
        color='#CCFFCC'
            }
        table+='</tr>'
  var msghtml = html.replace("<EVENTS>",table) 
//    Logger.log(msghtml)
      return msghtml
}else{
  var color='#FFBBBB'
  if(items.toString().indexOf(testitems[0])==-1){color='#FFBBBB'}
  var html = "One (or more) event removed <B>in RED</B> as shown below<BR><BR><TABLE border = 1 cellpadding = 5 bgcolor='"+color+"'><EVENTS></table>"    
  var table = "<tr valign='top' bgcolor='"+color+"' cellpadding=5>"
      for(zz=0;zz<items.length;++zz){
//  Logger.log(testitems.toString().indexOf(items[zz]))  
        var subitems = testitems[zz].split('|')
        if(items.toString().indexOf(testitems[zz])==-1){
        for(tt=0;tt<subitems.length;++tt){
         table+= '<td>'+subitems[tt]+'</td>'
    }
        }
        table+="</tr>"
            }
        table+='</tr>'
  var msghtml = html.replace("<EVENTS>",table) 
//    Logger.log(msghtml)
      return msghtml
}
}

  
function checkCalendars(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var calendar_name = e.parameter.calendar;
  var startDate = new Date(e.parameter.start);
  var endDate = new Date(e.parameter.end);
  var calList = sh.getRange(1,1,sh.getLastRow(),2).getValues();
   for(nn=0;nn<calList.length;++nn){
     if(calList[nn][0]==calendar_name){var CalId = calList[nn][1];break}
   }
  var Calendar = CalendarApp.getCalendarById(CalId)
//   
  var sheetName = calendar_name + "-from-" + Utilities.formatDate(e.parameter.start, FUS1, "MMM-dd-yy")
      + "-to-" + Utilities.formatDate(e.parameter.end, FUS1, "MMM-dd-yy")  
//
  var events = Calendar.getEvents(startDate , endDate);
  if (events[0]) {
    logsheet.getRange(last+1,1).setValue('Manual test launched on '+Utilities.formatDate(new Date(), FUS1,"MMM-dd-yy"));
    ss.setActiveSheet(logsheet)
    var eventarray = new Array();
    var line = new Array();
    line.push('Calendar Name : '+calendar_name,'start ','end','Localisation','created by','Modification','event length','invited');
    eventarray.push(line);
    for (i = 0; i < events.length; i++) {
      if(events[i].getTitle().match('week')=='week'){continue}
      line = new Array();
      line.push(events[i].getTitle());
      FUS1=events[i].getStartTime().toString().substr(25,6)+":00";
      line.push(Utilities.formatDate(events[i].getStartTime(), FUS1, "MMM-dd-yy")+' @ ' +Utilities.formatDate(events[i].getStartTime(), FUS1, "HH:mm"));
      line.push(Utilities.formatDate(events[i].getEndTime(), FUS1, "MMM-dd-yy")+' @ ' +Utilities.formatDate(events[i].getEndTime(), FUS1, "HH:mm"));
      line.push(events[i].getLocation());      
      line.push(events[i].getCreators().join());
      line.push('on '+Utilities.formatDate(events[i].getLastUpdated(), FUS1, "MMM-dd-yy"))
      line.push((events[i].getEndTime() - events[i].getStartTime()) / 3600000);
      var invitelist='';
      var list = events[i].getGuestList()
          for(nn=0;nn<list.length;++nn){invitelist+=list[nn].getName()+', '}
      line.push(invitelist)
      eventarray.push(line);
    }
    Logger.log(eventarray)
      try{ 
    var newsheet = ss.insertSheet(sheetName,2);
      }catch(error){
    var newsheet = ss.insertSheet(sheetName+'-'+Utilities.formatDate(new Date(), FUS1, "HH-mm-ss"),2)
        }
    newsheet.getRange(1,1,eventarray.length,eventarray[0].length).setValues(eventarray);
    newsheet.getRange(1,1,1,eventarray[0].length).setBackgroundColor('#ffffcc');
    newsheet.setColumnWidth(1, 350).setColumnWidth(2, 150).setColumnWidth(3, 150).setColumnWidth(4, 150).setColumnWidth(5, 150).setColumnWidth(6, 105).setColumnWidth(7, 95).setColumnWidth(8, 450);
    newsheet.setFrozenRows(1)
    SpreadsheetApp.setActiveSheet(sheet.getSheets()[2]);// envoie sur la page créée
  } else {
    var startstring = Utilities.formatDate(e.parameter.start, FUS1, "dd-MMM-yyyy");
    var endstring = Utilities.formatDate(e.parameter.end, FUS1, "dd-MMM-yyyy");
    Browser.msgBox('no event between ' + startstring + ' and ' + endstring +' in your calendar :'+calendar_name);
  }
  var app = UiApp.getActiveApplication();
  app.close();
  return app;  
}


function checkCal() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle("Calendar Tracking");
  app.setHeight(265).setWidth(400);
  // Create a grid with 3 text boxes and corresponding labels
  var grid = app.createGrid(3, 2);
  grid.setWidget(0, 0, app.createLabel("Name of the Calendar:"));
  var list = app.createListBox();
  list.setName('calendar');
  grid.setWidget(0, 1, list);
  for (var i = 0; i < calList.length; i++) {
    list.addItem(calList[i][0]);
  }
  grid.setWidget(1, 0, app.createLabel('Start Date :'));
  grid.setWidget(1, 1, app.createDateBox().setId("start").setValue(new Date()));
  grid.setWidget(2, 0, app.createLabel('End Date :'));
  grid.setWidget(2, 1, app.createDateBox().setId("end").setValue(new Date(new Date().getTime()+30*86400000)));
  var panel = app.createVerticalPanel();
  panel.add(grid);
  var button = app.createButton('Start Checking & report');
  var handler = app.createServerClickHandler('checkCalendars');
  handler.addCallbackElement(grid);
  button.addClickHandler(handler);
  var clock = app.createImage('https://dl.dropbox.com/u/211279/Time-change-clock_animated_TR80.gif', 0, 0, 82, 82).setId('clock').setVisible(false);
  var clockHandler = app.createClientHandler().forTargets(clock).setVisible(true);
  button.addClickHandler(clockHandler)
  panel.add(button).add(clock);
  app.add(panel);
  doc.show(app);
}

function delsheets(){
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var numbofsheet=ss.getNumSheets();// check how many sheets in the spreadsheet
    for (pa=numbofsheet-1;pa>0;--pa){ 
      ss.setActiveSheet(ss.getSheets()[pa]);
      if(ss.getSheets()[pa].getSheetName()!='logger'&&ss.getSheets()[pa].getSheetName()!='master'){
      var newSheet = ss.deleteActiveSheet(); // delete sheets begining with the last one
      Utilities.sleep(400);
    }
    }
  ss.setActiveSheet(ss.getSheets()[1]);// return to first sheet as active sheet (useful in 'list' function)
  SpreadsheetApp.flush();
  }

// This small function is to get the list of calendar names & Ids that you have access to, please edit the master sheet to keep only the ones you want to monitor (without empty rows).
function Callist(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('master')
  sh.getDataRange().clearContent();                      
  var list = new Array();
  var store = new Array();
  list = CalendarApp.getAllCalendars()
    for (n=0;n<list.length;++n){
      var name = list[n].getName() ;                     
      var id = list[n].getId() ;                     
        store.push( [name,id])
    }        
  sh.getRange(1,1,store.length,store[0].length).setValues(store);  
}



Comments