I am finding that more and more little applications that I have use Google Spreadsheets to store some data that I use in an Ajax app. After using the core API, you find yourself looking at fun code like foo.$t.

Most of the time I want a simple tabular view over a spreadsheet that has the first row as a header, and other rows as the data.

To do this I created GSpreadsheet which lets me do:


GSpreadsheet.load("pSYwzniwpzSFnt8Ix3ohQQA", { index: 'firstname' }, function(gs) {
// display all
document.getElementById("displayall").innerHTML = gs.displayAll();

// show one
var row = gs.select('Bob');
document.getElementById("onebyindex").innerHTML = row.email;

// show by row number
row = gs.select(1);
document.getElementById("onebyrownum").innerHTML = row.email;

// display one row
document.getElementById("displayrow").innerHTML = gs.displayRow('Bob');
});


You will see that you call GSpreadsheet.load(..., callback(takesAgsObject))

This is because of all of the asynchronous work going on. To get the JSON from the Spreadsheet back end you are always using the json-in-script output, and getting it by dynamically creating a script tag. The real dirty hack in this code is how to do that, and have the callback give you back the info to create the new object. To do this, I am creating a static method on the fly with eval() and calling into it passing in the right info. It's real ugly:


GSpreadsheet.load = function(key, options, callback) {
if (!options['worksheet']) options['worksheet'] = 'od6';
var worksheet = options['worksheet'];

var callbackName = "GSpreadsheet.loader_" + key + "_" + worksheet;
eval(callbackName + " = function(json) { var gs = new GSpreadsheet(key, json, options); callback(gs); }");

var script = document.createElement('script');

script.setAttribute('src', 'http://spreadsheets.google.com/feeds/list/' + key + '/' + worksheet + '/public/values' +
'?alt=json-in-script&callback=' + callbackName);
script.setAttribute('id', 'jsonScript');
script.setAttribute('type', 'text/javascript');
document.documentElement.firstChild.appendChild(script);
}