Update 04/07/2014: I’ve revisited this code and made a number of improvements. Read more in Google Sheets as a Database – INSERT with Apps Script using POST/GET methods (with ajax example)
Update: See comment by James to streamline this even more.
I was recently rediscovering an old Hirst post on Google Spreadsheets as a Database in which he demos an “interface for constructing URIs to query Google spreadsheets using the Google query language” . I found myself at this post after researching a ‘cunning idea’ I’ve in development and also because Google Search ‘probably’ knows best.
Anyway this post is unrelated to the ‘cunning idea’ but I got thinking one of the aspects of databases is getting data in. I tackled this when I was at Dev8D back in February with the post Collecting any data in a Google Spreadsheet using GET or POST. At the time I was planning a follow-up post to show a practical application but never got around to it.
So here is a more refined piece of code which actually records data submitted from your own form or custom code:
/*
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 doGet(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());
}
There are a couple of ways you can submit data. You can use a basic HTML form (this example shows how you can avoid redirecting to the Google macro/service page using a hidden iframe (I tried using ajax to submit the data without refresh but you run into cross domain/XHTTP security issues. I imagine you could get around this by having a intermediary PHP page that passes the data as POST or GET. If you are going down either of these solutions remember to allow anonymous access when you publish as a service in Google Spreadsheets)). Here’s the spreadsheet where the data from the basic form example is being collected.
There are other ways to customise input to Google Spreadsheets e.g. my custom bookmarking service using Google Spreadsheet post.
Let me know if I’ve forgotten anything …