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:
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.
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:
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
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:
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:
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:
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:
Immediately after posting my plans to twitter, @albertfong had an excellent suggestion:
@TRegPhysics Is there something that’ll email results (columns/rows) from a spreadsheet of the google form?
— Albert Fong (@albertfong) July 23, 2014
…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.