How to Post to a Google Spreadsheet

gistfile1.md

How to Post to a Google Spreadsheet

Part 1 - Setting up the Google Document

• Go to Google Docs and create your spreadsheet
• Give column names and make note of them as they become the names of your input fields
• Give the spreadsheet the tab name of "DATA"
• Click Tools > Script Editor
• Choose 'Spreadsheet' under 'Create Script for'
• Paste the following code at the bottom of the Code.gs file

/*   
   Copyright 2011 Martin Hawksey

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

/* Usage
1. Run the setup function (you'll need to do this twice - 1st time to grant acces to Script Properties)
2. Share > Publish as service ... set security level and enable service
3. Copy the service URL and post this in your form/script action  
4. Insert column names on the DATA sheet matching the parameter names of the data you are passing
*/



function doPost(e) { // change to doPost(e) if you are recieving POST data
  var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));
  var sheet = ss.getSheetByName("DATA");
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; //read headers
  var nextRow = sheet.getLastRow(); // get next row
  var cell = sheet.getRange('a1');
  var col = 0;
  for (i in headers){ // loop through the headers and if a parameter name matches the header name insert the value
    if (headers[i] == "Timestamp"){
      val = new Date();
    } else {
      val = e.parameter[headers[i]]; 
    }
    cell.offset(nextRow, col).setValue(val);
    col++;
  }
  //http://www.google.com/support/forum/p/apps-script/thread?tid=04d9d3d4922b8bfb&hl=en
  var app = UiApp.createApplication(); // included this part for debugging so you can see what data is coming in
  var panel = app.createVerticalPanel();
  for( p in e.parameters){
    panel.add(app.createLabel(p +" "+e.parameters[p]));
  }
  app.add(panel);
  return app;
}
//http://www.google.sc/support/forum/p/apps-script/thread?tid=345591f349a25cb4&hl=en
function setUp() {
  ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
}

• Save the project
• Select the Function 'setUp' and run it by clicking the 'Play' button
• Modal will popup and you'll have to grant access
• Run the function again. It won't do much...that's normal
• Click File > Manage Versions... and save a version
• Click Publish > Deploy as web app
• Execute as 'me' and only allow everyone even anon with access to the app.
• Click Deploy
• Copy the 'Current web app URL.' It becomes the action for your form element.

Part 2 - Creating the Form

• Create a form using your previously copied app URL, here is an example:

<form id="formID" method="post" action="https://script.google.com/macros/s/AKfycbw5yDwjyTzeAlBqtCqLj0vLQyZVWvAdQ6Qpq_iVMMs_g43CXYKx/exec" target="hidden_iframe">
    <label for="First_Name">First Name</label><input type="text" name="First_Name" value="" id="First_Name"><br />
    <label for="Last_name">Last Name</label><input type="text" name="Last_Name" value="" id="Last_Name"><br />
    <label for="Organization">Organization</label><input type="text" name="Organization" value="" id="Organization"><br />
    <label for="Entity">Entity</label><input type="text" name="Entity" value="" id="Entity"><br />
    <label for="Location">Location</label><input type="text" name="Location" value="" id="Location">
    <input type="submit" name="" value="Submit" id="">
  </form>

• The target will be an empty iframe to avoid cross-domain issues with Google. Here is an example:

<iframe name="hidden_iframe" id="hidden_iframe" style="display:none;"></iframe>

• Chances are your're using jQuery these days so here's your submit:

<script>
   $('#formID').submit();
</script>

Conclusion

That's a bare bones example that should get you up and running.

Helpful Resources

• Google Spreadsheets as a Database – INSERT with Apps Script form POST/GET submit method

I get this error
Refused to display 'https://script.google.com/macros/s/...../exec' in a frame because it set 'X-Frame-Options' to 'SAMEORIGIN'.

yean, get the same error.