Sunday, 1 June 2014

Setting Up A Form To Email Parents About Missing Homework

1 comment
I've recently been helping out Kimberly Pollishuke with a great idea to send parents emails if a student is missing a piece of homework. She wanted to do it using Google Forms and use the autocrat script to send an email out to parents immediately.

Obviously, she didn't want to inputting a parent email on every form submission, so we've used VLOOKUP and ARRAYFORMULA to let Google Sheets do all the hard work for us!

Here is a video tutorial on how to set this up if you are using the new version of Google Sheets. (I believe that Kim is setting up a screencast for the old version shortly)

The two formulae that you need can be found below.

In cell A1 of 'Array Values' tab: =ARRAYFORMULA('Form Responses 1'!A:E)

In cell F2 of 'Array Values' tab: =if(isblank(C2),"", VLOOKUP(C2,'Parent Emails'!$A$1:$B$55,2,FALSE))
Don't forget to drag this one down!

The new version of autocrat doesn't have an automatic trigger on form submit yet, so you will have to run the merge manually (although I imagine that this feature will soon be available!)

You can have a look at the spreadsheet I used in the video here.

You can find the Merge Template Doc here.

I designed a similar script quickly that will do a similar job, which you can then trigger the onformsubmit option. To do this, follow the instructions as before, but do not install autocrat.

Instead go to Tools > Script Editor and paste the code below:

function myFunction() {
var check = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mail Merge');
for (var i=2; check.getRange(i, 1).getValue() != "";)
if (check.getRange(i, 7).getValue() == "Sent")
var teacher = check.getRange(i, 2).getValue();
var student = check.getRange(i, 3).getValue();
var subject = check.getRange(i, 4).getValue();
var reason = check.getRange(i, 5).getValue();
var parentemail = check.getRange(i, 6).getValue();
var blob = Utilities.newBlob('Dear Parent/Carer,<br><br>I am writing to let you know that '+student+' failed to hand in '
+subject+' homework. The reason given was: '+reason+'<br><br>Kind regards,<br><br>'+teacher , 'text/html', 'Missing_Homework.html');
MailApp.sendEmail(parentemail, student+" Missing Homework", 'Please see the attached missing homework document.',
{attachments: [blob.getAs(MimeType.PDF)]});
check.getRange(i, 7).setValue("Sent");
You can then click Resources > Current project's triggers. You can then set up the function to run whenever you submit a form, so no need to manually run the merge. You need to select the following:

Run 'myFunction' 'From spreadsheet' 'On form submit'

That's it! Test it out. If you feel up to it you could play around with the wording in the code to personalise the email to your needs.

Back to all posts

1 comment :

  1. Ooooh... interesting! That's something I'd like to look into when I have some time. I know that you can call Google Translate from within a script. I've never done it, but I'm sure it could be done!