Mail Merge coding in Excel or Google Sheets

General information for the business: To send emails depending on attendance
Description of requirements/functionality: We use Google Forms to capture attendance and feedback from weekly teaching sessions. For attendees that do not attend, we want a system by which their supervisor automatically receives an email, with no more than a couple of clicks needed!!

We have an initial 'master spreadsheet', which has all the expected attendees in Column A, and the email address of their supervisor in Column B.

We want some form of script, to look at the list of attendees in the weekly results; where their name appears with 'attended' in the next column, there should be no action; but if besides there name there is another term (e.g. sick, holiday, etc etc), OR, they did not complete the Google Form and so their name isn't listed, then their supervisor should receive a standard email, saying that [insert name] did not attend this week.

Ideally, this would be coded directly in Google Forms (in Google Drive) with the mail merges in Gmail, but I would also accept that the weekly results are exported as a CSV and this is done in Excel / Outlook mail merge
Extra notes: