Building a Curriculum System - Part1: The Calendar

I often need to match a Sheets system to our school calendar. This tutorial will explain how to create a a list of dates between two variables (the start and end of a school year) and indicate which days are holidays, conference days, and half days. Just as importantly, the variables make it easy to reuse each year. This system is the core of some of my more complex projects. It is the centerpiece of my Google sheet curriculum project as well as the  scheduling platform that I use to organize student hosts for our Middle School News broadcast.

First, let's set up the pages that we will need in our spreadsheet. With the exception of a Variables page, you usually don’t want to put more than one data table on a page. Below is a bulleted list of the pages you should create and the purposes those pages will fill.

Lessons

This is an optional page. The “Lessons” page will allow you to select lessons from a drop down to change your lesson sequence. It would also allow you to link other things to the lesson, like a description, SWBATD information, and resources folders.

Instructional Days

This page will list each instructional day and assign a sequential number to each day in the list. This will allow you to shift lessons or assignments if your plan changes.

Variables

Create a variables page. This will store the start and end date for the school year. It will also contain the dates for holidays and conference days.

Setting up the calendar

---

Setting your Variables.

We are going to set the system up to list all the days in the school year in a vertical column. We are also going to create a flag when there is a holiday, superintendent conference day, or half day. We are going to set these values on the “Variables” pages because they will change from year to year. Let's get started.

Click on the “Variables” page. Select a cell and label it “Start of the Year.” Record the start date (Month-day-year) in the right adjacent cell. Go down a row and do the same thing for the “End of the Year.”

IMPORTANT NOTE: I am using cells C3 and C3 for my start and end dates. These cells will be represented in the function below.You will have to adjust the function in the next step if you use different cells.

Skip a column on the right and record the following three headings: 

Vacations | ½ days | Vacation Name

Fill out the “Vacations” chart. Every vacation day needs an entry. If Spring break is a week long it will require five represented days. Half days only get a date in the “Half day column.” Regular days off will have a date in both the “Vacations” column and the “½ day” column.

Building Your List of Dates

---

Now navigate to your “Instructional Days” sheet.  Add a heading in cell A1 named “Dates.” I am going to add an advanced function in cell A2 that will use the start and end dates on the “variables” page to build a list of dates. The beauty of this is I can change those variables next year and the list will update! I will paste the function below and then explain what it does. Full disclosure: I will use the ChatGPT AI to break down the function and then I will edit the AI response (I did need quite a bit of editing), because it is a time saver.

Function in A1: =ArrayFormula(TO_DATE(row(indirect("C"&Variables!C2):indirect("C"&Variables!C3))))

1. indirect("C"&Variables!C2):indirect("C"&Variables!C3): are constructing cell references. They take the values in C2 and C3 from the "Variables" sheet and turns them into cell references, so if C2 contains “08-31-2023” and C3 contains “9-26-2024”, these expressions would allow you to evaluate the cells respectively. The “INDIRECT” part will keep these values dynamic. I can change the dates easily!

2. row(...): then generates an array of numbers starting from the row number of the first cell reference (C1) to the row number of the second cell reference (C10). If your two cells referenced 1 and 10, it would create an array of 10 rows, from 1 to 10. In our example, it is making a row for each date.

3. ArrayFormula(...): This function is used to apply a formula to an entire range, turning a single-cell operation into an array operation. This allows us to create a vertical list of dates that will continue until the “End Date” is reached!

4. TO_DATE(...): This forces the Sheet to use our variables as dates. It will convert a number into the corresponding date. Without this, a sheet will see a string of numbers and assume they are just numbers. We are telling the sheet that this array contains dates and that the sheet should treat them in this way.

 

Viola! I have a list of dates now that span the gap between my start and end dates. Magic. Now Let’s make the dates useful.

Setting the Days of the Week

---

We need to take that long list of dates and add context. Important information includes what days do those dates represent? Which ones are the weekend? Which ones are holidays? Sadly, you can’t have a sheet automatically distinguish a Monday from a Saturday. There is a trick, though. A function called WEEKDAY will convert a date into a numerical representation of the weekday. The sheet considers Sunday the start of the week and will represent it as number “1.” Monday will be “2” and Saturday is, you guessed it, “7.”  Once we have this we can build an IF/THEN statement that will convert the numbers into week day names. Let’s start.

Select B1 on the “Instructional Sheet” and label the column “NoW” (Short for Number of weekday, but make it what you want!) In B2 we are going to add an ARRAYFORMULA. An arrayformula takes a function and applies it to a range of cells. This is super useful and cuts back on all those copy and pasted functions. I will follow the function with an explanation (using edited assistance from ChatGPT.) 

Paste the following function in B2: =ARRAYFORMULA(WEEKDAY(A2:A307))

1. WEEKDAY(A2:A307): This part of the formula calculates the day of the week for each date in the range D4 to D307. It returns a number, where 1 represents Sunday, 2 represents Monday, and so on up to 7 for Saturday.

2. ARRAYFORMULA(): This is a Google Sheets function that allows you to apply a function to a range of cells and get an array of results. In this case, it's used to apply the WEEKDAY function to the entire range of dates, so you get a list of weekday numbers corresponding to the dates in A2 to A307. A2 is the first date, and A 307 is the last.

Great! Now you have a nice list that we can reference. Let's build an If/then statement that will identify those numbers. Click the heading letter at the top of column A and click the down point arrow on the right of the box. Select “Add 1 column to the left.” Label this new column “DoW.” We are going to build a statement that checks the Number of the Week column and returns a text value. This will be a nested IF/Then/Else statement in an ARRAYFORMULA. 

Start by setting your function as an Array. You begin with “=ARRAYFORMULA” when setting up an array. Then add a parentheses and your first IF statement:

=ARRAYFORMULA( IF(

Our first statement is going to check for blanks and return no value. This is a handy trick that I use everywhere that I have a nested IF/THEN in an array. It prevents errors when the end of the list is reached and there are no values to check against. The Arrayformula function allows me to use a range, in this case C2:C. Function statements are surrounded by parentheses and the function components are separated by commas inside the parens. 

IF(logical_expression, value_if_true, else)

I am looking for blank values (IF( C2:C="") which will return nothing (IF( C2:C="",, ). Finally, I have my “else” statement which starts my first nested IF statement.

=ARRAYFORMULA( IF( C2:C="",, IF(

The next statement starts identifying the numbers of the week in column C. All text values need to be surrounded by quotes, eg: “Monday”. Then continue with the next else statement until all seven days are represented. 

=ARRAYFORMULA(If(C2:C="",,IF( C2:C=2,"Monday", IF( 

Congratulations! You’ve set up your dates and identified the days of the week! Now lets determine which of those days have class.

Setting up Instructional Days

---

Create two columns to the left of the “DoW” column. Label these AM and PM. Let's start by indicating that our function in A1 (Column AM) will be an array and set the function to ignore blanks. Then start your nested IF/THEN/ELSE.

=ARRAYFORMULA( IF( C2:C="",, IF( 

Next create your IF/Then/Else Statement to identify weekends. Write your statement for Sunday, too.

=ARRAYFORMULA( IF( C2:C="",, IF( C2:C="Saturday", "Weekend", IF(

Once the weekends are identified, we are going to exclude the holidays and conference days as “Not Instructional.” This is going to use a function called MATCH to compare the dates with our vacations list. We are going to nest this function inside a IF/THEN statement. This will compare the dates in column D with the vacation dates on our variables sheet. If a match is found, it will return “Not Instructional, else it will return “Instructional.”

IF( (MATCH( D2:D,Variables!E3:E,0)),"Not Instructional","Instructional")))))

Did you notice something went wrong? This is because values that did not return a match (the date wasn’t on the vacation list) came back as an error. Lets add a function called IFERROR that will have the match ignore these unmatched dates. We’ll add IFERROR before the MATCH parens and include the IFERROR instructions after the parens.

IF( IFERROR( MATCH( D2:D,Variables!E3:E,0))>0, "Not Instructional","Instructional")))))

Excellent! Now do this same thing for the PM column (col. B) but reference the Half Day column on the variables sheet.

Next we will make the sheet connect to lessons and allow you to change the number of days that each lesson will take to complete, allowing the dates to cascade and correct.

Extension activity: Try to figure out how to change the color of the rows based on whether Column A says “Instructional”, “Not Instructional” or Weekend. This will be a custom formula using conditional formatting.

Calendaring Weird Schedules with formMule

It’s that time of year when I build the schedules for my school in Google Calendar. My school uses a six day cycle (A,B,C,D,E, and F) which does not conform with Google Calendar well. This system allows you to use an unusual schedule to fill in a Google Calendar for the year. Each year I try to rebuild systems like this to make them a little better. In this case I’ve updated the dates, cleaned up some of the functions, added a directions sheet, and most importantly, used the new formMule script that’s built into the new sheets. Another big shout out to +Andrew Stillman and the New Visions Cloud lab team, without them this would not be possible.

This system requires some set up and I’ve tried to make it easy to play along. Please let me know how I’ve done. You can make a copy of a clean sheet by clicking here. From this point on I will be linking to a demo sheet that is already set up.

By default, the sheet is prepared for a six day cycle, though you can create any cycle by customizing column “A” of the “Teacher Schedule” Sheet. Next, you have to update column “D” of the “Day Cycle” sheet. I recommend adding your holidays first (delete mine) and then work your cycle around them. The rest of the set up is outlined step by step on the directions sheet. This demo sheet was used to create this calendar.

This system works well for me; I use it on the elementary level to create special area schedules (PE, Art, Music, etc.) That said, it might not fit your needs. You might also want to look at the inspired work of Christopher Webb. His Google Calendar Import Tool is nothing short of amazing. There’s also a rumor going around that James Peterson is working on an add-on for this kind of thing. I am eagerly anticipating that! Please let me know if you use this system, find a bug, or can think of a way to make it better.

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!

.

Improved Scheduler

This system has been updated here

So, I’ve been tinkering with the 6-day schedule spreadsheet I blogged about last week, and I cleaned up a bunch of the issues that I mentioned in that post.  Most notably, I reworked the sheet to allow for asynchronous schedules (thanks to feedback from Christine Hartooni.)  Now you can create a signup sheet that has a different schedule on each day of the six day cycle, if needed.  I also cleaned up some of the sheet mechanics.  Each month's sheet is now populated by a query and doesn’t need any manual adjustment (that will be great next year.)  The period schedule is populated via a VLOOKUP and no longer needs an ARRAYFORMULA. BAsically, it's just cleaner all around.

Here is a link to the new and improved scheduler.

Set Up:

To use the sheet, simply fill out the faculty list page with your own users and emails.  Then, set up your own 6 day cycle and add the HTML link to your calendar on the “6 Day Cycle” sheet. Next, customize the period start and end dates on the “Teacher Schedule” page. Finally, you want to set up Form Mule.  You can copy and paste my workflow into the script , or you can set up your own. Either way, you’ll want to set a location for the calendar and paste in your calendar ID into step 2b of FormMule.

Create a Teacher’s Schedule for the Year

I had mentioned that I would probably wait until next September to make these fixes, and then I discovered that my design was too limiting and prevented me from using it, too!  Sorry about that.  These changes allowed me to make another sheet that doesn’t use sign ups at all, instead it will plot a teacher’s schedule for the year across a 6 day cycle.  Basically, this modification uses VLOOKUP to populate entirety of each month sheet from the “Teacher Schedule” sheet.  Worked great.

Here is a link to the Teacher Schedule sheet.

Iteration is so important.  I feel like our students don’t get the time to iterate thanks to overpacked curriculums.  But that is a post for another day, isn’t it?

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.