Virtual Parents Evenings Wheatley Park (Secondary School) model

Rationale

In the past for ‘in person’ parents evenings, Wheatley Park have used a popular secondary school model, where parents would book 5min slots with all the teachers they wanted to talk to at the parents evening. The Teachers would then be located in the school hall and await the parents visits. Inevitably, appointments didn’t always keep to time, so parents would end up having to wait and have late appointments which sometimes meant other appointments were missed and parents would try and squeeze appointments in at non-appointed times! Although this model basically worked, it led to a somewhat chaotic evening and some frustrated parents.

To replicate this in the closest way virtually using Google Meet would have involved having ‘break out’ waiting rooms which requires a Google Enterprise licence for each teacher. Due to the extra expense this would incur, and the feeling that how it was done in person wasn’t ideal anyway, a new approach was introduced.

For the smooth running of the evening, particularly as parents wouldn’t have the visual cues/explanation of why they were waiting, it was decided that keeping appointments to time was crucial. With that in mind it was thought best to allocate each parent one Meet link, which they would join using the student’s school gmail account (allowing them direct access to the Meet) ready for their first appointment. Meanwhile, the teachers would have all the Meet links for each parent they were scheduled to meet, and would join the meet at the appointed time.

The feedback from parents for this new virtual Parents evening model has been extremely positive and many parents and teachers have expressed a preference for this format. For teachers, although parent’s evenings continue to be an exhausting experience for most, with a bit of preparation beforehand they can be a positive experience and for perhaps the first time ever, end exactly when scheduled! We have also been pleased to be able to encourage teachers, where appropriate, to conduct their appointments from home, further shortening their day and supporting their wellbeing.

Setup

These are the steps involved in setting up and running this Parents Evening model.

1. Plan the evening

Decide on day(s) and time(s) and length of appointments.

2. Get parents to book appointments

Most schools have a system of how they’ve done this in the past. At Wheatley Park, ParentMail is used, but whatever method is used, the result should be a google sheet of appointments, each row including as a minimum: student name, teacher name, parent/carer name, date and time of appointment. In the case of ParentMail, a csv (comma separated text) file can be exported with the appointment details and then imported into a google sheet.

3. Link emails to appointments

The next step is to link emails to the appointments. [This step may not be necessary if you have a system for making appointments that includes emails but for ParentMail the export does not include emails.] You will need staff and student emails (as a minimum) for emailing the appointments and Meet Link details. Ideally you’ll also link in parent/carer emails so they can be sent the details by email too and not just rely on their student’s email. The usual way to do this is to export the matching name (e.g student, staff or parent) along with the associated email from your Student Information database (e.g. SIMS, Bromcom, Integris, etc) and import this into another sheet alongside your appointment sheets. Then use VLOOKUP to match names across the two sheets and so get the correct email in a column next to the name on your appointments spreadsheet. (VLOOKUP Video demo) [If you need to combine first name and last name to use VLOOKUP, see this Video demo]

4. Run script to create appointments and assign Meet links

For the next steps you will need some google script attached to the spreadsheet that holds the appointments. Before you add the script you will need to think about which google account should run the scripts. The script will create appointments (the length of the appointment isn't imporant, but in this script are set to 3hrs) in the accounts primary calendar and will also send the emails, so you may wish to use a dedicated account.

The Script

You add the script to the spreadsheet by going into the script editor (Menu item: Tools - Script Editor), the name of the script doesn't matter too much, you can name it the same as the spreadsheet, or whatever you like. The below code works for a sheet named 'ApptData' where the appointment data has the following column headers, or can be editted to work for different spreadsheet layouts. Example spreadsheet here. IMPORTANT: Read the comment at the top of the script about enabling the Calendar API.

[If using a ParentMail export of appointments, to get these columns, combine the first and last name in columns A & B to a new inserted column C (Student Name) using this formula pasted (without outside quotes) in C2 '=A2&" "&B2', and then copy the formula down the column. Copy and Paste (Values only) into Column A and delete columns B & C. Then add the three extra email columns (C, G & I above) and see step '3' above to add emails. Then add extra column L header, 'Meet Link', ready to receive the meet links ]

You can run the script by selecting the function 'addMeetLinks' and clicking the 'Run' button, or you can import the function to the Macros menu of the spreadsheet ('Tools' - 'Macros' - 'Import' and then select 'addMeetLinks', you will then be able to run 'addMeetLinks' from the 'Tools' - 'Macros' menu. The first time you try to run the script you will need to authorise it for the google account running it, once it is authorised you will need to select the macro to run again. When you've run this script all the appointments should be created in the account's primary Calendar and the associated Meet link will be entered into the 'Meet Link' column on the 'ApptData' sheet.

5. Run script to send Emails

You will want to send emails to the teachers with their appointment details (including Meet Links) and also to the parents and/or students. You can do this whenever you feel is appropriate before the date/time of the appointments. The following script (if added to the script via the script editor) has functions that can be run (similarly to 'addMeetLinks' above) to send the emails, and uses templates from a sheet of your spreadsheet, see example here:

Once you have run the scripts to send emails, you can check in the Sent Mail of the account running the script to see the emails that were sent, which is helpful if people lose the email and need it sending again (i.e. you can find the email in Sent Mail and forward it). You will also see in the Inbox any emails that bounce!

6. Using Google Meet for the parent's evening appointments

Instructions/guidance for using the Google Meets for the appointments are given in the example email template.

Extra Notes about the Google Meets

Just to reiterate, this system creates Google Meets linked to calendar events in the account that runs the script. As long as this account is in the same domain as the teacher and student accounts that will join the Meet, then they can join the meet without needing to wait to be allowed to join by the 'host'. If a private account (e.g. a parent) clicked on the Meet link they would need to wait to be allowed to join, and only the account that ran the script (and has the appointment event created in it's calendar) would be able to 'allow them to join'. As soon as the Google Meet links are created they can be used, they are not limited to the date/time of the appointment, so if teachers want to test the system they can share one of their appointment's meet link with a colleague and try joining it. The Meet link will continue to be valid/usable until 90 days after it has been inactive. This means that if you created the appointment Events (with Meet links) for one particular day/time and needed to reschedule the day/time, then (unless you want it to be correct on the Calendar) you can just change the date/time on the google sheet and resend the emails using the above emailer scripts. If you just want to resend some of the emails, you could duplicate the 'ApptData' sheet (to keep all the data in sheet 'Copy of ApptData') and then edit the 'ApptData' sheet to only contain the appointments for which you need to resend emails, then run the email scripts again.

If you do want to use the Calendar as way of viewing the appointments and the Meet Links you can. If you want to delete an appointment from the Calendar you can do this as normal from the Google Calendar. If a mistake has been made or a whole evening needs rescheduling you can delete all appointments for a certain date/time using the following script: