dimcoderx/Google-Sheet-Form-Post.md

## Google-Sheet-Form-Post.md

      
    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


## 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());
}

## 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>

## 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>

## 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);
                }
            });
    });
});
	// 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());
	}
	<!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>
	$(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);
	}
	});
	});
	});