Behaviour Tracking
Last year Managebac introduced their behaviour tracking module, which replaced their now defunct Intersis system. As a Managebac school we thought it would make sense to use their behaviour tracking, it is built into Managebac, managed by Managebac and we were told it would develop over time.
It did have a few weaknesses, one of them being that only teachers of particular students and administrators could enter behaviour reports and we couldn't control who could sees what information.
The Secondary school set up a system where teachers filled out a Google Form and then use the Data director for forms add on to email the report to the secretary who then input the data into Managebac. Emails also went to the head of grade and the head of student services. This system allowed anyone to enter a report about a student (even if they didn't teach them) and then have it stored on Managebac. There were still limits to who could view notes and no option to record their follow up actions. Because the behaviour tracking in Managbac was new, we were confident it would evolve to a more useful tool over time.
Elementary decided to wait and see what was going to happen with Managebac. They were not happy about having this information input by a person as it might be sensitive and errors could occur with cutting and pasting.
It has now been twelve months and the behaviour module in Managebac has not matured enough for our liking. We feel we need a common format for recording and accessing student behaviour incidents. I was tasked with exploring using G Suite to create our own behaviour tracking system.
The design Specifications included
- It must be simple enough that someone else with a rudimentary knowledge of Google could fix it / build it / repair it if I wasn't around. That means no writing scripts or code (not that I could do that anyway)
- any teacher/staff member in the school should be able to enter a behaviour report
- email notifications to a variety of people, this will change depending on the grade level of the student.
- the data should be safe (access should be limited to those who need it)
- different teachers / admin will need different levels of access (i.e. grade 4 teachers see grade 4 students, Principal can see all reports)
- we need to record a variety of notes and find a way to visualise this information
You can have a look at these view only versions of the sheets and docs if you want to see specifics of how they work and feel free to make your own copies and have a play.
Form
Student Data sheet
Form Responses sheet
link to the behaviour reporting site
We started with the form that secondary use to record behaviour.
Form
Student Data sheet
Form Responses sheet
link to the behaviour reporting site
We started with the form that secondary use to record behaviour.
1. Creating drop down lists for all the students in the School
I knew we would want to filter reports, therefore I didn't want teachers to type in the names of the student they are making the report about. This could cause problems with filtering records if different teachers spell names differently or use different names for the same student. I decided a dropdown list was needed. To create the dropdown list I used the form ranger add on which allows you to create drop down questions from a list in a Google Sheet. (feel free to make your own copy to use with a google form)
I downloaded all the student data from ManageBac as a CSV file and then added it to a Google Sheet. Then used a query to sort the data into three different tabs, in the form we have three drop down questions rather than a single massive dropdown question. (this can be modified for as many questions as you like)
The advantage of this method is that we can change the data in the sheet and it automatically changes the form. That data can be easily downloaded and uploaded from Managebac or even manually added. We can also add homeroom teacher emails to the sheet (which is used later for the email notifications)
2. Adding Questions to the Google form
Then I added the rest of the questions we required to the google form, these can be anything at all. We chosen the following as we will want to track if certain areas, subjects or times cause more issues than others.
3. Email notifications
Once a staff member submits a behaviour report we want emails to go to particular individuals. Specifically all emails go to head of student services and the Principal, homeroom teachers get an email if there is a report about a student in their class.
To achieve this we use a combination of a few functions, Import range, Vlookup formula and query and the google sheets add ons copy down and Form Mule
Step number one was to set up the form responses sheet to link students with their homeroom teachers.
In our original student list sheet we added homeroom teacher emails for each student, this wasn't as hard as it sounds, I filtered by class and then copied the teacher email address to each student. This can easily be done at the start of the school year for all students or when a new students arrives at school.
In our form responses sheet I then created a new tab "students" and used the import range function to import student names and each students teacher into this tab.
Import range allows you import data from one sheet to another, that means if you update the original sheet "student data" the second sheet "form responses" also gets updated.
In a new column (column O) in the form responses sheet I then used the following Vlookup formula
which uses the data in a cell "N2" as a search term in the students tab (N2 is where the student names are located), it returns the data in the cell next to it (homeroom teacher email). This means that once a form is submitted a student name is searched for in the "students" tab and the data in the cell next to it (teacher email) is pasted in the cell. This email address is then used to send out the notifications. Here you can see where the effort of using drop down menus and form ranger comes to the fore. It keeps our data consistent and allows these types of systems to work.
This is a great way to search a list and add extra information to a google sheet that is linked to a form. The only issue is that when a new response is submitted Google Sheets inserts a new row, rather than pasting the data to a row. This means you can't copy and paste the formula all the way down the sheet. because of this I use the copy down add on, which copies the formula down to the next row each time a form is submitted.
Now that the data is ready I use form mule to automatically send an email when a new behaviour report (form) is submitted. Form mule can be set up to send emails to multiple addresses. We use a combination of the homeroom teacher email and the Principal / head of student services. This allows multiple people to receive the details of the behaviour incident. The email addresses can be put directly into the form mule template or they can be added to the sheet using the copy down formula and concatenate (to merge multiple cells together).
4. Access and searching behaviour reports
All of the behaviour reports are stored on a google sheet, this makes it easy to search for particular students, dates, locations etc. We can also generate reports and graphics about behaviour hot spots or times when we have more reports than others.
We also wanted this information to be easily available and searchable for admin members and teachers. It is also important to keep this data as private. For example grade 4 teachers should only see grade 4 students but Principals see all records. We also wanted this information to be easily on the eye.
To achieve this I set up a google site and then used Awesome Table to create cool embedded table into the site
Awesome table really is awesome, it allows you to choose columns from a Google Sheet and display them in a variety of formats. It also allows you to add a variety of search boxes, drop downs and sliders. This makes it really easy to search via date, class, or student name.
I created a new tab on the form responses sheet and used a query that only selected certain rows to create the information that needed to be shared. You can also change the order when using a query.
(here you can also see the types of filters I used for the awesome table)
In my example site the sheet is available for anyone on the internet to view, which we don't want when it comes to sensitive student data. We need a way to make the data private.
Because Awesome table uses a Google sheet all we have to do is change the permissions of the sheet so only certain people can view the sheet, that way if someone who we don't want to view the data tries, they will get an error message or the site wont display anything.
To set up grade level teachers with individual permissions I used the import range function to import all the data into a new sheet, then used a query (or even a filter) so that the only data on the sheet is limited to a certain grade, i.e. only grade four students are listed on the sheet. We then set up permissions so that only the grade four teachers can view that sheet, created a new Awesome table on a new page on the site. That way homeroom teachers can search for students in their homeroom but can not see any behaviour notes of other students.
5. Adding the ability to record the admin response.
This system also allows a response from the head of student services to be recorded. It could be recorded directly on the sheet but this is a bit messy and could be confusing for someone who is not tech savvy. I wanted to avoid data being changed or deleted. When creating these types of systems I think it as always best to limit the number of people who have access to the data.
To add their response it is much neater and easier for admin members to return to the original form and add their response there.
Whenever a google form is submitted, a url is generated that allows the user to go back and modify their response. You have probably seen the edit your response link / email you can get from a google form.
I used this script (available on line) which saves the edit URL to a cell in the response sheet. It took a little bit of fiddling, but is now working nicely. I have just realised that Form Mule will do this for you by simply clicking a box, so in future will be using this and saving myself a lot of hassle
I added an extra section to the form which is only for a member of admin to complete
To allow admin members to get back to the form and add their response I included a link to the edit URL in the Form Mule email. All the admin member has to do is find the original email and click on the link to go back to the form and easily add their response
This response is then recorder on the Google Sheet and can be viewed via the awesome table on the Google Site. There is also a link to the Site in the email body.
That is about it, everything is available via add ons, or simple formulas and hopefully this documentation should be clear enough that anyone else could duplicate it.
We will be testing the system next year and will be closely monitoring how it works, how easily it is maintained and is it is used. There are also plans to tweek it so it can be used in secondary. I will probably add grade level co-ordinators to the emails of students in their grade by adding them to teh student data sheet.
This is quite a long post but I wanted to try and make the entire system replicable (is that even a word) feel free to drop me an email or tweet if you have any questions or queries.
I downloaded all the student data from ManageBac as a CSV file and then added it to a Google Sheet. Then used a query to sort the data into three different tabs, in the form we have three drop down questions rather than a single massive dropdown question. (this can be modified for as many questions as you like)
The advantage of this method is that we can change the data in the sheet and it automatically changes the form. That data can be easily downloaded and uploaded from Managebac or even manually added. We can also add homeroom teacher emails to the sheet (which is used later for the email notifications)
2. Adding Questions to the Google form
Then I added the rest of the questions we required to the google form, these can be anything at all. We chosen the following as we will want to track if certain areas, subjects or times cause more issues than others.
- Location / subject
- Date
- Time
- Behaviour type
- Action taken by the teacher
- Notes
3. Email notifications
Once a staff member submits a behaviour report we want emails to go to particular individuals. Specifically all emails go to head of student services and the Principal, homeroom teachers get an email if there is a report about a student in their class.
To achieve this we use a combination of a few functions, Import range, Vlookup formula and query and the google sheets add ons copy down and Form Mule
Step number one was to set up the form responses sheet to link students with their homeroom teachers.
In our original student list sheet we added homeroom teacher emails for each student, this wasn't as hard as it sounds, I filtered by class and then copied the teacher email address to each student. This can easily be done at the start of the school year for all students or when a new students arrives at school.
In our form responses sheet I then created a new tab "students" and used the import range function to import student names and each students teacher into this tab.
In a new column (column O) in the form responses sheet I then used the following Vlookup formula
This is a great way to search a list and add extra information to a google sheet that is linked to a form. The only issue is that when a new response is submitted Google Sheets inserts a new row, rather than pasting the data to a row. This means you can't copy and paste the formula all the way down the sheet. because of this I use the copy down add on, which copies the formula down to the next row each time a form is submitted.
Now that the data is ready I use form mule to automatically send an email when a new behaviour report (form) is submitted. Form mule can be set up to send emails to multiple addresses. We use a combination of the homeroom teacher email and the Principal / head of student services. This allows multiple people to receive the details of the behaviour incident. The email addresses can be put directly into the form mule template or they can be added to the sheet using the copy down formula and concatenate (to merge multiple cells together).
4. Access and searching behaviour reports
All of the behaviour reports are stored on a google sheet, this makes it easy to search for particular students, dates, locations etc. We can also generate reports and graphics about behaviour hot spots or times when we have more reports than others.
We also wanted this information to be easily available and searchable for admin members and teachers. It is also important to keep this data as private. For example grade 4 teachers should only see grade 4 students but Principals see all records. We also wanted this information to be easily on the eye.
To achieve this I set up a google site and then used Awesome Table to create cool embedded table into the site
I created a new tab on the form responses sheet and used a query that only selected certain rows to create the information that needed to be shared. You can also change the order when using a query.
In my example site the sheet is available for anyone on the internet to view, which we don't want when it comes to sensitive student data. We need a way to make the data private.
Because Awesome table uses a Google sheet all we have to do is change the permissions of the sheet so only certain people can view the sheet, that way if someone who we don't want to view the data tries, they will get an error message or the site wont display anything.
To set up grade level teachers with individual permissions I used the import range function to import all the data into a new sheet, then used a query (or even a filter) so that the only data on the sheet is limited to a certain grade, i.e. only grade four students are listed on the sheet. We then set up permissions so that only the grade four teachers can view that sheet, created a new Awesome table on a new page on the site. That way homeroom teachers can search for students in their homeroom but can not see any behaviour notes of other students.
5. Adding the ability to record the admin response.
This system also allows a response from the head of student services to be recorded. It could be recorded directly on the sheet but this is a bit messy and could be confusing for someone who is not tech savvy. I wanted to avoid data being changed or deleted. When creating these types of systems I think it as always best to limit the number of people who have access to the data.
To add their response it is much neater and easier for admin members to return to the original form and add their response there.
Whenever a google form is submitted, a url is generated that allows the user to go back and modify their response. You have probably seen the edit your response link / email you can get from a google form.
To allow admin members to get back to the form and add their response I included a link to the edit URL in the Form Mule email. All the admin member has to do is find the original email and click on the link to go back to the form and easily add their response
That is about it, everything is available via add ons, or simple formulas and hopefully this documentation should be clear enough that anyone else could duplicate it.
We will be testing the system next year and will be closely monitoring how it works, how easily it is maintained and is it is used. There are also plans to tweek it so it can be used in secondary. I will probably add grade level co-ordinators to the emails of students in their grade by adding them to teh student data sheet.
This is quite a long post but I wanted to try and make the entire system replicable (is that even a word) feel free to drop me an email or tweet if you have any questions or queries.
