How to Create a Fully-Customizable Google Form

Image for post
Image for post

Look, we all know that Google Forms are really convenient and helpful, but their design and limited customization options leave a lot to be desired. If you’re using Google Forms for business, you may prefer Forms for its functionality, but because of its rigid style and limited customization, it can come off as unprofessional.

In this post, I will show you how to:

Create the Google Script that Sends Responses to your Email

Create the HTML

Bonus — Display Responses (Optional)

Step #1: Create the Google Script that Sends Responses to your Email

Create a new Google Sheet, name it whatever you want. However, the first column of your sheet should be ‘Timestamp.’

Image for post
Image for post

Next, go to Tools → Script Editor (special thanks to Martin Hawksey)


function formatMailBody(obj, order) {
var result = "";
if (!order) {
order = Object.keys(obj);
}

// loop over all keys in the ordered form data
for (var idx in order) {
var key = order[idx];
result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
// for every key, concatenate an `<h4 />`/`<div />` pairing of the key name and its value,
// and append it to the `result` string created at the start.
}
return result; // once the looping is done, `result` will be one long string to put in the email body
}
// sanitize content from the user - trust no one
// ref: https://developers.google.com/apps-script/reference/html/html-output#appendUntrusted(String)
function sanitizeInput(rawInput) {
var placeholder = HtmlService.createHtmlOutput(" ");
placeholder.appendUntrusted(rawInput);

return placeholder.getContent();
}
function doPost(e) {try {
Logger.log(e); // the Google Script version of console.log see: Class Logger
record_data(e);

// shorter name for form data
var mailData = e.parameters;
// names and order of form elements (if set)
var orderParameter = e.parameters.formDataNameOrder;
var dataOrder;
if (orderParameter) {
dataOrder = JSON.parse(orderParameter);
}

// determine recipient of the email
// if you have your email uncommented above, it uses that `TO_ADDRESS`
// otherwise, it defaults to the email provided by the form's data attribute
var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : mailData.formGoogleSendEmail;

// send email if to address is set
if (sendEmailTo) {
MailApp.sendEmail({
to: String(sendEmailTo),
subject: "YOUR_SUBJECT", //come up with a subject line
// replyTo: String(mailData.email), // This is optional and reliant on your form actually collecting a field named `email`
htmlBody: formatMailBody(mailData, dataOrder)
});
}
return ContentService // return json success results
.createTextOutput(
JSON.stringify({"result":"success",
"data": JSON.stringify(e.parameters) }))
.setMimeType(ContentService.MimeType.JSON);
} catch(error) { // if error return this
Logger.log(error);
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": error}))
.setMimeType(ContentService.MimeType.JSON);
}
}
/**
* record_data inserts the data received from the html form submission
* e is the data received from the POST
*/
function record_data(e) {
var lock = LockService.getDocumentLock();
lock.waitLock(30000); // hold off up to 30 sec to avoid concurrent writing

try {
Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it

// select the 'responses' sheet by default
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = e.parameters.formGoogleSheetName || "responses";
var sheet = doc.getSheetByName(sheetName);

var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var newHeader = oldHeader.slice();
var fieldsFromForm = getDataColumns(e.parameters);
var row = [new Date()]; // first element in the row should always be a timestamp

// loop through the header columns
for (var i = 1; i < oldHeader.length; i++) { // start at 1 to avoid Timestamp column
var field = oldHeader[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);

// mark as stored by removing from form fields
var formIndex = fieldsFromForm.indexOf(field);
if (formIndex > -1) {
fieldsFromForm.splice(formIndex, 1);
}
}

// set any new fields in our form
for (var i = 0; i < fieldsFromForm.length; i++) {
var field = fieldsFromForm[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
newHeader.push(field);
}

// more efficient to set values as [][] array than individually
var nextRow = sheet.getLastRow() + 1; // get next row
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// update header row with any new data
if (newHeader.length > oldHeader.length) {
sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
}
}
catch(error) {
Logger.log(error);
}
finally {
lock.releaseLock();
return;
}
}function getDataColumns(data) {
return Object.keys(data).filter(function(column) {
return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
});
}
function getFieldFromData(field, data) {
var values = data[field] || '';
var output = values.join ? values.join(', ') : values;
return output;
}
function onFormSubmit (e) {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = e.parameters.formGoogleSheetName || "responses";
var sheet = doc.getSheetByName(sheetName);
var data = sheet.getDataRange().getValues();
var userNames = sheet.getDataRange().offset(1, 1, sheet.getLastRow()-1,1).getValues().join();


var re = new RegExp(event.namedValues["name"],"g");
var count = userNames.match(re).length;

if (count != 1 ) {

sheet.deleteRow(event.range.getRow());
}


}

This script accomplishes everything we need, which is that every time someone responds to the form, it updates the spreadsheet and sends a notification to your email. Save the program and do the following to deploy it:

Image for post
Image for post

Go to ‘Publish’ → ‘Deploy as a web app’

Your email address is the default for ‘Execute the app as:’ and for ‘Who has access to the app:’ you must select ‘Anyone, even anonymous.’ Hit update and copy the script url.

Create the HTML

Save the below as index.html

<html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><meta name="description" content="contact form example"><link href='https://fonts.googleapis.com/css?family=Montserrat' rel='stylesheet'><link rel="stylesheet" href="https://unpkg.com/purecss@1.0.0/build/pure-min.css"><link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.4.0/css/font-awesome.min.css"><link rel="stylesheet" href="style.css"><title>MY FORM</title><style></style></head><body><h2>Math Quiz</h2><form class="gform pure-form pure-form-stacked" method="POST" data-email="example@email.net"
action="YOUR_SCRIPT_URL">
<!-- change the form action to your script url --><div class="form-elements"><fieldset class="pure-group"><label for="name">Username</label><input id="name" name="name" placeholder="username" /></fieldset><fieldset class="pure-group"><label for="answer">What's 2 + 2?</label>
<input id="answer" name="answer" placeholder="Answer" />
</fieldset><br><button class="button-success pure-button button-xlarge">Submit!</button></div><!-- Customize the Thankyou Message People See when they submit the form: --><br><br><div class="thankyou_message" style="display:none;"><h2><em><b>Thanks for your response!</b></em></h2></div></form><!-- Submit the Form to Google Using "AJAX" --><script data-cfasync="false" src="form-submission-handler.js"></script><!-- END --></body></html>

Save the below as form-submission-handler.js

// get all data in form and return objectfunction getFormData(form) {var elements = form.elements;var honeypot;var fields = Object.keys(elements).filter(function(k) {if (elements[k].name === "honeypot") {honeypot = elements[k].value;return false;}return true;}).map(function(k) {if(elements[k].name !== undefined) {return elements[k].name;// special case for Edge's html collection}else if(elements[k].length > 0){return elements[k].item(0).name;}}).filter(function(item, pos, self) {return self.indexOf(item) == pos && item;});var formData = {};fields.forEach(function(name){var element = elements[name];// singular form elements just have one valueformData[name] = element.value;// when our element has multiple items, get their valuesif (element.length) {var data = [];for (var i = 0; i < element.length; i++) {var item = element.item(i);if (item.checked || item.selected) {data.push(item.value);}}formData[name] = data.join(', ');}});// add form-specific values into the dataformData.formDataNameOrder = JSON.stringify(fields);formData.formGoogleSheetName = form.dataset.sheet || "responses"; // default sheet nameformData.formGoogleSendEmail= form.dataset.email || ""; // no email by defaultreturn {data: formData, honeypot: honeypot};}function handleFormSubmit(event) {  // handles form submit without any jqueryevent.preventDefault();           // we are submitting via xhr belowvar form = event.target;var formData = getFormData(form);var data = formData.data;// If a honeypot field is filled, assume it was done so by a spam bot.if (formData.honeypot) {return false;}disableAllButtons(form);var url = form.action;var xhr = new XMLHttpRequest();xhr.open('POST', url);// xhr.withCredentials = true;xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");xhr.onreadystatechange = function() {if (xhr.readyState === 4 && xhr.status === 200) {form.reset();var formElements = form.querySelector(".form-elements")if (formElements) {formElements.style.display = "none"; // hide form}var thankYouMessage = form.querySelector(".thankyou_message");if (thankYouMessage) {thankYouMessage.style.display = "block";}}};// url encode form data for sending as post datavar encoded = Object.keys(data).map(function(k) {return encodeURIComponent(k) + "=" + encodeURIComponent(data[k]);}).join('&');xhr.send(encoded);}function loaded() {// bind to the submit event of our formvar forms = document.querySelectorAll("form.gform");for (var i = 0; i < forms.length; i++) {forms[i].addEventListener("submit", handleFormSubmit, false);}};document.addEventListener("DOMContentLoaded", loaded, false);function disableAllButtons(form) {var buttons = form.querySelectorAll("button");for (var i = 0; i < buttons.length; i++) {buttons[i].disabled = true;}}})();

Note: In each fieldset, ‘name=’ must match the column name of the Google Sheet.

That’s it! You can style the page however you like. The form is now fully customizable, and each response gets sent to your email.

Bonus — Display Results (Optional)

If the form is something like a poll and you’d like to show the participant the results immediately after submitting, go back to the Google Sheet. Select File → Publish to the web → Select to publish only the chart. Copy the link.

In index.html you can add these lines of code beneath the thank you message:

<iframe width="600" height="371" seamless frameborder="0" scrolling="no" src="INSERT_CHART_LINK_HERE"></iframe></div>

Now, whenever a person submits a response, they can see the results in realtime on the same page!

Non-CS major coming up with fun coding projects with real-world application.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store