Monday, November 24, 2014

Google Apps Script - Part 2 - Send email result to selected address after submitting google form (advanced, email with template)

This blog post is an advanced topic of the previous article about sending email result to the selected address after submitting a google form. The advanced features are:
  • The email address will be get from a separate sheet instead of choosing from the form.
  • The email content will be get from a template which is also from a sheet instead of hard-coding into the script.

1. In the same spreadsheet of the responses sheet, create a new sheet name "emailaddresses". Fill in the name of a person in one column (A) and email address of that person in another column (B):


Note: The names in column A need to be exactly the same with the names lists in the form. Check out the getEmailByName function in the script below for more detail.

2. In the same spreadsheet of the responses sheet, create a new sheet name "emailtemplate":

* Create your email template in html form with placeholder where you will fill in the submitting result, for example:


* Minify the template by going to http://www.willpeavy.com/minifier/

* Copy the minified html content of the email template back to the emailtemplate sheet at cell A1

3. In the google script write those functions:
  • Get email from emailaddresses sheet
  • Get the template from emailtemplate sheet,
  • Fill in the data based on placeholders
  • And send email to the selected email address



Notes:

1. You may need to change the fillEmailTemplate function with the correct field names.

2. For Radio questions with sub-questions, please take a look at these functions: setChecked, getSubQuestionAnswer, getSubQuestion, getDomainQuestion and the placeholders for those questions (e.g: <input class="{{ 4f2checked }}" disabled type="radio" name="entry.617854372" value="2" id="group_617854372_2" >)

3. Remember to set the trigger for the EmailFormConfirmation