Student Feedback system - send emails

The following Google Apps Script code is designed to get student feedback from the spreadsheet so it can be emailed to each student in-turn. It then updates the master Google sheet with an acknowledgement that emails for a Group have been sent.

Example of Email Sent? column with timestamp

To launch the function a menu item called Feedback > Send Student Email is used to call the sendEmailsConfirm function. I created this as a check for the user to ensure they intended to send emails rather than accidentally clicking it (as you will see in a later post there is more than one item in this menu in the final product).

'getUi' allows us to access the spreadsheet's user-interface so that we can then create the 'alert' popup which displays the customised Yes/No buttons to run the sendEmails function or do nothing. The sendEmails function itself gets all of the sheet data and sets a variable to '0'. This variable is used as a flag to inform the script of any emails that have been sent, to then update the master spreadsheet, otherwise the master spreadsheet would be updated every time the sendEmails function was run (even if no new ones had been sent).

The script then creates a loop to go through each student and checks there is an email address present and an empty Email Sent? column (the latter meaning the function can be ran more than once without repeatedly sending emails to every student). The relevant pieces of data are assigned to variables, after which the construction of the email begins.

When it comes to sending the email I use a try/catch to prevent a script error from an invalid email address. This means the log will catch the error in addition to allowing me to set a variable which decides if a timestamp should be written into the Email Sent? column for that student. The timestamp is formatted via 'formatDate' and informs the administrator when the email was sent for a particular Group, as well as setting the flag variable to '1' to inform new emails have been sent.

With the flag as '1' we 'split' the filename to get the Group ID which can be subsequently searched for in the master spreadsheet and 'setValue' alongside the relevant Group with the same timestamp as above. Remember that there are 36+ Groups in the project I worked on so this master spreadsheet provided a single place for administrators to oversee them all (including checking that emails have been sent for that Group).