Static sites are ever so useful. Not everything needs CMS like WordPress or some other active website framework. I use Middleman to create static sites, but there are several other tools available.
If one hosts one’s static site at a server where PHP is available, then collecting information entered into a form is not difficult. But what if one hosts at Amazon S3, where no scripting language is available?
There exists commercial services for handling form data, such as JotForm. But maybe you don’t want to spend $8/month (personal) or $20/month (commercial) to collect form data.
Although documentation exist on posting form data to google sheet as a database like the post by Martin Hawksey where he showed how to use Google Sheets as a Database - INSERT with Apps Script using POST/GET methods (with ajax example) using a combination of JavaScript in the browser and a Google App Script to send form data to a spreadsheet (Google Sheets) the steps were not well spelled out .
The Form
Create a file named
index.html and copy this into it:
<html>
<head>
<!-- META DATA -->
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="description" content="Demo form ">
<meta name="author" content="wwww.gigzweb.blogspot.com">
<title>demo page</title>
<link rel="stylesheet" href="css/style.css">
<!-- Bootstrap Core CSS -->
<link rel="stylesheet" href="css/bootstrap.min.css" type="text/css">
<!-- Custom Fonts -->
<link href='http://fonts.googleapis.com/css?family=Asap:400,700' rel='stylesheet' type='text/css'>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
</head>
<body>
<div background-color:gray;>
<div class="col-md-8">
<h3>Contact Form</h3>
<form name="form1" method="post" id="defaultgsheets" action= "">
<div class="row">
<div class="col-md-6">
<div class="form-group">
<input type="text" class="form-control input-lg" name="name" id="name" placeholder="Enter name" required="required" />
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<input type="email" class="form-control input-lg" name="email" id="email" placeholder="Enter email" required="required" />
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<div class="form-group">
<input type="text" class="form-control input-lg" name="subject" id="subject" placeholder="Subject" required="required" />
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<div class="form-group">
<textarea name="message" id="message" class="form-control" rows="4" cols="25" required="required"
placeholder="Message" style="height: 190px;"></textarea>
</div>
<button type="submit" class="btn btn-skin btn-block" name="submit" id="submit">Submit</button>
</div>
</div>
</form>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<!-- Custom Theme JavaScript -->
<script src='google-sheet.js'></script>
</div>
</div>
</div>
</body>
</html>
The Javascript
Create a file named
google-sheet.js in the same directory and copy this into it:
// Variable to hold request
var request;
// Bind to the submit event of our form
$("#defaultgsheets").submit(function(event){
// Abort any pending request
if (request) {
request.abort();
}
// setup some local variables
var $form = $(this);
// Let's select and cache all the fields
var $inputs = $form.find("input, select, button, textarea");
// Serialize the data in the form
var serializedData = $form.serialize();
// Let's disable the inputs for the duration of the Ajax request.
// Note: we disable elements AFTER the form data has been serialized.
// Disabled form elements will not be serialized.
$inputs.prop("disabled", true);
// Fire off the request to /form.php
request = $.ajax({
url: "SCRIPT URL GOES HERE",
type: "post",
data: serializedData
});
// Callback handler that will be called on success
request.done(function (response, textStatus, jqXHR){
// Log a message to the console
console.log("Hooray, it worked!");
console.log(response);
console.log(textStatus);
console.log(jqXHR);
});
// Callback handler that will be called on failure
request.fail(function (jqXHR, textStatus, errorThrown){
// Log the error to the console
console.error(
"The following error occurred: "+
textStatus, errorThrown
);
});
// Callback handler that will be called regardless
// if the request failed or succeeded
request.always(function () {
// Reenable the inputs
$inputs.prop("disabled", false);
});
// Prevent default posting of form
event.preventDefault();
});
The Sheet
Navigate to drive.google.com and click on NEW > Google Sheets to create a new Sheet. Give it a name, perhaps “Form Google Sheets”. Put the following names into the first row of the first five columns:
Timestamp name email subject message
The Script
Click on Tools > Script Editor…, which should open a new window and a dialog called ‘Google Apps Script’. Click on Create script for > Custom Functions in Sheets. This will create one script called 'Code.gs’ containing functions such as SAY_HELLO.
Click on 'Untitled Project’ at the top and give this project a name: 'Form Script’.
Highlight all of this script (we are going to replace it) and paste in the following:
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
In the menus click on File > Manage Versions… We must save a version of the script for it to be called. In the box labeled 'Describe what has changed’ type 'Initial version’ and click on 'Save New Version’, then on 'OK’.
Back to the menus: click on Resources > Current roject’s triggers. In this dialog click on 'No triggers set up. Click here to add one now’. In the dropdowns select 'doPost’, 'From spreadsheet’, and 'On form submit’, then click on 'Save’.
Back to the menus: click on Publish > Deploy as web app… For 'Who has access to the app:’ select 'Anyone, even anonymous’. Leave 'Execute the app as:’ set to 'Me’ and Project Version to '1’. Click the 'Deploy’ button.
A dialog should appear announcing 'This project is now deployed as a web app’. Copy the Current web app URL from the dialog; it should look something like:
https://script.google.com/macros/s/AKfycbw6RTOxn5OT_BIw9Nl_3KoFSXEQEbiKSZCLyombb1YqkGfRKUSz/exec
Click OK.
Now go back to
google-sheet.js and replace 'SCRIPT URL GOES HERE’ with the URL copied from the dialog.Display or refresh the
index.html web page. Enter data into the four fields and click on the 'Send’ button. Within a few seconds that data should appear in your Google sheet. If your browser is Google Chrome, right-click in the web page and click on Inspect Element > Console. It should show:Hooray, it worked!
- Object
success
- Object
Well done! You can now modify this form and drop it into any web page to collect–at no cost–responses from those who visit your page. With a little research and effort you may be able to get Google Apps to email every time someone submits your form.
Posts
