The goal of this post is to make an easy-to-edit auto-response for google apps web form. This post is the translation in English of my recent post written in Japanese 論理と表現を分離して Googleフォームの自動返信を少しだけ使いやすくする
Background
Google Docs (or Drive) offers a very handy web form and you can make and share it in seconds. If you are wondering how to make a registration form for your low budget meetings or short-run projects, Google Form would be a perfect choice.
However, when used with the default setting, the Google form works not that perfect: You may have wondered how to force it send an auto-response message for the submitter. I suppose this is intentional not to make it a playground for spammers. But don’t they allow us to have such a functionality even for temporary websites, or in limited-access areas?
The answer is YES. You can make it work with Google Apps Script. See, for example, Tutorial: Automating a Help Desk Workflow. The idea is simple: Watch the submission event and let it trigger an email sending script.
Why I didn’t fully appreciate it
In the above mentioned tutorial site, you saw a very small piece of script that works perfectly. But I was not content with that solution. In common situations, I suppose, you, programmer, set up a web form and your non-programmer colleague actually uses it. So, I wondered: How do they edit the template for the auto-response message? Should you explain that \n means a new line, put a semicolon at the end of the sentence, don’t use a quotation mark between quotation marks and stuff like that? Who becomes happy in such a situation?
What I did
Here I show one way to make the script read a template that is easily editable for non-programmers. What I wanted is something like the relationship between HTML and CSS:
CSS is designed primarily to enable the separation of document content (written in HTML or a similar markup language) from document presentation, including elements such as the layout, colors, and fonts. (Quoted from Wikipedia)
Separation is always good. So, let’s do it with Google form too.
Step 1. Create a form. Because this is a demonstration only, I put only two text boxes: Name and E-mail. …Sorry for the Japanese in the pic 
Step 2. Create a document like THIS (This doc is publicly visible). Step 2 and 3 are interchangeable. I present this way to specify the requirement of the script. After creating the template, look at the URI and find the document ID (in my case, it’s 14O7df3d_kNiKB6tDhgzrLz2vhaDKCc4fbiS3O_KmC88).
Template is split into three parts by ===’s (more than 3).
- In the 1st part, the header information is set. You could dynamically change the subject too, but here we just don’t do that.
- The body of the message goes in the 2nd part. When sending the message, the submission contents for Name and E-mail, will replace the doubly-bracketed {{Name}} and {{E-mail}}, respectively. You can also use the default field Timestamp.
- 3rd part is ignored.
Your non-programmer colleague will be happy with this template? If you think not enough, please make the template neater and the script better.
Step 3. Here is what I did. (I’m not a pro-programmer, so please teach me if you know a better solution. I really appreciate it.)
Scripting
Let’s specify the Doc ID and the name of the column where recipients’ email addresses are stored.
loadTemplate() function (ll. 42-) reads the template file and returns the header and body while cutting off the 3rd (memo) part.
The core is at ll. 22-23. The doubly-bracketed keywords are replaced with the corresponding form entries.
// Doc ID of the template file
var id = "koko_ni_id_wo_iretekudasai";
var mailCol = "E-mail";
function onFormSubmit(e){
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = ss.getLastRow();
var c = ss.getLastColumn();
var rg = ss.getDataRange();
// Processing the body part
var t = loadTemplate(id);
var body = t.body;
var v = {};
for ( var j = 1; j <= c; j++ ){
// Replacing the template keyword into the input value
var cname = rg.getCell(1, j).getValue();
var entry = rg.getCell(r, j).getValue();
v[cname] = entry;
var re = new RegExp("{{"+cname+"}}", "g");
body = body.replace(re, entry);
}
// Processing the header part
var mail = v[mailCol];
var subject = t.head.subject;
var opt = { cc: t.head.cc,
bcc: t.head.bcc,
replyTo: t.head.replyTo,
name: t.head.name };
// Sending the message
MailApp.sendEmail( mail, subject, body, opt );
} catch (e) {
// When exception raised
MailApp.sendEmail(Session.getActiveUser().getEmail(), "Error report", e.message);
}
}
function loadTemplate(docId){
// Load your template doc file
// docId = Google Docs Document ID
// Find it from doc's URI
var t = DocumentApp.openById(docId).getText();
// Comment out strings after double slashes
t = t.replace(new RegExp("//.*\n", 'g'), "");
// Body part is placed between a pair of lines with more than 3 consecutive ='s
var tsplit = t.split(/===+.*\W+/);
// Retrieving the header part
// that should be placed before the first ===
var head = {};
var lines = tsplit[0].split(/\n+/);
for ( var i in lines ){
var line = lines[i];
if ( null != line.match(/(\w+)\s*=\s*(.+)$/)) {
head[RegExp.$1] = (RegExp.$2).trim();
}
}
// Retrieving the body part
// that should be place between the firs and last ==='s
var body = tsplit[1];
return { head: head, body: body};
}
Result
The script works like the following pic. So far so good.
