Email All Teachers and reducing teacher spam

As a teacher I get a lot of emails which are for “The teachers of….” but most of the time I don’t actually teach this student. The person sending the email is sending to the entire staff email list to make sure they get their message to a selected group of teachers.

For common groups (such as Form Teachers, Departments) the easiest way to avoid this is to create Google Groups – but for teachers of an individual student, in my schools that would require over 1,500 groups (one for each student). So I decided to come up with a system that could pick out the teacher and send them an email, in this case using Awesome Table.

What does this do?

This uses data from a Google Sheet, displayed via Awesome Table to create a searchable database of students and their teachers email addresses. From this you can select to email all the teachers of a particular child.

An example of that is here:

How to create this for use in your school..

1. You can download the template spreadsheet here.
2. You need to get the teacher and student data in the correct format to match the above from your school MIS / SIS:

  • If your system exports teachers names rather than their email – you can either use Find and Replace or vlookup in Google Sheets to add the email addresses. I prefer vlookup as that makes it easy to change email addresses.
  • If your system puts each teacher on a new line in your sheet, you can merge the data using Power Tools and the merge rows tool (which is really useful for teachers who have to move data about a lot)

3. Once you get your data in the correct format – you can drop it into the sheet, which gives you the data in the correct format for Awesome Table to use.

4. Now to set up the Awesome Table – visit Awesome Table and log in with your Google Account.

5. Go to “Create a New Blank View” and then select the spreadsheet you created with the teacher email data in.

6. In the settings area, you need to add a value for the template – add Template!A1:B2 in this field.

7. Update the view and you should see your table ready to use or embed in a site (using the share button).