Skip to content

Instantly share code, notes, and snippets.

@mogsdad
Last active August 1, 2022 07:48
Show Gist options
  • Save mogsdad/6472790 to your computer and use it in GitHub Desktop.
Save mogsdad/6472790 to your computer and use it in GitHub Desktop.
Google Apps Script workflow for an email survey. Written in response to StackOverflow question 18668828. http://stackoverflow.com/a/18669532/1677912
// doPost needs the spreadsheet ID, it has no concept of "active spreadsheet".
var _spreadsheetId = '0AmkSPNhhUowadExOUklueG5KMWdMQU5UdkJaM2FCc2c';
// 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();
var plainText = 'Please complete this survey online at: ' + scriptUrl;
html += '<p>Alternatively, you may <A href="' + scriptUrl + '"> complete this survey online.</A>';
// Send email form
GmailApp.sendEmail(recipient, subject, plainText, {htmlBody:html} );
Browser.msgBox("Survey Sent");
}
/**
* GET handler to provide alternate online form.
*/
function doGet() {
// Build survey body
var template = HtmlService.createTemplateFromFile('emailTemplate');
template.scriptUrl = ScriptApp.getService().getUrl();
template.serialNumber = getGUID(); // Generate serial number for this response
var app = template.evaluate();
return app;
}
/**
* 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);
});
}
<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>

Google Apps Script Survey Workflow

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:

Email

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.

Spreadsheet

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.

For users without appropriate HTML FORM support in their clients, links are provided in both plain text and html versions of the mail.

Deployment

To use this survey system as-is:

  1. Create a new spreadsheet in your Drive account. Add headers for "Timestamp", "Serial Number", "Commuter?", and "Vehicle" in row 1.
  2. Tools - Script Editor. Copy the Code.js content. Copy the ID of your spreadsheet, and update the _spreadsheetId variable at the top of the file. Save.
  3. File - New HTML file, name the file emailTemplate. Copy the emailTemplate.html content. Save.
  4. 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.)
  5. 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.

@user112
Copy link

user112 commented Sep 17, 2013

User gets survey email, but when I click on submit button in the e-mail, then I get a blank page with text "Bad Value" and no change in spreadsheet.

@mogsdad
Copy link
Author

mogsdad commented Sep 17, 2013

@user112 - Assuming that you've modified the code for your own form, have you ensured that the element name tags in the html match the parameters name in doPost()? That error indicates that the doPost() has tried to access a property in parameters that doesn't exist.

@user112
Copy link

user112 commented Sep 19, 2013

I have not changed any variable, was just checking your example as I have the same requirement, I just changed 'spreadsheet ID'. I am able to receive e-mail and when I select checkbox and click Submit button, it gives Error page with 'Bad Value' and when I click on the alternative link it shows error page with message "Script function not found: doGet". Thanks in advance.

@mogsdad
Copy link
Author

mogsdad commented Oct 3, 2013

Sounds like you haven't published the script, see deployment steps 4 & 5.

@Alsaegh
Copy link

Alsaegh commented Jun 8, 2014

Work like a charm!
Now I need to change the input type to use text-box instead of the two check-boxes for the survey?
I'm not an expert, tried to change the Html and gs files but getting error on submitting the survey.
Appreciate if you can help me with this :)

@hemantaggarwal
Copy link

I am also trying something similar to this, sending bulk mails given in a spreadsheet. I want to ask if there is any way to extract email id of receiver from metadata or header and when that user clicks "submit" button, email id will go as parameter to doPost() method and it can be stored in spreadsheet?

@Omer-Kleiner
Copy link

Omer-Kleiner commented Sep 18, 2021

Fingers crossed you are still around:

  1. I've implemented this and it works well except that when the alternate online form is submitted an error loads. For chrome it says "script.googleusercontent.com refused to connect.". On Firefox it says "Firefox cannot open this page with a this link to learn more.
    Most of the correct info still gets passed to the sheet (except the serial code), but the error itself makes it hard to distribute to clients.

  2. To make the serial number meaning full I swapped out the GUID generator within getGUID and just input in a string for the name of the person I'm sending the email too. It works perfectly when submitting the embedded form but when the pop up form is submitted a new GUID is generated and inputted into the cell instead. I'm not even sure where it is coming from because I removed the GUID generator. Even in testing when I returned it the number that comes back is different than the one that went out. Is it possible to address this issue so I can have identifiable date from submissions?

I got it a few minutes after I typed this. Needed to deploy a new version of the app not just update the current.

@mogsdad
Copy link
Author

mogsdad commented Sep 19, 2021

I haven’t touched any App Script in years, and the environment has changed since this was originally written. I can empathize about struggles modifying the code, but I don’t have time to commit to maintenance or modifications.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment