Teacher Schedules, Webcal Feeds, Google Calendar and Apps Script

G Suite schools know the power of Google’s collaborative apps and how they facilitate learning, yet few schools venture to leverage their customizable powers to create custom solutions to age old administrative problems.  Take Google Calendar and teacher schedules for example.  Many learning management systems generate beautiful individual calendars, yet make it annoyingly cumbersome to view anyone else’s schedule other than your own.  Some even offer webcal feeds to teachers’ schedules providing somewhat more flexibility in where you view the schedules, but even those have limitations regarding visibility across an organization and you can’t edit those individual events.  Likewise, there are usually options for downloading ics files, and importing them into your default calendar, but those often come with events you may not necessarily want or need on your calendar either.

Teacher schedules are in many cases complicated works of art and not conventionally Google friendly.  For example, I work in a school that follows a 10-day, number cycle at the Primary School and an 8-day, A-H day rotating cycle in the Middle and Upper School with slightly different bell schedules.  There are also various holidays, professional, and non-instructional days to consider as well.  These variables create nearly impossible conditions for mass producing teacher schedules in his or her default Google calendar.

This is where Google’s customizable power is key and where my story begins.  A video demonstration is located at the end of this post.

Step 1:  Collecting webcal feeds of individual teacher schedules.

In the interest of saving time, I made a short tutorial showing how teachers could find their own webcal feeds in our LMS and submit them in a Google Form.  By automatically collecting their email address, the Form Response sheet aligns the webcal feeds with the appropriate “owner” of the feed.

 

Step 2: Subscribing to all teacher webcal feeds with one service account.

Once the webcal feeds have been collected, subscribe to all of them manually using a service account.  Unfortunately, there isn’t a method for automating calendar subscriptions via URL so this has to be done one calendar at a time, ensuring they are visible to everyone. The payoff is that the webcal feeds are persistent, so this doesn’t need to be done each year.

 

Step 3: Retrieve calendar IDs for all of the webcal feeds

Once you’ve subscribed to all of the calendars, create a new sheet called Teacher Schedules in your service account with three tabs, Schedules, WebcalFeeds, and AllCalendars respectively.  In the end, your web app will be bound to this sheet so make sure permissions allow anyone in your organization to view it. On your sheet, go to Tools, then open the Script Editor.  Name your project MySchedule (this will also be the name of the app) and create a new script file called calendarids.  Apps Script can then list all of your Calendar Names and IDs in the AllCalendars sheet.  It’s important to note that even if you change the display name of the webcal feeds, the name remains that long, incomprehensible URL when you retrieve it via Apps Script.  Paste this snippet of code and run it to dump the calendar names in Column A and the IDs in Column B of the AllCalendars sheet.  It’s also a good idea to set a daily trigger on this function so the list is refreshed automatically.

function getCalendars() {
  var ss = SpreadsheetApp.openById("YOURGOOGLESHEETID");
  var sheet = ss.getSheetByName("AllCalendars");
  var cals = [];
  var calendars = CalendarApp.getAllCalendars();
  for(var i=0;i<calendars.length;i++){
    cals.push({'id':calendars[i].getId(),'name':calendars[i].getName()})
    var name = sheet.getRange(i+1,1);
       name.setValue(calendars[i].getName());
    var id = sheet.getRange(i+1,2);
       id.setValue(calendars[i].getId());
                
Logger.log(calendars[i].getId());
Logger.log(calendars[i].getName()); 
  }
}    

And here is a screenshot of the resulting sheet.

 

Step 4: Import FormResponse Sheet from webcal feed collection form

At this point, we have a sheet that lists all of our calendar names and calendar ids and another sheet that lists faculty email addresses and webcal feeds.  Using the =importrange function we can import the faculty email addresses and corresponding webcal feeds to our WebcalFeeds sheet.

In cell A1:

=IMPORTRANGE(“URLOFYOURFORMRESPONSESHEET”,“Form Responses 1!A1:C”)

Moreover, by adding an =ARRAYFORMULA(IFERROR(VLOOKUP(C2:C,AllCalendars!$A$1:$B,2,FALSE))) in cell D2 we can lookup the Calendar ID of each webcal feed from the AllCalendars sheet.

Step 5: Align faculty email addresses and Calendar IDs in a new sheet

In the interest of working with a clean sheet, I queried in cell A2 of the Schedules sheet two columns of interest for the web app, the email address of the faculty member and Calendar ID from the WebcalFeeds sheet . This is the only piece of data used by the web app.

 

Step 6:  Build and deploy the web app

In your Google Sheet, reopen the Script Editor and create an HTML file called index.  Copy and Paste the code below into that file. This controls how the web app is displayed for the end user.   Modify as needed.


<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<script type="text/javascript">
</script>
</head>

<body>

<a id="caller" href="#">Subscribe</a>
<br>
<div id="div">waiting...</div>
<p>By clicking on the link above, your OnCampus schedule will appear as events on your Google Calendar. This app creates 25 events per hour.<br />
Your schedule should be complete within a day. You will see the words <b>"You are Subscribed!"</b> when the app has fully executed.
</p>

<script>
function callback(mySchedule) {
document.getElementById("div").innerHTML=mySchedule;
}
function go() {
google.script.run.withSuccessHandler(callback).mySchedule();
}
document.getElementById('caller').onclick = go;
</script>
</body>
</html>

Finally, in the Code.gs file is where all the action is. I built this app off an existing snippet of code I found on stackoverflow.  I’m not an expert coder, but sensitive to patterns and fairly decent at asking questions that help me find the answers.


//derived from original code found here https://stackoverflow.com/questions/21467888/copy-events-from-one-google-calendar-to-another-without-duplication
function doGet() {
return HtmlService.createHtmlOutputFromFile('index')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function createTimeDrivenTriggers() {
// Trigger every hour
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger('mySchedule').timeBased().everyHours(1).create();
}

function mySchedule(){
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
createTimeDrivenTriggers();

var userProperties = PropertiesService.getUserProperties();
userProperties.setProperty('COUNTER', '0');

var user = Session.getActiveUser().getEmail();
var data = SpreadsheetApp.openByUrl("YOURGOOGLESHEETURL");
var sheet = data.getSheetByName("Schedules");
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
var range = sheet.getRange(1, 1, lastRow, lastCol).getValues();
for(n=0;n<range.length;++n){
if (range[n][0]==user){break} ;// if a match in column A is found, break the loop
}
Logger.log(range[n][1]);
var calendar = CalendarApp.subscribeToCalendar(
range[n][1]);
var calendars = CalendarApp.getCalendarById(range[n][1]);
var hide = calendars.setHidden(true);
var exclude = "-School"
var firstDay = new Date("2018/08/27"); // First Day of school
var lastDay = new Date("2019/06/12"); // Last Day of school
// get my oncampus calendar to see whether I have events to add to my primary calendar
var my_oncampus_calendar = CalendarApp.getCalendarById(range[n][1]);
var my_oncampus_events = my_oncampus_calendar.getEvents(firstDay, lastDay, {search: exclude});
Logger.log(my_oncampus_events);

// get my primary calendar
var default_calendar = CalendarApp.getDefaultCalendar();
var default_events = default_calendar.getEvents(firstDay, lastDay, {search: exclude});

// build array of event tags for comparison
var default_event_Tags = [];
default_events.forEach( function (event) {
default_event_Tags.push(event.getTag('origID'));
});
var counter = userProperties.getProperty('COUNTER');
my_oncampus_events.forEach( function (event) {
// if it doesn't exist in default_events, create a new event
var eventId = event.getId();
Logger.log(event.getTitle());
var match = default_event_Tags.indexOf(eventId);
if (match === -1) {
if (counter < 25){
// Duplicate the event from my_oncampus_calendar
var newEvent = default_calendar.createEvent(event.getTitle(), event.getStartTime(), event.getEndTime());
// Apply a tag with original event ID
newEvent.setTag('origID', eventId);
counter++;
}
}
Logger.log(counter);
});
return "<h1>You are Subscribed!</h1>";
}

Let’s explore this code from top to bottom.

function doGet() { return HtmlService.createHtmlOutputFromFile('index') .setSandboxMode(HtmlService.SandboxMode.IFRAME);}

The doGet() function is needed for the webapp and communicates with our HTML file called index.

 

function createTimeDrivenTriggers() {
// Trigger at 1 am everyday
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger('mySchedule').timeBased().everyHours(1).create();
}

 

The createTimeDrivenTriggers() function programmatically installs a trigger to run the app every hour.  More on why that is important later.  The rest of the code pertains to the mySchedule() function and can be explained like this in terms of tasks.

The first task is to delete the existing trigger and create a new one.  This prevents the accumulation of multiple triggers.

var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
createTimeDrivenTriggers();

 

The next task is to establish an important user property.  In this case, it’s a simple counter.  This counter helps us shut the app down before we run into errors.  More on that later.

var userProperties = PropertiesService.getUserProperties();
userProperties.setProperty('COUNTER', '0');

Then the app needs to figure out who is using the app and retrieve the right webcal feed. This part of the code retrieves the email of the active user, then looks for it in Column A of our Google Sheet.  Once it finds a match it stops looping and logs the calendar ID located in Column B.

var user = Session.getActiveUser().getEmail();
var data = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1qVfrE5OTdVPRQnXJ3djtz5buTb-ofFBbGGlGmKA8zfg/edit#gid=0");
var sheet = data.getSheetByName("Schedules");
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
var range = sheet.getRange(1, 1, lastRow, lastCol).getValues();
for(n=0;n<range.length;++n){
if (range[n][0]==user){break} ;// if a match in column A is found, break the loop
}
Logger.log(range[n][1]);

The code then subscribes that user to that webcal feed and hides it.  Our goal is not to view the webcal feed, but rather add the events to our primary calendar.

var calendar = CalendarApp.subscribeToCalendar(
range[n][1]);
var calendars = CalendarApp.getCalendarById(range[n][1]);
var hide = calendars.setHidden(true);

Next I declare which events to ignore using keywords.  In my case, I did not want to return any of the all day events, which coincidentally all contained the string “-School”.  The code also establishes a date range in which to search for events, then retrieves all events that meet the criteria.

var exclude = "-School"
var firstDay = new Date("2018/08/27"); // First Day of school
var lastDay = new Date("2019/06/12"); // Last Day of school
// get my oncampus calendar to see whether I have events to add to my primary calendar
var my_oncampus_calendar = CalendarApp.getCalendarById(range[n][1]);
var my_oncampus_events = my_oncampus_calendar.getEvents(firstDay, lastDay, {search: exclude});
Logger.log(my_oncampus_events);

The code then looks at all of the events in that range already on my primary calendar.

// get my primary calendar
var default_calendar = CalendarApp.getDefaultCalendar();
var default_events = default_calendar.getEvents(firstDay, lastDay, {search: exclude});

Then an array of event tags is built to compare events on both calendars.

// build array of event tags for comparison
var default_event_Tags = [];
default_events.forEach( function (event) {
default_event_Tags.push(event.getTag('origID'));
});

Next the counter is set to 0, both calendars are compared and any events not yet on my primary calendar are added and given the same tag, preventing duplicate event creation the next time the script runs.  The counter counts each time a new event is created and once it reaches 25, no more events are created.

The reason for the 25 event limit is because of the errors Google throws if you’re creating too many events too quickly.  The script will fire again an hour later and the whole process repeats itself.  Most schedules can be completed in a day.

var counter = userProperties.getProperty('COUNTER');
my_oncampus_events.forEach( function (event) {
// if it doesn't exist in default_events, create a new event
var eventId = event.getId();
Logger.log(event.getTitle());
var match = default_event_Tags.indexOf(eventId);
if (match === -1) {
if (counter < 25){
// Duplicate the event from my_oncampus_calendar
var newEvent = default_calendar.createEvent(event.getTitle(), event.getStartTime(), event.getEndTime());
// Apply a tag with original event ID
newEvent.setTag('origID', eventId);
counter++;

Finally, a confirmation message that the app installed successfully is returned to hour HTML file.

return "<h1>You are Subscribed!</h1>";
}

To publish your web app, in the script editor go to Publish, Deploy as a web app. Make a comment regarding the projects version, configure the app to execute as the user accessing the web app, and make it available to everyone in your domain.

 

Then share the web app’s URL with your teachers.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *