How Teachers can Email Parents of Students from Google Forms

A school provides email accounts for students that are enrolled in high school. The school has published a Google Form and any student can put their name in the form to request access to an email address.

Parent’s consent is required though. When a child submits the request, an email confirmation is sent to the parent of the child for them to provide consent before the student’s email address can be created.

Lookup Parent’s Data in Google Sheets

The key here is that an email should be sent to the parent of the student. This data itself is not available in the Google Form but the school maintains a Google Sheet with the parent’s data and we’ll do a lookup to fetch the parent’s name and email address based on the student’s name.

When the Google Form is submitted, a new row is added to the Google Sheet with the form response.

We’ll add two new columns to the form response sheet that will lookup up the parent’s email address and name from the parent records sheet. It uses ArrayFormula to instantly fetch the parent’s data in the form sheet after a new form response is submitted.

=ArrayFormula(
    IF(ROW(B:B) = 1, "Parent's Email",
    IF(NOT(ISBLANK(B:B)),VLOOKUP(B:B, Parents!$A$2:$C$100,3, FALSE),))
)

If the Row Number of the current row is 1, we set the column title which in this case is Parent's Email. If the row is not 1 and there’s form data in column B, we use VLOOKUP to get the matching data from the parent records sheet into the form response sheet.

Send Email to Student’s Parent

The first step is to install Document Studio from the Google marketplace. Next, switch to the Google Sheet storing the Google Form responses and go to Extensions > Document Studio > Open to launch the add-on.

Create a new workflow and give it a descriptive name like Send Consent Letters to Parents. On the task screen, create a new Email task and choose the Parent’s Email field from the “Send Email to” dropdown.

Here’s how the final email template will look like:

You can include placeholders, enclosed in double curly braces, to include form answers and values from Google Sheets formulas.

Switch to the Save screen of your workflow and turn on the option that says Run on Form Submit. This will internally enable the form submit trigger and your workflow will run whenever a new form is submitted.

Go to your Google Form, submit a sample response and you should see a copy of the consent email that was sent to the student’s parent.

You may also add a Create File task in the same workflow to generate a consent document in Microsoft Word or PDF. This can be automatically attached to the email message and the parent can email the signed copy.

For assistance, see this tutorial - Generate Agreements from Google Form Responses

source:https://ift.tt/Cyoi7GE