Conditional Content in Google Forms Emails

How to send email notifications to a different recepient based on existing questions in your Google Form

When a new Google form response is submitted, you can automatically generate an email. With the help of formulas in a Google Sheet response file you can vary the content of the email or the recepient. This feature is available from the Google Sheets version of Mail Merge add-on. This article gives an example on how to do both.

Step 1: Create a Google form with a list of questions

This Google Form has a special questions called How happy are you? If the value is below 5 we want to write a different content in the email body. We also want to send the email to a different person with in the organization so that he can take action on why the person is unhappy.

Step 2: Link Google Form to a Google Sheet

This will save responses from Google Form to a Google Sheet response tab

Step 3: Open the Google Sheets the conditional values

Open the Google Sheets holding your Survey responses and create a column called Email Address. This column will hold the recepient email when a survey is submited. However we want to send the email to Joe if the rating is less than 5 and send the email to Mary if the rating is greater or equal to 5. Hence we write the formula accordingly as shown below.

We also wantto create a column called Response which will hold a different response in the email based on the rating. So we create a formula similar to the above.

Step 4: Start Mail Merge add-on in Google Sheets

Start Mail Merge add-on in Google Sheets by clicking on Extensions > Mail Merge > Start to open the Mail Merge dialog box. A new column called Filter Rows to Merge is created automatically. This column would tell you when the email has been merged/sent.

Step 5: Setup mail merge

Click on the To Emails tab

Under Email To select Merge From and choose the Email Address column so that emails would go to the conditional email set. i.e. Either Joe or Mary.
By Body Template click New or Edit to write your email response body.

Below is the email body template with the conditional *|Response|* merge field. Click Save when ready from your email body. A different response will show based on the rating given.

For this to work, you need to choose Send email automatically under On a Google Form Submit and Save changes.

Step 6: Copy Down formulas from row 2

The last issue we need to solve is to automatically copy down the formulas we've written in row 2 into the new row inserted after a Google Form's reponse. Unfortunately, Google does not automatically copy down the formulas. To help us with this issue we need to install an other add-on called Copy Down. This will help you copy down the formulas under the Email Address and Response columns into new rows. For an example on how to do that please check this article.

Step 7: Test by Submitting your Google Form

Let's fill out our Google form and give a 9 star rating. Once the form is submitted a new row in your Google Sheet is created and the formulas will also be copied from row 2. An email will then be sent.

Step 8: Email Result

Once you click the Submit button an email is gerenated based on the template you configured earlier. Below is an example of the email. Since the rating was 9/10 the email was sent to Mary and the response highlighted in red is also based on the formula under the Resoponse column.

Report abuse