google docs - insert data from an external form to a spreadsheet

README.md

Google Docs insert data from external form into a spreadsheet

• script.js is on the html page, it sends the data to google docs
• the javascript in collection.js takes the data and inserts it in the correct place in the spreadsheet

you can see it live in action:

• input is at http://jsfiddle.net/nbass/DpEAb/
• collection spreadsheet is at https://docs.google.com/spreadsheet/ccc?key=0Ancky9VbVd4FdE1JcTZfcFZOQUgxallvRUV4QUJvUUE

collect.js

// forked from https://gist.github.com/mhawksey/1276293

// function to recieve the data from the form
function doPost(e) {
    // get the correct spreadsheet
    var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));
  
    // get the sheet with a name of "DATA"
    var sheet = ss.getSheetByName("DATA");
  
    // find the headers of the sheet and their values
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
    // get the row below the headers
    var nextRow = sheet.getLastRow();
  
    // get the first cell
    var cell = sheet.getRange('a1');
  
    // the first column in the sheet
    var col = 0;
  
    // insert the data into the spreadsheet in a loop only if the header is the same as the html name=""
    for (i in headers){
      if (headers[i] == "Timestamp"){
          val = new Date();
      } else {
          val = e.parameter[headers[i]]; 
      }
      cell.offset(nextRow, col).setValue(val);
      col++;
    }
  
    // debugging
    var app = UiApp.createApplication();
    var panel = app.createVerticalPanel();
    for(p in e.parameters){
        panel.add(app.createLabel(p +" "+e.parameters[p]));
    }
    app.add(panel);
  
    return app;
}

// function to init the spreadsheet for the first time
function setUp() {
    ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
}

form.html

<form>
    <input type="text" placeholder="name" name="Name">
    <input type="text" placeholder="comment" name="Comment">
    
    <input type="submit" value="submit">
</form>

script.js

$('form').submit(function() {
    // post the form data to the spreadsheet
    $.post('https://script.google.com/macros/s/AKfycbyOyFG6fPlMOZi6Biv8Df9qIY46_DYWK4YiPbR8hl-nfb7omMs/exec', $('form').serialize());
    
    // reset the value on the input
    $('input[type=text]').val('');
    return false;
});