Skip to content

Instantly share code, notes, and snippets.

@willpatera

willpatera/Google-Sheet-Form-Post.md

Last active November 9, 2024 15:22
Show Gist options
  • Save willpatera/ee41ae374d3c9839c2d6 to your computer and use it in GitHub Desktop.
Save willpatera/ee41ae374d3c9839c2d6 to your computer and use it in GitHub Desktop.
Download ZIP
Post to google spreadsheet from html form
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

Depreciation Warning: This code is not maintained, and should be seen as reference implementation only. If you're looking to add features or update, fork the code and update as needed.

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
google_script.gs
// original from: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
function doGet(e){
return handleResponse(e);
}
// Usage
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
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 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());
}
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="[email protected]"/>
</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);
}
});
});
});
@FergusCoulter
Copy link

Hey, this is a great script. It's odd how it's posting with a GET command but y'know still cool.

One little question though, how secure is this? The script is visible via the inspect source tool so the details can be seen, script ID and what not. I'm not 100% on web security but I would view this as an issue. Can you please confirm?

@hossainahmedkhan
Copy link

Dear,

Once the index.html is created into 'gd' then how should the data go to spreadsheet! How is the script would look Like!
Thank you.

@elson-faal
Copy link

works here, but only post text data, don't work with de input type file, does note upload files.

How can i make a form that upload a file from inputs type file AND set de data of other inputs (or textareas etc) in the spreadsheet AND put a link to the file in the same spreadsheet?

I managed to upload a file by this method: https://issuetracker.google.com/issues/36764534
i also managed to set other kind of data to the google drive's spreadsheet, from other form, by the method of this page.

but i can't do both in the same html form. Is it possible?

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