6 Day Cycles & Google Calendar Reboot

This system has been updated here.

It’s schedule time again! The problem is simple: many schools have schedules that follow an a-typical cycle, often using six days instead of five, and Google Calendar doesn’t have a mechanism for recurring events like this. This post cleans up the six day scheduler that I wrote about a year ago. This customized Google Sheet uses the New Visions for Public Schools script “formMule” to automatically create a custom schedule in Google Calendar for the year. My secretaries, nurses, and other support staff loved being able to use Google calendar to easily find any class in the school. This post will provide step by step instructions to make it your own.
.
Start by making a copy of the 6 day scheduler. Click on the 6 Day Cycle tab and adjust the schedule to fit your system. This sheet is currently formated for the 2014-2015 school year. The Weekday, Six Day Cycle, and Month columns are easily built with functions, but I use a different sheet to calculate these values and then import them using the pushData script. It’s good to use direct values here; there are a lot of dates and all those functions slow down the sheet. Adjust the "Six Day Cycle" column If your schools uses something different. There's no reason you can't have a four day cycle!

.
Next, click the faculty tab and update accordingly. The email column is optional and only needs to be used if you want to invite teachers to the events in the schedule. I skip it because this leads to lots of email all at once. I don’t want to spam my teachers!

..
Click on the sheet named “Teacher Schedule.” The scheduler currently accommodates up to 9 periods in each day. Please note that you don't need to use all the periods - the sheet will only create events where the "Description" column is used. Customize the “Start Time” and “End Time” columns to reflect the time slots for your school. The Description column is used to name the events in your calendar. As mentioned before, events that have values in the "Description" column will be created in the Google calendar. These cells are validated from column A in the Faculty List Sheet. Using the validated values (the down pointing arrow on the right side of the cell.) saves lots of time in the long run.

.
I’ve packaged the formMule script with this sheet, so it should come installed when you make your copy. I’ve also included installation instructions below If something goes wrong with the package. The instructions are at the end of the post

.
Things I’ve learned:

This project evolved from a lab sign up Google sheet. The sheet was used successfully by teachers all last year to sign up for labs and carts. Appointments were automatically added to a shared calendar. It mostly worked as planned, but teachers found using the sheet to sign up for lab time awkward. More troublesome, teachers had difficulty deleting and updating events. Scheduling frustration never helps anyone, so I’ve decided to forgo the Google sheet for teacher sign ups and give Appointment Slots a chance. It will be a pain to set up but I think it will be a lot easier for teachers. The six day scheduler, on the other hand, has worked great!

.
formMule Installation

Install formMule from the script gallery (Tools -> Script Gallery.) Click the formMule menu that is now available, run the initial installation, authorize the script and accept the terms. Click the formMule menu again and select “Step One: Define Merge Source Settings.” Set the Merge source to be “Master Schedule” and click “Save Settings.”

.

.

.

Click the formMule menu again and select “Step 2b: Set Up Calendar Merge.” Click the checkbox next to “Turn on calendar-event merge feature.” Under “Create Event Condition” set the drop down box to “Description.” Type “NOT NULL” in the field to the right of “Description.” Set “Event Title” to ${"Description"}, if this is for a particular calendar, Art, for example, I will add “(art)” after ${"Description"}. Set “Start Time” to ${"Start Date Time"}, and “End Time” to ${"End Date Time"}. Click “Save Calendar Settings” at the bottom of the window.
.

.

.

Go back to the formMule menu and click “Preview and perform manual merge.” Look at the “Event Create” tab and make sure that the values are correct. Click “Run Merge Now” on the bottom of the window if everything looks right.

.

A year-long schedule can have thousands of events and a single merge might not complete the year. I add a time based trigger and set it to fire every hour. The complete schedule will be added to the calendar in less than a day!

.