Send form by email and track responses in spreadsheet
The components involved in this workflow are:
- A script to generate and send an email with an HTML form.
- An HTML template for that email, which allows us to customize the email for each recipient.
- A
doPost()
function to handle responses. The script must be deployed as a Web App. - A spreadsheet to collect responses. The script will be contained in the spreadsheet, and extends the spreadsheet UI with a menu for sending a copy of the survey. (It could be adapted for standalone use, without the UI component.)
Here is an example of such a workflow, conducting a Commuting Survey. Recipients will receive a survey email like this:
Recipients fill out the form right in their email client, if it supports that capability. Responses will be collected in a spreadsheet, like this:
Create the spreadsheet headers yourself, before running the script.
The "Serial Number" column has been added to illustrate a way to correlate responses with particular respondents; note that some entries repeat. When a survey email is generated, it is given a unique serial number, which is then passed back as a hidden value with the responses. We could extend this system to recognize updates from respondents, for instance.
Now, the code. (Which is also available as a gist.)
emailTemplate.html
<div>
<form action="<?= scriptUrl ?>" method="Post">
<table>
<tr>
<td>
<label for="commute">Do you commute to work?</label>
</td>
<td>
<select name="commute">
<option>Yes</option>
<option>No</option>
</select>
</td>
</tr>
<tr>
<td>
<label for="vehicle">If "Yes", how do you get to work?</label>
</td>
<td>
<input type="checkbox" name="vehicle" value="Bike">I have a bike<br>
<input type="checkbox" name="vehicle" value="Car">I have a car
</td>
</tr>
<tr>
<td>
<!-- A Hidden field is a handy way to pass information to the
Server-side POST handler. For example, a serial number could
be used to collate responses from a particular recipient. -->
<input type="hidden" name="serial" value="<?= serialNumber ?>" />
</td>
<td>
<input type="submit" value="Submit" />
</td>
</tr>
</table>
</form>
</div>
Code.gs
// doPost needs the spreadsheet ID, it has no concept of "active spreadsheet".
var _spreadsheetId = '--- Spreadsheet ID ---';
// Add custom menu with option to send survey
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Send Survey",
functionName : "sendSurvey"
}];
sheet.addMenu("Custom Menu", entries);
};
/**
* Build & Send Survey, an HTML form in email.
*/
function sendSurvey() {
var recipient = Browser.inputBox("Send Survey", "Enter Recipient Email", Browser.Buttons.OK_CANCEL);
if (recipient === 'cancel') return;
var subject = 'Commuting Survey';
// Get the URL of the published Web App, to include in email for POST of response
var scriptUrl = ScriptApp.getService().getUrl();
if (!scriptUrl) throw new Error( 'You must Deploy as Web App first.' );
// Build email body
var template = HtmlService.createTemplateFromFile('emailTemplate');
template.scriptUrl = scriptUrl;
template.serialNumber = getGUID(); // Generate serial number for this response
var html = template.evaluate().getContent();
// During debugging, send emails to self. Remove this line for real operation.
recipient = Session.getActiveUser().getEmail();
// Send email form
GmailApp.sendEmail(recipient, subject, 'Requires HTML', {htmlBody:html} );
Browser.msgBox("Survey Sent");
}
/**
* POST handler for responses;
*/
function doPost(e) {
Logger.log(e);
var ss = SpreadsheetApp.openById(_spreadsheetId);
var sheet = ss.getSheets()[0]; // Assume first sheet collects responses
// Build a row of data with timestamp + posted response
var row = [
new Date(), // Timestamp
e.parameters.serial[0], // Serial Number
e.parameters.commute[0], // Commuter? Yes / No
e.parameters.vehicle.join(',') // Vehicle
];
// Make sure we are the only people adding rows to the spreadsheet
var lock = LockService.getPublicLock();
// Wait for up to 30 seconds for other processes to finish.
var locked = lock.tryLock(30000);
if (locked) {
// Save response to spreadsheet
var rowNum = sheet.getLastRow()+1;
sheet.getRange(rowNum, 1, 1, row.length).setValues([row]);
// Release the lock so that other processes can continue.
lock.releaseLock();
var result = "Response Recorded: \n "+row.join('\n ');
}
else {
// Failed to get lock
result = "System busy, please try again.";
}
// Report result of POST, in plain text
return ContentService.createTextOutput(result)
.setMimeType(ContentService.MimeType.TEXT);
}
/**
* Returns an rfc4122 version 4 compliant GUID / UUID string
* Thanks to @broofa!
* http://stackoverflow.com/a/2117523/1677912
*/
function getGUID() {
return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
return v.toString(16);
});
}
Deployment
To use this survey system as-is:
- Create a new spreadsheet in your Drive account. Add headers for "Timestamp", "Serial Number", "Commuter?", and "Vehicle" in row 1.
- Tools - Script Editor. Copy the
Code.gs
content. Copy the ID of your spreadsheet, and update the_spreadsheetId
variable at the top of the file. Save. - File - New HTML file, name the file emailTemplate. Copy the
emailTemplate.html
content. Save. - Publish - Deploy as Web app... Make it accessible by anyone, including anonymous. (In a Google Apps domain, you can restrict it to users in the domain.)
- Authorize the script, by reloading your spreadsheet or running the
onOpen
function in the editor.
Ready to go! You'll find a "Custom Menu" in your spreadsheet, with a "Send Survey" command.
Send an email on behalf a form submitter
I think there is a typo in the code.
You should replace:
const options = {
htmlBody: message,
replyTo: data.email,
cc: emailCc,
'from': emailFrom
};
to:
const options = {
htmlBody: message,
replyTo: data.email,
cc: emailCc,
from: emailFrom
};
Related Topics
Sticky Header and Footer Scrollable Content
Flexbox: Align Between Bottom and Center
Angular2 Without Hash in The Url
How to Get a Flexbox to Have a Center Fixed and a Bottom Fixed Children Together
How to Resolve 500 Internal Server Error
Is It Correct to Use Div Inside Form
In The Dom Are Node Ids Case Sensititve
Unordered List (<Ul>) Default Indent
Is 'Disabled' a Valid Attribute for an Anchor Tag
Hide Element, But Show CSS Generated Content
Force Ie9 to Emulate Ie8. Possible
How Position Absolute and Fixed Block Elements Get Their Dimension
Photoshop Mock Up Font Isn't Same as in HTML
CSS - Hover Passes Through Elements to Activate Hover on Covered Element