Skip to content

Instantly share code, notes, and snippets.

@koyanloshe

koyanloshe/Google-Sheet-Form-Post.md

Forked from willpatera/Google-Sheet-Form-Post.md
Last active April 17, 2020 08:32
Show Gist options
  • Save koyanloshe/96ce65ec58fe5995b96c to your computer and use it in GitHub Desktop.
Save koyanloshe/96ce65ec58fe5995b96c to your computer and use it in GitHub Desktop.
Download ZIP
Post to google spreadsheet from html form #HTML
Raw
Google-Sheet-Form-Post.md

Overview

This collection of files serves as a simple static demonstration of how to post to a google spreadsheet from an external html <form> following the example by Martin Hawksey

Run example

You should be able to just open index.html in your browser and test locally.

However if there are some permissions errors you can make a quick html server with python. Open terminal and cd to the directory where the gist files are located and enter python -m SimpleHTTPServer. By default this creates a local server at localhost:8000

If you're using python 3 the command differs slightly.

Google Spreadsheet

The spreadsheet is located here

TODO

  • Resolve 405 Error on Safari
  • Workaround for 405 error with Safari browser check
  • Add user feedback while ajax is submitting the request
  • Validation using Bootstrap Validator
Raw
index.html
<!DOCTYPE html>
<html>
<head>
<title>Post to Google Sheet Demo</title>
<!-- bootstrap & fontawesome css -->
<link href="http://cdn.jsdelivr.net/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet"/>
<link rel="stylesheet" href="http://cdn.jsdelivr.net/fontawesome/4.1.0/css/font-awesome.min.css" />
<!-- BootstrapValidator CSS -->
<link rel="stylesheet" href="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/css/bootstrapValidator.min.css"/>
<!-- jQuery and Bootstrap JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript" src="http://cdn.jsdelivr.net/bootstrap/3.2.0/js/bootstrap.min.js"></script>
<!-- BootstrapValidator JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/js/bootstrapValidator.min.js"></script>
<!-- Animated Loading Icon -->
<style type="text/css">
.glyphicon-refresh-animate {
-animation: spin .7s infinite linear;
-webkit-animation: spin2 .7s infinite linear;
}
@-webkit-keyframes spin2 {
from { -webkit-transform: rotate(0deg);}
to { -webkit-transform: rotate(360deg);}
}
@keyframes spin {
from { transform: scale(1) rotate(0deg);}
to { transform: scale(1) rotate(360deg);}
}
</style>
</head>
<body>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-lg-12">
<h2>Post to Google Sheets with form validation</h2>
<p>
Based on Martin Hawksey's <a href="http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/" target="_blank">example</a>.
<p>
</div>
</div>
<hr>
<form class="form-horizontal" role="form" id="test-form">
<div class="form-group">
<label class="col-lg-3 control-label">First Name</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group">
<input type="text" class="form-control" name="firstName" placeholder="First Name"/>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Last Name</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group">
<input type="text" class="form-control" name="lastName" placeholder="Last Name"/>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Email</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group">
<input type="text" class="form-control" name="email" placeholder="name@domain.com"/>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Address</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group col-lg-10">
<textarea type="text" class="form-control" name="address" placeholder="Enter your address here" rows="4" style="resize: vertical;"></textarea>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Notes</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group col-lg-10">
<textarea type="text" class="form-control" name="notes" placeholder="Comments or questions?" rows="4" style="resize: vertical;"></textarea>
</div>
</div>
</div>
<div class="form-group">
<div class="col-lg-9 col-lg-offset-3">
<button type="submit" class="btn btn-default" id="postForm">Submit</button>
</div>
</div>
</form>
</div>
</body>
<footer>
<script src="validation-functions.js"></script>
</footer>
</html>
Raw
success-page.html
<!DOCTYPE html>
<html>
<head>
<title>Post to Google Sheet Demo</title>
<!-- bootstrap & fontawesome css -->
<link href="http://cdn.jsdelivr.net/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet"/>
<link rel="stylesheet" href="http://cdn.jsdelivr.net/fontawesome/4.1.0/css/font-awesome.min.css" />
<!-- BootstrapValidator CSS -->
<link rel="stylesheet" href="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/css/bootstrapValidator.min.css"/>
<!-- jQuery and Bootstrap JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript" src="http://cdn.jsdelivr.net/bootstrap/3.2.0/js/bootstrap.min.js"></script>
<!-- BootstrapValidator JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/js/bootstrapValidator.min.js"></script>
</head>
<body>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-lg-12">
<h2>Success</h2>
<p>
Thanks for submitting the form - check out the responses submitted in the <a href="https://docs.google.com/spreadsheets/d/1p6X_HejWbIBx3eDDSacNvkVI3vFkFMSq4XW4rUDTyAw/edit?usp=sharing" target="_blank">spreadsheet</a>
<p>
</div>
</div>
<hr>
</body>
<footer>
</footer>
</html>
Raw
validation-functions.js
$(document).ready(function() {
$('#test-form').bootstrapValidator({
//submitButtons: '#postForm',
// To use feedback icons, ensure that you use Bootstrap v3.1.0 or later
feedbackIcons: {
valid: 'glyphicon glyphicon-ok',
invalid: 'glyphicon glyphicon-remove',
validating: 'glyphicon glyphicon-refresh'
},
fields: {
firstName: {
message: 'The first name is not valid',
validators: {
notEmpty: {
message: 'The first name is required and cannot be empty'
},
stringLength: {
min: 1,
max: 30,
message: 'The first name must be more than 1 and less than 30 characters long'
},
regexp: {
regexp: /^[A-z]+$/,
message: 'The first name can only accept alphabetical input'
},
}
},
lastName: {
message: 'Last Name is not valid',
validators: {
notEmpty: {
message: 'Last Name is required and cannot be empty'
},
stringLength: {
min: 1,
max: 30,
message: 'Last Name must be more than 1 and less than 30 characters long'
},
regexp: {
regexp: /^[A-z]+$/,
message: 'Last Names can only consist of alphabetical characters'
},
}
},
email: {
validators: {
notEmpty: {
message: 'The email address is required and cannot be empty'
},
emailAddress: {
message: 'The email address is not a valid'
}
}
},
address: {
message: 'Address is not valid',
validators: {
notEmpty: {
message: 'Address is required and cannot be empty'
}
}
},
}
})
.on('success.form.bv', function(e) {
// Prevent form submission
e.preventDefault();
// Get the form instance
var $form = $(e.target);
// Get the BootstrapValidator instance
var bv = $form.data('bootstrapValidator');
// Use Ajax to submit form data
var url = 'https://script.google.com/macros/s/AKfycbzy3UKiaESk9y2ccnBkdSgCo1zxJ0Kx0qE0_eKro7QYE5yFKJVH/exec';
var redirectUrl = 'success-page.html';
// show the loading
$('#postForm').prepend($('<span></span>').addClass('glyphicon glyphicon-refresh glyphicon-refresh-animate'));
var jqxhr = $.post(url, $form.serialize(), function(data) {
console.log("Success! Data: " + data.statusText);
$(location).attr('href',redirectUrl);
})
.fail(function(data) {
console.warn("Error! Data: " + data.statusText);
// HACK - check if browser is Safari - and redirect even if fail b/c we know the form submits.
if (navigator.userAgent.search("Safari") >= 0 && navigator.userAgent.search("Chrome") < 0) {
//alert("Browser is Safari -- we get an error, but the form still submits -- continue.");
$(location).attr('href',redirectUrl);
}
});
});
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment