Apps Scripts for Scheduling Student Meetings

Editor's Note: Aran Glancy is a Google Certified high school math teacher at the Dalton School in New York City. He teaches ninth and eleventh grades, and works with other teachers both formally and informally on the use of technologies (including but not limited to Google Apps) in the classroom.

As a teacher at the Dalton School in New York City, one of my most important and enjoyable jobs (along with teaching my 9th and 11th grade math classes) is to meet with my students one-on-one in what my school calls Lab.  Both students and teachers have free time programmed into their schedules specifically to hold these meetings. But since every one's schedule is different, finding a common time to meet is often difficult.  This school year I am implementing a Google Apps Script I wrote to help with the scheduling process.  

The paper sign-up sheets we used when I first started were functional, but very problematic.  Last year, I had the idea to move my schedule to a Google Calendar.  Dalton is adopting Google Apps, but we haven't yet released Calendar to the students.  To work around this, I made my Lab Calendar public and put a link on my blog and on our course websites. That way, students could see my schedule anywhere they could find a computer. Finding a free meeting time was now easy.

The problem, however, was actually scheduling the labs.  Without Calendar as part of our Apps deployment I couldn't share the calendar with them, and even if I could I was concerned about students deleting each other’s labs to make room for their own!  I decided to have students email me their lab requests, which I would then use to manually create the events.  For the students, this system worked well, but I found myself spending a lot more time than I had anticipated creating events.  I have on average 6 to 10 of these meetings a day.  Even with the Quick Add feature, it was still a big time sink.  

The biggest problem, however, was that most of the email requests were happening in the evening and during the night.  Most of the time I didn't get to these until the morning, so many requests sat in my inbox unprocessed--and not on the calendar--throughout the night!  Students were signing up for meetings at times that looked free but had actually been booked by another student earlier in the evening.  I couldn't process the emails fast enough to prevent the overbooking.  

This summer, as I prepared for another year, a colleague suggested that Google Apps Script might be able to add events to a calendar directly from a spreadsheet.  I was intrigued.  A quick google search led me to some sample code which I copied and pasted into the newly discovered Script Editor.  

I quickly found my way to the Google Apps Script API, and was immediately amazed at all of the services available.  With the Calendar service and and the Mail service in particular, I realized I could use a Google Form as my lab sign-up sheet.  Through a script, a student's event could automatically be added to my calendar without directly giving the student access to the calendar.

I started by creating a Form with fields for name, email, date, time, topic, duration, and other comments.  In the corresponding spreadsheet, I started a script that added the event to my calendar.  With my calendar stored as the variable cal, the command

  cal.createEvent("Lab with "+student_name+" re: "+topic, d1, d2, advancedArgs);

added an event with title (for example) "Lab with Johnny re: factoring," where d1 and d2 are the start and end times. advancedArgs is just a list previously defined with name-value pairs for the Description field and the Location field of the Google Calendar event.  I left off the location, because it wasn't necessary for my purposes.

What if there was already something scheduled during that time? The line

  var events = cal.getEvents(d1,d2);

stores any events that already exist during the scheduled event in an array. I then used an if statement to test for conflicts before I created the event:

  if(events.length==0)
  {
  cal.createEvent("Lab with " + student_name + " re: " + topic, d1, d2, advancedArgs);
     }

When scheduling the labs, it's important to know whether a lab was scheduled successfully, so I used the Mail service to send emails confirming the event or reporting any errors.  Here is an example of an error email:  

MailApp.sendEmail(myemail+", " + studentemail, "Lab with Mr. Glancy: CONFLICT", student_name + "\n\nSorry, but the lab you tried to schedule on " + d1.toString() + " for " + duration + " minutes was not available. \n\nPlease look for another time, and try again.  If you have any questions, please email me directly.  \n\nThanks, \n\nMr. Glancy");

Encouraged and excited, I started adding more features.  I found last year that students would often put the wrong date on their lab requests.  To deal with this, I added a test to make sure that the lab hadn't already happened by comparing the start date and time to the timestamp of their form entry.  

I also didn't want them scheduling meetings too early or too late, so I checked to make sure that the start and end times were during the regular school day.  Each test has a corresponding error email, and if the event passes all tests and is created, a confirmation email is sent to both the student and myself.  The final step was to set the script to run when a form submit event took place.

I added a link to the form on my blog and course website just in time for the first week of school.  As of this writing, 4 days into the semester, I have had 51 lab requests--all of which have been either successfully added to the calendar or marked as conflicts or invalid.  And I haven't done any of them by hand!  Thanks Google Apps Scripts.  I haven't released the actual sign up sheet I am using, however, I created a template in the Docs Template Gallery called Sample Calendar-Import Form which contains a very similar script and has the same functionality.  Next project:  A customized gradebook!