Post to google spreadsheet from html form

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 = []; 
    
      // IF there is a url parameter of reset - then it will clear sheet
    if (e.parameter.hasOwnProperty("reset")) {
      // clear everything except the header row until the last row for all headers      
      sheet.getRange(headRow + 1,1, nextRow,headers.length).clear();
      
      // return the clear result
      return ContentService
          .createTextOutput(JSON.stringify({"result": "success", "cleared": nextRow }))
          .setMimeType(ContentService.MimeType.JSON);
    }
    
    // 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="[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>

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