Using Google Apps to Automate Scheduling of Student Reassessment

Using Google Apps to do… what?

To automate the scheduling of student reassessment!

Last year, I had difficulty keeping up with the multitude of students coming in on different days at different times. Partway through the year, I’d created a physical form for them to fill out and turn into me. Unfortunately, that meant I’d have to tweak the template form every time I’d have a quiz or a test, print off a bunch of copies, and then keep up with the ones that were turned in. Furthermore, I’d have to manually add each student to my Google calendar since that’s how I actually keep up with things.

As a denizen of the space future (and the so-called “Millennial” generation), I feel entitled to not ever have to do things on paper when possible. Or do rote processes manually. That’s what computers are for, right?

So! After some googling and some reaching into my brain’s way-back machine to pull out the fragments of knowledge I retained from my programming classes back during my undergrad, I’ve solved my problem! Well, to be more truthful, someone else has solved my problem, and I adapted their solution for my needs.

What’s it look like?

First off, no more paper! Students will go to a specific page of my class website to see a reassessment calendar and form:calendar

form

They simply fill out the form, hit submit, and they’re done! Within seconds, an event is created on a special Google calendar that I’ve created specifically for this class, in this case my AP Physics 1 class. Students see their name and whether they signed up for a morning or afternoon session on the calendar. They (and I) can click the event to see what standard they’re coming in for, when they submitted the form, and what they said they’d done to prepare.

calendar2

How’d I do it?

I have a light background in computer programming, so from the get-go I figured that what I wanted to be done could be done. The big question is “how do I get Google Form to ‘talk’ to my Google Calendar?” The answer is Google Scripts!

Instead of  learning the Google Scripts, Calendar, and Form/Spreadsheet API, which I totally could have done if I wanted to spend the time, and piecing it all together myself… I turned to The Internet and found this tutorial from Matthew Koster. Much to my pleasant surprise, it does almost exactly what I wanted it to do straight “out-of-the-box.”

The instructions given are quite detailed, and the code provided is written clearly with comments. They do require some basic knowledge in programming to understand, though.

As I said, it did almost exactly what I needed it to do right out of the box, but I did have to tweak a few things. I’ll detail that below.

Adapting it to work for me

The one tweak that anyone implementing this will need to make is modifying some of the variables at the top of the code:

code1

First off, I had to get the calendarID for the calendar I wanted to use. I got it by looking at the “Calendar Settings” for my calendar

calendarID

I copy+pasted the Calendar ID here into the calendarID variable in the code. This tells my script which calendar to use.

Next, I had to modify the startDtId, endDtId, titleId, descId, and stdId variables. Note: the stdId variable I added myself. It wasn’t a part of the original code. These variables identify the columns from the Google Form response spreadsheet that contain the data I want. So, looking at my response spreadsheet:

spreadsheet2

So, for the startDtId (short for “start date ID”), I used column 3. I used column 3 for the endDtId (end date ID) as well. I’ll discuss why I did this later. I wanted the title of the calendar event to be the student’s name and whether they were doing a morning or afternoon session, so I used columns 2 and 4 for titleId1 and titleId2 for those two pieces of information, respectively. The standard (stdId) and what the student had done to prepare (descId) I wanted to appear in the details of the calendar event.

Another tweak was that I changed the createEvent function to a createAllDayEvent function:

calendarfunction

More on why I made this change below.

Challenges, limitations, and improvements

One challenge I came upon was that I didn’t want students to have to put in the actual time they were coming in for. I didn’t want to rely on them to put in the exact right start and end times every time. I just wanted to let them select Morning or Afternoon and leave it at that. Unfortunately, the original code I adapted wants you to submit a time along with a date. I tried figuring out how to get the script to translate them selecting “Morning” or “Afternoon” into a time on the calendar, but gave up. I’m know there’s a way to do it, I just didn’t want to spend another few hours figuring it out. One overall goal was to minimize the amount of time I had to spend learning or relearning how to do stuff.

Instead, I chose to just make the event an all day event (hence changing the function from createEvent to createAllDayEvent) and to append “Morning” or “Afternoon” to the title. That way, me and the student will be able to look back and see when they scheduled themselves for.

I also wanted to include which standard the student planned to reassess in the calendar event’s description, which is why I created the stdId (standard ID) variable and assigned it to column 5.  To make sure the standard made it into the event description, I added this line, boxed in red:

calendarfunction2

I basically copy+pasted the sheet.getRange etc function call that was already there and put in stdId. Notice that I also added a + “\n”. The “\n” is code for “create a new line” and the + glues it all together. This made it so the event description would have the standard, then go to a new line, then put what the student had done to prepare, like this:

event

 

Immediately after posting my plans to twitter, @albertfong had an excellent suggestion:

…and I think that it’s totally possible! The tutorial that I’m working from includes how to setup a script that emails the form results when it’s submitted. I’ll need to add a field in the form for students to input their email address and use that in the SendGoogleForm function. It’s definitely an improvement I’ll make soon. Thanks for the suggestion, Albert!

I’m really pleased with the results, and I’m incredibly thankful to Matthew for sharing his code. I could have figured this all out myself, sure, but that would have taken days or even weeks as my coding skills are very, very rusty. I’m excited about eliminating this tedious bit of scheduling and paperwork, and I look for any opportunities to do stuff like this. The more time I save by automating stuff like this, the more time I have for more productive things like writing good feedback.

Advertisements

18 thoughts on “Using Google Apps to Automate Scheduling of Student Reassessment

  1. Do you know a way that will make it impossible for those using the form to create an event on the same date/same start/end time?

    Regarding the problem with entering correct start/end times, I’ve been using a formula in the spread sheet that allows me to create a form using just “period 1, period 2,” etc. The formula uses that information to set the correct start/end times. Then FormMule pushes the formula down and creates the events (I’d be glad to share this with you if it helps…given to me by a very special “helpful person” in the cloud).

    • I’m certain it’s possible to program in some kind of rejection, but it’s definitely beyond my current abilities. The difficult part would be in interrupting the form submission. By default, when submitting a form, it goes to a “Thank you for submitting” screen. I’d have to figure out how to interrupt that. No ideas on how to do that, though.

      I’d love to see how you handled the start/end times! Thank you for sharing. You can email it to my work email trevor[dot]register[at]cherokee[dot]k12[dot]ga[dot]us.

  2. This is awesome. I’m a mom with an Android fetish who has a kid with homework organization issues on an iPad. I need a way to help him organize and manage his time so he can get his assignments done. There are tons of apps but very few that work and communicate between Apple and Android. Google Calendar is a solution but a little too complicated to jot a task down quickly in. Forms are a simple, elegant solution, so I started searching for a way to get forms and calendars to talk. I found your tweak through Matthew’s post and it is perfect for my situation. Once implemented with a few of my own tweaks, my son can enter his assignments and due dates as they are given out and they will populate on our “school” calendar! No special app needed. Plus much easier for him to do quickly which hopefully will result in better usage compliance. Thanks for sharing this!

  3. Hello Trevor, Just wanted to stop buy and say that I am glad to see my tutorial helped you out. I haven’t done much with it since sadly, but happy to see it still benefits others. Your article goes into a little more detail than mine in regards to the layout of the spreadsheet, which is a great visual aid.

  4. Like you I have adapted the script for my use. We use the form to check out equipment for my school. I would like to adapt the script so staff can have lab space for different periods throughout the day, however cannot figure out the If/then statement needed like you refer to with the afternoon/morning sessions. It has been a couple months since the last post. Have you had any success?

    • I ended up deciding not to worry about it as I discovered that simply appending “Morning” or “Afternoon” to the event title worked just fine. You could do something similar by appending the periods to the end of the event name? Sorry, I wish I had more help to offer!

  5. I can’t figure this out at all – I am building a form that people can submit a pickup time for a ride
    I want it to add a cal event to my cal and email creator and 2 others. Very hard for me to put all together. not a super geek. anyone feel like helping?

  6. This is a really great tutorial. I actually want to do something very similar to what yo have done, Trevor. When I am in the script editor and press the “play” button with “create event” as the function it says this: TypeError: Cannot call method “createAllDayEvent” of null. I have no idea what this means or how to fix it. It looks exactly like the one you wrote. Do you still use yours and does it work? If so, would you be willing to share yours so that I can make a copy of it and input my own information?

    Any help you could offer, would be great! This is what my info looks like in the script editor:

    //this is the ID of the calendar to add the event to, this is found on the calendar settings page of the calendar in question
    //Look for “Calendar Address:” and the ID shows up beside it.
    var calendarId = “dangug2f1409coi6pg5eh3srpo@group.calendar.google.com”;

    //below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed)

    //Column containing the Start Date/Time for the event
    var startDtId = 3;
    //Column containing the End Date/Time for the event
    var endDtId = 3;
    //Column containing the Name of the individual
    var titleId = 2;
    //Column containing the Second part of the Title for the event (In this case, morn,afternoon)
    var titleId2 = 4;
    //Column containing the reason for the event
    var descId = 5;
    //Column containing additional comments for the event
    var stdId = 6;
    //Column containing the Time Stamp for the event (This will always be 1)
    var formTimeStampId = 1;

    function getLatestAndSubmitToCalendar() {
    //Allow access to the Spreadsheet
    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var values = rows.getValues();
    var lr = rows.getLastRow();
    //Removed setting of Hour and Minute for the Start and End times as these are set i our form
    var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
    var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
    //Create an addition to the Description to included who added it and when
    var subOn = “Added :”+sheet.getRange(lr,formTimeStampId,1,1).getValue()+” by: “+sheet.getRange(lr,titleId,1,1).getValue();
    //Setting the Comments as the description, and adding in the Time stamp and Submission info
    var desc = sheet.getRange(lr,stdId,1,1).getValue()+”\n” -sheet.getRange(lr,descId,1,1).getValue()+”\n”+subOn;
    //Create the Title using the Name and type of Absence
    var title = sheet.getRange(lr,titleId,1,1).getValue()+” – “+sheet.getRange(lr,titleId2,1,1).getValue();
    //Run the Crete event Function
    createEvent(calendarId,title,startDt,endDt,desc);
    }

    function createEvent(calendarId,title,startDt,endDt,desc) {
    var cal = CalendarApp.getCalendarById(calendarId);
    var start = new Date(startDt);
    var end = new Date(endDt);
    //Manually set the Location, this can be modified to be dynamic by modifying the code if need be
    var loc = ‘Spn Class’;

    //Set the Options, in this case we are only using Description and Location, as we do not need Guests or sendInvites
    var event = cal.createAllDayEvent(title, start, {
    description : desc,
    location : loc
    });
    };

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s