- 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.
- 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>
That's a bare bones example that should get you up and running.