In this post I'm going to explain how I use Google Forms and Sheets to complete in a couple of hours a task which used to take several days.
At my school, we run "enhancement" classes for all S2 students twice a week. Each of the two sessions has a list of available classes - ranging from games development to crocheting and bushcraft - and we ask students which of the classes they would like to attend, then allocate them to classes (bearing in mind that some classes will inevitably be over-subscribed). If you have to deal with a similar administrative task you may find this post useful. My solution uses Google forms and Google sheets, and relies on some spreadsheet formulas.
In an ideal world, this task would be completed for me by someone else! In reality the wonderful office staff in my school are already working at full capacity; truth be told they are over-worked and under-paid. And so I need to sort it out. If you are a depute reading this and have managed to avoid all administrative work, please get in touch and tell me your secret!
The previous depute used to give every S2 student a paper form with all the choices on it, and have them write 1, 2, 3 or 4 beside their preferences. He would then spend a weekend (or more) shifting these bits of paper around until he had created classes. He had to complete this sorting task twice - once for each of the two enhancement sessions in the week. The piles of paper would then be taken and typed up for entry into our MIS.
As this task approached, I considered how I might use IT to improve the process. I hoped to save time, and to ensure fairer allocations.
I realised that Google forms would do the job, and created this form for the students to complete:
The original form, created in my school's Google Apps for Education account, used the option to require login and collect usernames, so it didn't actually matter whether the students gave me their names.
Once the students had completed this form, I had all their choices in a Google sheet (this sheet is created automatically). At this point it looked something like this (I've cleared the names):
Because I knew I would be repeating this task every year, I decided it would be worth putting a bit of time into streamlining the process, and so I took a copy of the sheet and did the following:
I inserted two columns before the choices, into which the "choice number" allocated to a student would go. For example, if the first student had the number 1 in column F, that would mean they were doing "Ace of Cakes". I then added another two columns which converted the choice numbers into the actual choices in words. This was the tricky bit. The following formula went into cell G2:
=INDIRECT(concatenate("R",row(F2),"C",F2+9),false)
This gave me a spreadsheet like
this one:
When a number is changed in column F, the corresponding words in column G change. You can try this for yourself by following
the link, as I've made the spreadsheet editable (play nicely!)
This made life much easier for me. All I needed now was a summary of how many students would be in each class, based on the choices they had been allocated. I have exactly that on the second worksheet in the sheet. It looks like this:
This summary automatically updates as I make changes in column F. The formula in cell B1 is
=countif('Form responses'!G:G,A1)
Once I had this set up, it only took me about half an hour to sort all the allocations. I started by putting 1 all down column F. This gave me a summary of how many students had picked each class as a first choice. Some of these totals were fine, but some were over-subscribed. For these, I sorted the first sheet to see all the students who had chosen this, and selected at random however many I had to shift to their second choices. I just changed their 1s to 2s and the job was done!
Having completed the allocations, I had them all in the spreadsheet so could easily print out class lists for entry into our MIS.
Forms and spreadsheets rule!
