Thursday 25 August 2016

How we do Activities (the 2016 version)

The way we have been running our school activities and athletics has been evolving over the past two years.

We started with a paper system in 2010 but quickly started using Google Forms, Sites, Docs and Sheets.

Since then we have learnt a bit (A LOT) and made a few changes, used a few different apps and add ons, improved some things and simplified others. We have new staff, some staff have left. Our process isn't perfect and it still has scope for improvement. It seems like this little project will always be in beta, which is fine, better than fine, it is important to always be in beta.

So what has changed? How are we doing things now and how are we going to do things in the future?

We still have an activities Google Account '' 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 We forward all emails sent to this address to our actual activities coordinator. This has been very useful because in three years we have had three different activities coordinators and this year the role has been shared.

We still have our activities Google Site which has now also turned into out activities and athletics blog

We still have a page with all the activities and sign ups using a Google form embedded into the page.

How has the process changed?

This is what we do now.

1. two to three weeks before student and parent sign up, teachers fill out a Google form letting the activities coordinator know what activities they want to be a part of. The activities coordinator uses this information to create the student / parent Google form sign up. They also use the data 'using the save as doc Google sheets add on to create a list of all available activities. This list is posted on the site as an embedded Google doc, that can be modified

The site is sent to parents and is available on our school splash (launch page) which is available from the school website.

2. The Google form is embedded on the Activities site and students (secondary) and parents (elementary) can sign up for their activities. Each day and time (morning, lunchtime, afternoon) has a drop down menu to prevent students from signing up for multiple activities on the same day. For a while the form had four pages (one for each section of the school) this made sorting the data complicated. We now have two pages (Elementary and Secondary). Next we will probably go back to one and make sure we are careful about how we name our activities on the Google Form.

3. We used to use Ultradox trigger to automatically generate emails to the students and parents, now we use 'Form Mule' to automatically generate the emails. It is free, easier to use and allows us to send more emails per day (up to 1500).

4. The form has two pages, one for elementary activities and one for secondary. There are also multiple options on days with before school, lunchtime and after school activities. To generate a summary of what each student signed up for on each day I used the concatenate function in Google Sheets
This gave us a nice summary column for each day which made the email to parents and students easier to create. We have a one page template which contains a table.

It is very important that we are careful and deliberate about how we name the activities. If the activity is in both secondary and elementary they must exactly the same name. If an activity is on multiple days this needs to be indicated in the name (usually the day of the week in brackets after the name "swimming (M)". We also need to label paid activities. All of this labeling is very important when we start sorting and organising the data.

Warning from now on some parts can get a bit tech heavy. This link takes you to an example Google Sheet where you can play along or copy formulas.

5. In Elementary the parents sign up themselves, so they know what their children have signed up for. 

In Secondary the students sign up and we wanted a way to let the parents know what their kids have signed up for. In the past we asked students to add their parents email address, this was problematic because there was no consistency with email addresses (people didn't use their school email) or the email address was typed incorrectly. 

To solve this problem we used the VLOOKUP function, I have another google sheet (downloaded and modified from Managebac) with the student email addresses and parent email addresses. The function below will search my other sheet 'list of parents and students' looking for the student email address (this came from the form automatically as the students had to be signed in to complete it) it then returns the value next to the student name (which is the parent email addresses)
Then I use the concatenate function to combine the student email address and the parent email address into a single value in a new column. The comma allows multiple email addresses to be used at once.
This new value is then used by Form Mule as the email address to send the personalised email to. This personalised email uses the data from step 4 in a table to give the students and parents a summary of what has been signed up for.

Form mule is then set up to send the email on form submit.

Students sign up via the form and an automatic email is sent to them and to their parents letting them know what they have signed up for.

Organising the data

6. Once the sign ups have closed, the activity coordinator then needs to organise the data and decide what activities can run and what can not.

We used to use a query to send the data to a new sheet, we still do this to generate a list of students who need bus transport

Making 50 new sheets for each activity is time consuming, so now I

use this Array formula function to generate a list of unique values from my form responses, which is a list of all the activities that have been signed up for.

Then using data validation I create a drop down menu in a new sheet

I then set up the new sheet with the headings I want.

Using this following formula combines a query with the drop menu, so now you have an interactive list where you can select the activity and then it returns a list of all student who signed up for the activity. Decisions can be made about the activity runs or if extra space or staff are needed. It is interactive and updates as new responses come in (if the form is still open and sign ups have not closed)

The activities coordinator can then copy and paste (making sure to paste values only) to a new sheet to create any list of students signed up for activities that he or she likes.

We then do the same thing for grade level so teachers or admin can search for students in their grade and see what they signed up for.

An example spreadsheet with all the formulas can be found here so you can copy and paste and create your own magic.

Sending out a final confirmation email (and invoices)

7. If students need to be removed from activities because the activity is not running, they change their mind or the activity is  over subscribed, they can be taken directly out of the original form responses sheet. This then creates our master list of all activities that are running.

This master list than then be used with the above drop down menus to make an interactive list for teachers.

The coordinator drafts an acceptance email that tells students and parents which activities they have been accepted in and any fees they have to pay (for paid activities). This includes links to sport websites, agreements or attendance guidelines.

We have some paid activities and the email acts as our invoice. To generate the amount students need to pay I copied the list of activities and then did a find and replace to replace the name of the activity with the amount, then by summing up the amounts we had a total column.

When then use Form Mule again using the acceptance template to send out the final acceptance email. This time we use the send conditions and have two separate templates. One for a student who has paid activities and owes money (condition is total amount column is null) and one for students who don't have paid activities (condition is total amount column is not null). This allows us to personalize the email even further.

These emails can be sent in bulk rather than being sent on form submit with the press of a button.

8. The activities coordinator then created attendance lists in Google Sheets (by copying and pasting from the drop down menus) and shared this with all staff and put it on the activities website (in the past we asked staff to create their own) that way anyone who has viewing or editing rights can see at a glance who is at activities.

Teachers are encouraged 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.

We haven't used the awesome table on the site any more because we now have the interactive sheets and they weren't used that much.

We have improved and changed how we do activities but we still have room for improvement. I am really happy with the way the process has evolved and survived three different coordinators.

The three major changes from 2010 are
  • using Form Mule instead of Yet another Mail Merge, 
  • the introduction of email invoices and interactive sheets
  • automation of adding parent school email addresses to the student email addresses in secondary. 

In my last post there were a few things I wanted to change

here they are in blue below

Some improvements that we already know we need / want include
  • parent Google accounts to allow them to log into our domain (Done)
  • links on the school web page / creation of a splash page for students / staff / parents (Done)
  • a workflow / diagram so all parties know the procedures including dates for each stage of the process (Done, this blog post?)
  • a streamlined attendance function (Maybe, still some work to do)
  • an automated email gets sent to the activities coordinator when students are marked absent. (Not yet)
  • a way for students / parents to find out what activities they have signed up for (Not yet, maybe we do need the awesome table although we don't want parents to see other kids activity sign ups)

The other improvements we still want to see include

  • a drop down list for bus locations
  • a simple form (a single page with no repeating questions) to make the sorting of data easier
  • generation of email lists so teachers can easily email all the students in their activity
  • a link in the email allowing students or parents to modify or edit their sign ups (so they can do it themselves. 

Wednesday 10 August 2016

The Amazing Race

This year, for my usual start of year session with staff I was tasked with reminding the staff some of the systems we use at school

  • How to post daily notices
  • Creating Google sites
  • Using Google Photos
  • Sharing links to PDF's hosted in their Google drive
  • How to book meeting rooms
  • add a help desk request
plus a few other common tasks that staff need to do with computers

I could have lectured and reminded the staff about all the school systems we have in place. I could have even done a really cool presentation Zen style presentation with my own photos and wowed them with my presentation skills.

Rather than lecture, I chose to model active inquiry-based learning. This involved more work for me, but the results were much more effective and therefore rewarding.

I got the idea from Wesley Przybyoski at the Google Apps Summit in KL. Wes' Amazing Race Site

I developed and facilitated a Google Apps Amazing Race Challenge, whereby staff teams completed a series of online challenges; as they completed each challenge they submitted their product and were sent to the next location / challenge. This continued until they finished racing around the “world”.

The Process

I first posted a link to a Google Form (that was the sign up), on the introductory slideshow. I then used the Form Ranger Google forms add on and the data from the sign up form to populate a drop down question in the next 7 google forms so that the team name would be consistent. Form ranger will take data from a Google Sheet and auto-populate questions in a Google Form. So it is great for creating dynamic interactive forms where the questions can change depending on other form submissions.

Once they completed the sign up form, (using the Form Mule add on) they were emailed the first challenge as a my maps link. You can set up custom emails using Form Mule that are activated once a form is completed.

I created 7 different MyMaps (Example map no 6). These maps contained the tasks that the staff had to complete. Each task had to be completed and a Google Form filled in with a link to the product as evidence they completed the challenge.

Once the staff completed the challenge (via a Google form) they were emailed the next challenge once again using Form Mule. They then got the next map with the next location and the next challenge.

This continued until they had completed all seven challenges. I commandeered two staff members to be the judges and recorders. They were given access to the spreadsheets and gave points depending on the accuracy of the answer, speed and extra points. These were then recorded on another spreadsheet which auto updated on a slide in the Google presentation.

The Result

I am sure someone won, I don't know who, it doesn't really matter. I do know that all the staff had to work together in teams and actually do the type of tasks they were expected to do on a daily or weekly basis at school. If they forgot how to do something they also had some contacts that they could call on that could show them how to do it

The staff loved it and participated fully, I even had one of our new teachers come up to me afterwards and say how she loved the fact that anything I presented was done in such a creative and interactive way. She said it was much better than the usual PD sessions she had attended.

During a debrief, many of the staff commented on how it was much better to do active hands on PD rather than listen to a lecture. A few even booked some coaching time with me to help them develop their own Amazing Race for their classes.