Newbie here Google form submit to send email with QR code

[Romesh Patel]

I have a google form that collects a reference number.

The google sheet that it is linked to the google form  uses an array formula

=ArrayFormula( IF(ROW(F:F)=1,"Image Tag", IF(ISBLANK(F:F),"", SUBSTITUTE("<img src='URL' />","URL",(CONCAT("https://chart.googleapis.com/chart?chs=200x200&cht=qr&chl=",F:F) ) ) )))

Which basically makes the QR code URL in HTML so that when it is mailmerged the QR code shows up.

The service is only available for those aged 5 to 9 years of age, so I need to add a helper cell to my sheet that adds NOT ELIGIBLE to  the  email sent column so that when the mailmerge scrip is run ( I have to give credit to this guy, it is his script, https://developers.google.com/apps-script/samples/automations/mail-merge Martin Hawksy) it doesn't send an email.

The age is worked out from the date of birth, but how do I output the result to a different column in google sheets?   I have this formula which gives the completed years of the child so nearly 5 or just 10 still still show up as 4yo and 10yo.

=ArrayFormula( IF(ROW($E:$E)=1,"Age of child", IF(ISBLANK($E:$E),"", DATEDIF(E:E,today(),"y"))))

I then want the mail merge script to run every time there is a new entry (so when someone submits a response on the google form) They automatically get a confirmation email.

The next step is that in the same workbook, I add a new sheet called NOT ELIGIBLE, and use the filter function on Google sheets to filter the email sent and look for the words NOT ELIGIBLE.

These people get send a different email explaining why they are not eligible, and perhaps they entered a date of birth wrong, and they may want to correct the record.

Again I want this to run every time there is a new entry from the filter function.

Really sorry, but I am not an IT guy, but in healthcare, and I was forced into this a Microsoft don't support mailmerge QR or even HTML from Word to Outlook.

Really appreciate your help.

Many thanks.