In a teacher meeting one day, we were discussing the ever-increasing difficulty of tracking discipline issues, especially the minor ones – uniform violations, tardies, using headphones in between classes. These are the small actions that teachers most loathe dealing with – who wants to be looking at students’ socks when class starts? Who can remember to ask a student for a signed note home sent because a student was late to class? Who wants to look up how many tardies a student has (to give them the proper verbal warning) when they come sauntering in 10 minutes after class has started?
Definitely not me.
My school practices positive discipline – a wonderful idea where, with several notable exceptions, most disciplinary faults are first verbally addressed, then noted to a parent, and then finally addressed with a detention after the third strike. It helps the parents know when their students are at risk of a detention, and it gives the students a chance to get their acts together.
In practice, however, I rarely gave out detentions – I could not follow through with the positive discipline method. It wasn’t the actions themselves: I could give a verbal warning and make note of that, and I could send a note home. It was keeping track of all those minor faults that was impossible. It is hard to be actively engaged in a class’ learning and also remember what step of the process I am on for each student, even with a notebook to record everything.
I found a wonderful resource on this (click here) page about a tracking system that one teacher uses to help his English students improve in their writing skills. It tracks their progress over time, and the students themselves have access to their individual tracking sheet.
He offers the examples for free and talks about how to format them for your own needs. As I watched the video, it occurred to me that I could use this same process for tracking discipline. I tried it out. And it has been amazing.
The students now know when I have not received a homework assignment from them for whatever reason. They can keep track of how many tardies they have in my class. They are notified immediately when I have updated their tracker. I don’t have to remember to ask for a parent note home, because I just send an email.
The system is not perfect (I would love to know how to automatically pull up a student’s email address when I type in their name on the Google Form, for example), but it works wonders. Students are avoiding the little misbehavior faults because they know I am on the ball. Classroom management has never been easier.
Just a note: this should not be used to replace verbal communication, but to back it up in writing. When I sent the email to the students, I word it as a reminder about the conversation we had in class. It’s always important to talk to the student first, especially if it’s the second occurrence – the student deserves the chance to know/be heard before parents are emailed. I also copy my supervisor on parent emails so that everyone is in the know.
Recipe for a Student Discipline Tracker
Prep time: Approximately two – four hours
-Firefox browser (Chrome does not work for Part C)
-Gmail account (not a personal account – I created a new work-only account for this activity)
-Outlook account – again, with a work-only address (unless you know how to Mail Merge with Gmail)
-List of student names, student email addresses, and parent email addresses in an Excel spreadsheet, organized by grade and section
-List of parent email addresses
-A computer with a comfortable keyboard (I prefer a desktop for this – it’s faster for quickly copying and pasting info)
Part A: Making the Form
- Create a tracker work-only Gmail account. I created one that has Ms.-Name-Do-Not-Reply so that they are not tempted to send homework to that email address later.
- Logged in to this account, go to the Drive: http://drive.google.com
- Upload or create your reference list of student names, email addresses, and parent email addresses in the Drive. Title this “Student Reference Sheet.” Add a column titled “Tracker URL.” Here is a sample (make a copy to create your own): https://docs.google.com/spreadsheets/d/1kfcs2hUuJ7FINu9YbOj41SCrDPrFyMct_oZmhLSiq7s/edit?usp=sharing
- Create a Google Form. See my example here: https://docs.google.com/forms/d/e/1FAIpQLSdUFjqiytKYpk93IzyajGOTf3cn3PUy1kRUQG3dIcUDa5mniA/viewform
- Title it: Student Discipline Form
- Add a question titled “Student name.” Make the answer a dropdown list.
- Copy the entire column of student names from your Google Sheet, and paste into the option 1 area. The names should appear as different options. Make this question required.
- Add a question titled “Email address.” Make the answer a dropdown list and paste the student email column into the options. (I haven’t figured out a way to make the email addresses automatically appear based on the student selected – if you have ideas, please let me know!) It is important that the email addresses are listed in an order you can remember because you will have to be able to identify them off the top of your head. I recommend having them in the same order as the Student Reference Google Sheet. Make this question required.
- Add a question with a multiple choice answer. Write in the small-item discipline infractions according to your school. Mine include “incorrect uniform,” “book not covered,” “gum,” “no homework,” and “late to class.” Make this question required.
- Add a question with a short answer option called Comments.
- If you ever make a mistake and need to erase an entry, go to “Edit this form,” “Responses” and click on “Individual.” (It will not un-send the automatic email so you might want to write the student another entry with a comment saying that you made a mistake and will remove this from their tracker.)
- To see the ready-to-use form, click the eye in the upper right-hand corner. I saved this finished form on my bookmarks bar for quick access.
- Open the form edit option. Click on “Responses.” Then click on a little green box with a white T inside that says “Create Spreadsheet.” It will create a Google Sheet with the responses organized into columns (again, I saved this link as a bookmark for easy reference): https://docs.google.com/spreadsheets/d/1b4jjXkxPGn5p-jeRfnFBRGJC7ldeUIJZJaIGeiUzA5c/edit?usp=sharing
This is useful enough that if you wanted to stop here, you could. But in order to make individual student records, continue to Part B.
Part B: Making individual student trackers.
- Create a blank Google Sheet in the drive. Copy and page your first student’s name into the title bar. (Copy directly from the Student Reference Sheet so that the name remains the same across all of the different sheets.)
- Place this code into cell A1:=query(IMPORTRANGE(“fill_in_sheet_URL_here”, “Form Responses 1!$A:$E”),“Select * where Col2=’Boo Radley'”)
- Erase Boo Radley without erasing the quotation marks around it and paste the student name into this space, exactly as it is (do not include any underscore marks and remove any extra spaces after the first quotation mark and before the last)
- Go to the Student Discipline Form (Responses). Copy the long string of numbers and letters that appears in the URL address. For example, my response sheet’s URL is https://docs.google.com/spreadsheets/d/1b4jjXkxPGn5p-jeRfnFBRGJC7ldeUIJZJaIGeiUzA5c/edit?usp=sharing, so I will only copy this part: 1b4jjXkxPGn5p-jeRfnFBRGJC7ldeUIJZJaIGeiUzA5c
- Paste this into the part of the code that says fill_in_sheet_URL_here without erasing the quotation marks.
- Hit enter on your keyboard. If a box appears that says “Allow Access” to connect the sheets, click it.
- Click on the “Share” button at the top right of this student’s page. Click on “Get shareable link.” It will automatically copy the link. Make sure it is on the “Can view” option.
- Go back to your Student Reference Sheet. Copy the link into the student’s row under the column “Tracker URL”. As you can see, I have filled in the URL address for Boo Radley: https://docs.google.com/spreadsheets/d/1kfcs2hUuJ7FINu9YbOj41SCrDPrFyMct_oZmhLSiq7s/edit?usp=sharing
Part C: Automatic Emails (optional, but extremely useful)
This option allows you to automatically send the student an email every time they are included on the form.
- Go to the Student Discipline Form: Responses sheet. Click on “Add-ons” in the menu at the top. Click Get Add-ons.
- Search for FormMule and add it.
- After it is added, click Add-ons in the menu again, and click Launch.
- Sign in to your Gmail work account if prompted, and click Allow.
- If the Add-on spins and spins, try Firefox or Internet Explorer. FormMulw seems to have problems running on Chrome.
- Select sheet “Form Responses 1” and click “Send on Form Submit” under Form Trigger. This will set up an email to be sent to the student the moment that you fill out the form. If you prefer to have the forms sent at a certain time, then use the other option.
- In the next screen, leave everything as is and click “Save template settings.” Then click Next: Edit templates.
- This is the where you craft the template for the email address to your student.
- In the To: bar, click “Student email” in the Add Merge Tag bar at the right-hand side of the page.
- CC: I like to CC my actual work email, the one I expect student to write to.
- Reply to: Again, I put my actual work email that the students already know.
- Subject: Discipline Update from Ms. ______ (or whatever generic subject you want)
- In the Body:
Click on the merge tags at the right of the page when you want FormMule to automatically fill in the information. You might have to erase the extra text outside of the <<>> in order for it to make sense.Dear <<Student name>>,This is a discipline reminder for what we discussed today: <<Discipline item>><Comments:>>
Please let me know if you would like to talk about this. Remember that the second time this happens, your parents will receive an email, and the third time will be a detention.
- Click on Preview and Send All to see what the emails will look like. Edit the template if something doesn’t look right. If all is well, click Send Now. (Note: you have a daily quota of 100).
- Every time you fill out the original Google Form, the student will now receive an email.
Part D: Making and sending a bazillion individual tracker sheets with relative ease
This is where having a desktop computer makes a big difference. Open up these two pages on different tabs of the same window:
- Open your Student Reference Sheet.
- If you followed the instructions for part B, you should already have the first student’s individual file made.
- Open your first student’s individual tracker sheet (see Part B for instructions on how to do this).
How to make an individual sheet for every student:
- Go to the Student Reference Sheet. Click on the cell with the second student’s name. Hold down Ctrl then type the letter c to copy the text from this cell.
- In my case, the second student’s name is Scout Finch.
- Go to the first student’s tracker. Under file, click “Make a Copy.”
- The title of the document is automatically written as “Copy of Boo Radley” – it is also automatically highlighted. Hold down Ctrl and type the letter v to paste Scout Finch into the document name.
- Click on cell A1 in this new document. In the formula bar, highlight the name Boo Radley (without highlighting any quotation marks) and again type Ctrl and v to paste the name Scout Finch into the formula.
- If it asks you to “Allow access,” make sure to click this.
- Go to the blue Share button in the upper right-hand corner. Click on “Get shareable link” and click on the “Copy Link” button (make sure it is on the Anyone with the link can view option).
- Go back to the Student Reference Sheet. In Scout Finch’s row, go to the cell underneath the Tracker URL column and hold down Ctrl while pressing v (copy the link into the cell).
- Exit Scout Finch’s tab. It will save automatically and keep your browser window clean.
- Repeat all steps above until you have made an individual tracker for every student. Once you get into the pattern of it, you will see that it goes pretty quickly.
How to send each student their own tracker URL through a mail merge:
- After the entire column of Tracker URL is full in the Student Reference Sheet, go to File – Download as – Microsoft Excel.
- Save into an easily accessible folder on your computer.
- Open a blank Microsoft Word Document.
- Go to the “Mailings” tab. Click on “Step-by-Step Mail Merge Wizard” and click “Email message.” Click Next.
- Click “Use the Current Document.” Click Next: Select recipients. Under “Use an existing list,” click on Browse. Find the excel sheet you downloaded in step 1.
- Click on Sheet 1 and Click OK. It will show you a list of all of the students. Click OK again.
- On the Mail Merge Wizard on the right-hand side of the page, click Next: Write your email message.
- Write your email message. Where you want to insert a field from the Excel document, use the Insert Merge Field at the top of the document. Here is what my email looked like:To «Student_Name», Here is your personal Student Discipline Tracker sheet: «Tracker_URL»
Use this to keep track of your discipline record in my class.
Please let me know if you have any questions.
- Click on “Preview Results” to see a finished example of your email. Then, click Finish and Merge – Send email messages.
- In the “To” line, select “Student_Email.”
- In the Subject line, write the subject of your email. Mine is “Personal Discipline Tracker – English class”
- Click OK. (You may have to log in to outlook on the desktop in order for the emails to send.)