The Anatomy of an Idea - Building a Solution One Step at a Time

I recently came across a post from Jennifer Kloczko discussing the notion of original ideas. In it she documented some of the ideas that she has implemented this year and where she ‘stole’ them from. I too have written about this before, discussing the benefits of sharing and working collaboratively. However, one aspect that Kloczko does not necessarily address is the way in which ideas ‘stolen’ can morph and evolve as they become ingrained in other contexts. This process often starts with a problem. For me, this problem is Synergetic.

This year I have spent a lot of time learning about different facets of Synergetic. A ‘total solution’, Synergetic is a management system with a focus on administration. My work has included developing a reporting solution, setting up the attendance process and configuring online spaces. One particular area that has absorbed quite a bit of my time though has been timetabling.

For secondary schools this is more obvious. You link in a third party applications, such as Timetabler or Edval, to manage things. This however is not the case for primary schools. They have no need for the intricacies of a robust timetabling package, the issue though is that they still need a timetable.

The solution Synergetic offer is a lightweight application called Primary Time. It allows users to create timetable blocks and place them within a visual grid. I have two concerns with Primary Time. The first is that you need to populate a lot of information associated with rooms, teachers, groups and subjects that does not flow through to the timetable file produced. Although there is the means of uploading this information in Primary Time via CSV files (click here for a copy of the different files), these files still need to be made beforehand and maintained moving forward. The second issue is that there is not a direct connection between the two applications. Unlike other software packages that develop a constant connection that allows for a flow of information back and forth, users are required to manually download a file from Primary Time and upload this into Synergetic.

The problem with all of this is that the timetable applications ideally act as the source of truth when it comes to timetable related information. That means if Primary Time were to be used in this way, users would need to follow the tedious cycle of updating Primary Time and then reloading it, every time a change needed to be made, no matter how large or small this change may be. (This issue is compounded by the fact that you cannot download timetable related information from Synergetic and upload it into Primary Time.)

Another factor at play is the reality that most primary schools do not require an explicit period-by-period timetable. Instead, timetables are usually developed around exceptions, with the rest of the time being allocated to a classroom teacher, allowing the to balance and bend their teaching time. Locking in a highly descriptive timetable therefore serves little purpose and is often a hindrance, rather than a help. For most primary schools in Victoria the timetable is required for roll marking purposes, with primary schools mandated to enter results for AM and PM.

An alternative to using Primary Time is manually entering the timetable within Synergetic. Although this is an option, especially when generating roll marking periods, it to is still a very tedious process and not an ideal solution.

After these initial experiences, I was left with the question

How might we use another application to make the development of a timetable for schools easier and more efficient?

At the end of the day, all that Primary Time does is produce a CSV file with six columns: day, period, form, class, room and teacher. This seemed quite simple. I therefore started by trying to reproduce a school timetable by cutting and pasting cells in Google Sheets. This worked, however it was still fiddly, therefore not a feasible solution.

I wondered if there was some way of automating this process, at least generating a basic timetable that could be manipulated. I remembered reading a post a while back from Martin Hawksey documenting a formula for repeating data. (A win for serendipity.) I started with this and then progressively unpacked each column further, addressing the particular requirements. I must admit, I did get some additional help from Hawksey on the formulas that I had conjured together. I eventually managed to put something together.

It involved entering the days in the timetable, the sessions running each day and a list of classes with their rooms and teachers. It would then generate a list with a copy of each class for each session. There were two problems with this. Firstly, it was not easy to add in the specialist classes. Secondly, it was still unwieldy and confusing.

Fine I could make a copy of the timetable adjust it in order to add specialists, one of the challenges with this is working with the timetable in a list format. The ability to visualise the timetable is one of the benefits of using an application like Primary Time. My solution was to recreate the lists as a dynamic table, with a dropdown button to choose the teacher.

To make this, I built on the work of Ben Collins and David at CIFL around the use of the VLOOKUP formula. It also meant I had to INDEX the table to start with the form column. In addition to this, I made a dynamic selector created using data validation to choose the class. Although this answered the visual problem, as soon as different variables (10 day timetable or 8 period day) were added then the table would break. Maybe there is another way I could do this, but I felt like I hit a wall, so I decided to focus on making a simpler solution.

I had started out with the intent of making it easier to create a timetable for Synergetic, so rather than worry about creating a full timetable, I instead turned my attention to creating a timetable solely for timetabling purposes. Rather than create a line for each period, I focused on creating a line for each roll marking period. This way I did not have to worry about anyone making sense of the lists of periods and classes, instead the end user would enter their values and download the corresponding CSV file. The problem that remained was how to make this fail proof.

Spreadsheets can become busy places very quickly and the sight of lengthy formulas puts a lot of people off.

=TRANSPOSE(SPLIT(REPT(JOIN(“,”,ARRAYFORMULA(REPT(SPLIT($H$4,”,”)&”,”,$I$3))),$I$2),”,”))

The challenge then was to focus on inputting the values. I moved the inputs from the page with the formulas and made a separate sheet for that information. After some feedback I then split this information again, with one sheet providing a space to list the teachers, classes and rooms, while the other sheet providing a summary of the days in the timetable and the roll marking periods. Although this hopefully made it easier, there was still the challenge of downloading the CSV file.

To me the process was clear. Enter the classes and definitions, then download the timetable file. The process though of clicking on the right sheet, going to File and then downloading a CSV provided too many concerns. I asked around if it would be possible to turn it into a script. Some colleagues said yes, but suggested just focusing on the downloading of the CSV, with that being the particular point of contention. So I did what I often so, returned to Google.

I have wanted to explored Google Apps Script for a while, but always found other things to distract me. Finally I had a clear purpose. To start off I worked through Ben Collins’ introduction to Apps Script. It provided a useful starting point and a button for my script, but it did not address my particular challenge. After reading numerous forum posts and scrolling through the Developer Page and Drive APIs, I stumbled upon a code that Michael DeRazon had shared on GitHub for downloading a Spreadsheet to Google Drive. As with all things borrowed, I took to bending the code to fit my needs, but none of the changes that I tried worked. I eventually had a colleague look over it and provide some guidance. He pointed out that there was a loop that was messing things up and came up with the solution of downloading the CSV file to Drive and then download to the computer.

Although this was not necessarily the solution I had hoped for, in that it downloaded a copy to Drive before downloading a CSV to the computer, it at least addressed the problem, making the creation of a timetable easier and more efficient. You can get a copy of the sheet here.

For those who got this far, well done and thank you. It would have been easy to have just shared a copy of my resource and be done with it, but I think that the real value is found in the thinking behind it all. To me, this captures the power and potential of digital technologies as summarised by Richard Olsen. He breaks it down into the following: