Friday 16 January 2015

Activities Activities Activities

As with any new school, we have the opportunity to set the way things are, we don't need to do something just because it has always been done that way.

With our after school activities this is a wonderful opportunity. We have just started season two and with effort, team work and trial and error we almost have a system we are happy with.

For Season One
1. We used a Google form for teachers to nominate which activities they would like to run.
2. We then used another Google form for the high school students to sign up,
3. Elementary students signed up on paper which then had to be added to our activities spreadsheets which was created manually.
4. Originally Elementary had two seasons while secondary had one
5. The roll for each activity was taken on another spreadsheet (which also had to be created manually)
6. There were lots of email floating around, messages on managebac for students to sign up etc

It was messy and thank goodness we only have 150 students.

After a few chats, a visit to the KL GAFE conference and some online research we greatly improved the process for season two and almost have it exactly how we would like it.

So how do we handle activities sign ups now?

This might seem a bit complicated, but it has automated a lot of the sign up process and provides a better experience for teachers, students, parents and the activities coordinator.

1. We created a google site for activities at IGBIS  This site has some embedded Google Docs so the activity coordinator can easily update activity information or news (he just has to update a Google doc that is in his starred area of his Google Drive)

2. We have a Google Account 'activities@igbis.edu.my' that all sites, forms and documents are created through. That way the resources are not tied to a teacher but to a role. It also means any emails that are sent are sent from activities@igbis.edu.my. We forward all emails sent to this address to our actual activities coordinator

3. Two to three weeks before students sign up, teachers complete a Google form letting us know what activities they want to run. We have this information in a google spreadsheet which is easy to manipulate.

4. Once the form is closed we then use the Google sheets add on "save as doc"  to generate a list of all the activities including their description. The Activity coordinator then tidies the doc up and it becomes our list of current activities on the website. The doc is then published to the web and embedded on the site so that any changes that happen to the doc appear on the website.

5. We then use this information to create a google form for student sign up. The form is put on the website. This is done manually with a drop down for each day an activity is offered. That way a student can't sign up for two activities at the same time.
We would have liked to use a students log in details but we need elementary parents to also fill out the form and they don't have school google accounts (yet).

Now the fun starts

5. Once the online form is filled in, we then use ultradox trigger to automatically generate an email to the parents letting them know what their kids have signed up for.  Parents know what is happening and they can confirm that they want their kids to attend an activity that is provided by an outside provider (with the extra cost). The activities coordinator doesn't have to do anything and the parents stay informed. We also know when the parents or students type in a wrong email address as we get a bounce back message.
This took a bit of fiddling to get it just right (because we used drop down menus the first item on each drop down had to be a blank so that ultradox didn't think the first option on the drop down was selected).

6. On the Google form responses sheet I created a tab for each activity. I then used the query language to automatically move the student detail into the correct activity tab. This means that when a student signs up for an activity the list for that activity is automatically created. See this video for detail on how I did it


The activities coordinator now has a list of who has signed up for each activity, this is created as each response is made. The coordinator can keep an eye on popular activities or see when an elementary student has signed up for a high school activity and visa versa.

7. Once the sign up is closed we then close the sheet, the coordinator can check the sign ups for numbers and make sure all sign ups are legitimate. He can reopen the sheet and manually add any extra students if we have new students in the school or someone was away during sign up etc. He can also remove students or send emails to parents and students if we don't have enough students to run an activity.

It is fine to delete students from the original form responses sheet, they will also be removed from the other sheets. It is important that if we want to add an extra student we use the sign up form and don't just manually add the student to the sheet. (we don't want to mess up activity names, which will then break the sheet)

8. This sheet is then shared with staff so they can see who has signed up for their activity.

9. Once all activities are finalised we duplicate the form responses sheet and use yet another mail merge to send a final email to parents confirming the activities that are running.

10. Using the original responses we then create three other sheets (One for each afternoon of activities) and use this template to create an attendance sheet for each activity at the bottom. Here it is important to copy and paste the values only, so that teachers can add students or remove students once the activity has started. 

The sheets are then linked to the staff area of the website, even though the website is open the sheets and documents on the staff page (which contain student information) are protected because they are google docs that are only shared with staff at school.

The teachers can now take attendance for their activities. I encourage teachers to make a repeating calendar event with a link to the attendance sheet. That way they get a pop up reminder (on their computer / iPad / Phone) that reminds them they need to take attendance with a direct link to the attendance sheet. One click and they are ready to take attendance.

I would really like to find a better way to take attendance, but haven't found anything that works like attendance on Managebac or Powerschool.

11. We then duplicated the list of activities and used this nice little google sites gadget Awesome table to create an interactive table so anyone on staff can search the activities by student name, activity or day.

This is very handy for booking busses and for finding out where a particular student is.

All of this took a bit of trial and error, I quickly changed the attendance sheet once it was shown to the staff. Originally all activities were on one sheet and the sheet contained too much information and we were asking the staff to add their own attendance dates etc.

That is about it, we still have some work to do, but we are streamlining the process and have come a long way in less than six months and starting from scratch. A major advantage is that because much of the workflow is streamlined and automated as the school grows the amount of work doesn't increase at the same rate. 

Some improvements that we already know we need / want include
  • parent Google accounts to allow them to log into our domain
  • links on the school web page / creation of a splash page for students / staff / parents
  • a workflow / diagram so all parties know the procedures including dates for each stage of the process
  • a streamlined attendance function
  • an automated email gets sent to the activities coordinator when students are marked absent.
  • a way for students / parents to find out what activities they have signed up for


plus whatever else we think of as we go along. 

Maybe by the start of next year it will be perfect

Yeah right!