Creating a Sign Up system with FormMule and Google Spreadsheets

I think we are beginning to leave the era of the computer lab. The specter of the common core tests have done at least one good thing: motivate a progression towards one to one computing in schools.  That said, schedules and sign ups are still the arteries that make schools tick. The lab and cart schedule are no exception. Google calendars helped to update the conventional sign up sheet, but there are parts that just don’t fit school culture. Google Calendars don’t support 6 day cycles (many schools, including mine, are reliant on this awkward format) and a calendar without time slots is too easy for teachers to abuse (you know who you are.) Last year I tried appointment slots, which work well from some schools, but I found it complicated for my teachers to change or delete events. It turns out that my teachers change events a lot.  

Thankfully, Google Scripts provided a solution this summer. PushData and FormMule have allowed me to customize a system that will “just work,” and best of all, I don’t have to think it about it (much) once it’s set up.  I have four schedules for various lab carts and rooms and the PushData script migrates the schedule, the six day cycle, and my faculty list to each.  When a snow day changes the 6-day cycle, all I have to do is make one change on the master 6-day cycle sheet and all the schedules update.  Once on the schedule, various functions move the data around to automatically populate and organize a vertical agenda for each month.  Teachers can select their name from a validated drop down list, add a description to their event, paste in a relevant Google Doc URL and invite a guest.  Thanks to FormMule, the schedule automatically adds the event to a Google Calendar and sends reminder emails to participants.  If a teacher needs to modify or delete an event, they just record the change and make a note in the “Update/Delete” column, then POOF the calendar is fixed.

It took me a little while to figure out how to configure the Spreadsheet to behave correctly.  I wanted to share it here just in case it's useful for someone else.  All of the settings will copy with the spreadsheet - you'll just have to add a calendar ID.  A really big thank you goes out to Andrew Stillman whose awesome scripts made the whole thing possible.

Click here to open the Demo Schedule sheet.  Read on for setup notes and other things.

What you need to do to set this up:

Settings for FormMule step 2b





• Copy the sheet.
• Create a Calendar to sync
• Copy the calendar ID
• Run the FormMule Script.
• Configure the FormMule Script (See the screen capture for step 2b above.)
• Paste calendar ID into the appropriate spot of the FormMule settings window.
• Add your own faculty to the Faculty sheet.  I push this out with Pushdata so that changes are easily maintained.
• Set up your triggers: - I added a one hour trigger to the manualSend handler because I am not using a form submission and I want teacher to be able to see the schedule when they sign up - I added a clearRange handler that will reset the “update/delete” column on each month’s sheet.  This will reset the edit condition for multiple revisions. I set a daily trigger that fires every morning at 2AM.
• Populate the “Faculty List” sheet with your teachers and their google account emails. I used PushData for this because I have several schedules and don’t want to keep track of multiple edits.
• Edit the six day cycle sheet to reflect your calendar.  I use PushData for this, too.
• Edit the time slot sheet (Yup. PushData.)
• I have named ranges for all the rows and columns with formulas, you may want to protect these if you use it in your school, just in case a teacher accidentally changes something.
• Hide all the sheets that aren't labeled for a month.

What I want to make better:

I just don’t have time to fix all the things that work, but could work better.  The time slots are currently rendered to the month sheets using the ARRAYFORMULA function.  This works, but it is inelegant. I did this because I needed to account to holidays and the daily repeat of the schedule, doing it manually was the easiest path.  In the next iteration I want this to account for holidays and the daily schedules automatically.

I also push the dates to each month using ARRAYFORMULA (This is in hidden rows at the top of each sheet.  I used the formula to quickly develop the rest of the sheet and now I don’t really have the time to go back and fix it.  I’d rather something like VLOOKUP here.

The next time I work on this (probably next September) I will address these issues.  If you get to it before then, let me know! As far that goes, let me know if you see a better way to do anything else!

Something I learned:

Go easy on time based triggers. I added two triggers to Andrew’s FormMule.  I wanted changes to schedule to be quickly reported on the calendar so I made the trigger fire every 5 minutes. This created an error after I used up my allocated CPU time.  I have since changed my triggers to fire every hour and it works much better.