Auto-generating individual schedules from a master schedule?

August 2, 2015 8:34 AM

Last year, my school used a google spreadsheet as a master schedule, both in general and for days that differed from our normal weekly schedule. It was very effective for knowing where a certain class was or who was using a certain room, but it was not teacher-friendly. I'd like a way to take the master schedule and auto-generate a teacher's individual schedule. Is there a way to do so without a lot of extra work? Specifics inside.

One day of our master schedule looked something like this: (please excuse formatting)

						MONDAY
--------------------------------------------------------------------------------------------------------------------------------
		|	Homeroom 9X - Rm 1	|	Homeroom 9Y - Rm 2	|	Homeroom 9Z - Rm 3	 |
-------------------------------------------------------------------------------------------------------------------------------
Period 1	|	Course A (Teacher J)    |	Course B (Teacher K)     |	Course C (Teacher L)	|
Period 2 	|	Course D (Teacher K)      |	Course E (Teacher M)    |	Course F (Teacher J)  |
Period 3 	|	Course E (Teacher N)      |	Course F (Teacher K)     |	Course G (Teacher L)	|
Period 4 	|	Course A (Teacher L)     |	Course B (Teacher M)    |	Course C (Teacher N)	|

Each day had their own table like the above. Color coding helped somewhat, but only per subject area, not per teacher.

An example of what I would like to generate:

--Teacher K--

Monday
Period 1: Course B - Homeroom 9Y - Rm 2
Period 2: Course D - Homeroom 9X - Rm 1
Period 3: Course F - Homeroom 9Y - Rm 2
Period 4: [PREP]


Next year's constraints:
- Master schedule should look similar.
- We already use google calendar for events and would rather have a separate weekly schedule.
- Free or low cost software if needed. We do use google apps for education. All staff have access to Macs on a regular basis.
- Entries should be easy to input. (I do not want to recopy information from a schedule unless absolutely necessary.)
- It is possible to generate many master and individual schedules, incl ones for half day, special events, etc.
- Entries can be easily changed and individual schedules can be regenerated.
- Prefer to generate either one, some, or all individual schedules as needed.
- Courses do not meet the same time every day.
- Rooms will now be assigned by course (Math, English, etc) instead of by Homeroom (10A, 10B, etc) These could be listed along with course and teacher in the individual cells.
- Homerooms will often travel together, but will sometimes be split in two: Period 2 for Homeroom 9Z might have two entries: Spanish with Ms. J and Art with Mr. K.
- During some periods, a teacher will not be listed. On their schedule, it should say "[PREP]."


Any suggestions would be greatly appreciated. Thank you!

posted by wiskunde to Work & Money (5 answers total)

We use Google Calendar for this at my work, but I see you’d prefer not to. If it makes a difference, each room in our office has its own calendar and can be “invited” to a meeting, which allows our staff to produce a printed schedule separate for each room in the morning, taped to the door as a reminder. By inviting a room and a teacher to an event, you’d have the ability to see separate calendar views for each.

In a spreadsheet, I think the ability to filter might be helpful. With a unique identifier for each teacher, that would be possible. Names are not great for this because they can overlap, but a short unique thing like "T20" for "teacher #20" or the teacher’s email address would make it possible to use the filter feature to show only matching rows and columns. The difficulty in your example is that filtering wouldn’t collapse the columns; you would still be seeing all of rooms 1 and 2, but not 3.
posted by migurski at 9:50 AM on August 2, 2015

I'm transitioning our organization from a very convoluted Google spreadsheet our end-users struggled to use to Zoho Creator and it is beautiful.
posted by teremala at 10:21 AM on August 2, 2015

You can do this, although I doubt that Google Sheets will be powerful enough. Do you have access to Excel? Then it's a matter of creating a table from the master schedule and using VLOOKUP formulas to create individual teacher schedules.
posted by Automocar at 11:37 AM on August 2, 2015

You can use vlookups, cross sheet references, and other fancy stuff in google sheets. I'm amateur enough that I can't look at your data and tell whether it will work, or what the formulas would be, but I've hacked together useful sheets using similar tricks in the past. Not so useful without more concrete suggestions, but... the google sheets formula list.
posted by Jack Karaoke at 3:48 PM on August 2, 2015

Idea 1: create a tab for each teacher that's a copy of the original. Use conditional formatting to hide cells that others are teaching (if cell does not contain teacher name, make text white). Not exactly the format you were hoping for, but not too difficult, and should be easy to read.

Idea 2: Google apps script! If you have someone with some JavaScript / programming experience, or someone willing to learn, this will extend the functionality of your school's google apps x1000. You could even use it to auto-generate calendar events based on your spreadsheet, or send email reminders. It's great.

Very roughly, here's how I imagine the script would look. These would all be nested loops as I tried to indicate with the dashes:

Loop through the list of teachers (which you might want listed in the spreadsheet along with email addresses). For each:
-create a new spreadsheet
-name the spreadsheet based on their name (e.g., Teacher J Fall 2015)
-even share the spreadsheet with their email address!
-loop through the period rows, for each:
--loop through the room columns, for each:
---if the cell contains teacher's name, put the cell content and the room name in that teacher's spreadsheet (next to the period name)
---if the teacher's name is not found, put "prep" in that teacher's spreadsheet (next to the period name)
posted by beyond_pink at 8:26 AM on August 3, 2015