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 'firstname.lastname@example.org' 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 email@example.com. 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)
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.
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.