Lesson planning with Google Sheets and Calendar

When I attended university in Edinburgh, I worked part time in a pizza restaurant. One of the chefs was doing a post graduate degree in psychology; his thesis was on connecting the brain to a peripheral data source to extend the capacity of the brain. I can't help thinking that Smartphones are a big step toward this, along with other research in controlling devices with the mind and eye movement.

Access Timetable GSheet here

For me, I don't remember my timetable; I don't need to if it is on my phone. The problem is getting my timetable and planning off my computer and onto my phone.  Google Apps Script, or GAS, allows the various Google apps to talk to each other so it is possible to build a school timetable into a GSheet and export it to GCal. Inspired by some work by Martin Hawksey and Romain Vialard, I have written/used a script that imports, deletes and exports the content of my calendars, and updates them.

function updateLessons(){
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var calName = sh.getRange("B1").getValue();
  var date_deb = sh.getRange("C1").getValue();
  var date_fin = sh.getRange("D1").getValue();
  var cond = sh.getRange("J2").getValue();
  var line = new Array();
  var toChangeTitleCnt = 0;
  var toChangeCnt = 0;
  var blank = "";
  if(calName=='default'){var cal = CalendarApp.getDefaultCalendar()}
  else{
    var cal = CalendarApp.openByName(calName)};
  if (cal)
  {
    var events = cal.getEvents(new Date(date_deb), new Date(date_fin),{max: 4000});
    var sel= sh.getRange(3,1,sh.getLastRow(), 10).getValues();
    for(e=0;e<events.length;++e){
      var changeFlag = false;
      var ID = events[e].getId();
      for(n=0;n<sel.length;++n){
        if ((sel[n][8] == "x"||sel[n][8] == "X")&&sel[n][0]==ID ){
          changeFlag = true
          sh.getRange(n+3,9,1,1).setBackgroundColor('#55ff55');
          //sh.getRange(n+3,7,1,1).setBackgroundColor('#55ff55');
          //sh.getRange(n+3,6,1,1).setBackgroundColor('#55ff55');
          Utilities.sleep(50);
          SpreadsheetApp.flush();
          Logger.log('FLAG '+ e)
          sh.getRange(n+3,9,1,1).setValue(blank);
          sh.getRange(n+3,9,1,1).setBackgroundColor('#ffffff');
          break
        }
      }
      if(changeFlag){
        try{
          var toChangeTitle = events[e].setTitle(sel[n][5]);
          ++toChangeTitleCnt;
          var toChange = events[e].setDescription(sel[n][6]);
          var toChangeLoc = events[e].setLocation(sel[n][4]);
       
          ++toChangeCnt;
          changeFlag = false;
          Logger.log('Lesson Title updated : '+sel[n][5]);
          Logger.log('Lesson updated : '+sel[n][6]);
          }catch(Err){Logger.log('Event from a series')}
      }
    }
  }
  var msg = toChangeTitleCnt + " lesson titles changed + " + toChangeCnt + " lessons updated in calendar '"+calName+"'";
  ss.toast("Update completed", msg, 3)
  Utilities.sleep(3000)            
}

The update feature above is the one I use most. I teach 8 classes, so each has its own sheet. When I make a change to my planning I run the update function and it updates the events in my calendar; my phone syncs with the calendar and my changes are automatically available to me.

When I am finished planning my week and have updated each class, I run the import Calendar function on my main sheet that pulls all of the events in the calendar back into my GSheet. I have a technician that prepares my science equipment for my classes, so this import is important. The script that runs the import splits the content in the calendar description field to separate my lesson plan from the tech requisitions. With the tech requisitions in a separate column, I have created another equipment sheet that builds my requisitions order for my tech.

function importCalendar(){
  clrdisplay()
  var cal = 0;
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendarName = sheet.getRange("B1").getValue();
  if (calendarName == "default"){
    cal = CalendarApp.getDefaultCalendar();
  } else if (calendarName != "default") {
    cal = CalendarApp.openByName(calendarName);
  } 

  var events = cal.getEvents(new Date("September 1, 2014"), new Date("August 31, 2015"), {max: 4000});
   var day = new Array();
    day = ["Sun","Mon","Tue","Wed","Thu","Fri","Sat"]
 
  for (var i=0;i<events.length;i++) {
    var formRow = i+3;
 
    var tech = "=iferror(right(G" + formRow + ",len(G"  + formRow + ")-(find(\"||\",G"  + formRow + "))), \"No Practical Equipment\")";
    var update = "";
    var class = "=LEFT(F" + formRow + ",FIND(\" -\",F" + formRow + "))";
    var temp = ""
     temp = day [events[i].getStartTime().getDay()];
    var details=[[events[i].getId(), temp, events[i].getStartTime(), events[i].getEndTime(), events[i].getLocation(), events[i].getTitle(), events[i].getDescription(), tech, update, class]];
    var row=i+1;
    var range=sheet.getRange(row+2,1,1,10);
    range.setValues(details);
  }
}

function clrdisplay() {
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var LL=ss.getLastRow()
  sh.getRange(3,1,LL,10).clear().setVerticalAlignment('MIDDLE');
} 

A couple of my colleagues have used my planner, and one has changed to use Planner.com, or Planneredu.com. These apps are nice, but I like the customisation building my own has permitted, and that my timetable/lesson plans are on my phone....and that I don't have to print out my tech requisitions because my tech is shared into my planning sheet.