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);
}
June 14th, 2007 at 1:37 am
“enjoyed reading this ,thanks”
http://www.mp4-converter.net/dvd-to-mp4/
July 30th, 2007 at 7:15 am
if one tries to overhaul two rabbits he can catch no one
December 20th, 2007 at 12:52 pm
virginia is a fairy
June 13th, 2008 at 7:25 am
Hi, I have been trying to use your example with one of my spreadsheets and I can’t get it to work. The only things I changed were the key and the variables for the spreadsheet. Is there a problem with the formating of my key? This is the error that I got on my page.
Error: invalid assignment left-hand side
Line: 87, Column: 48
Source Code:
GSpreadsheet.loader_p_Ixp7T-h4ecEKfNSDU4ddQ_od6 = function(json) { var gs = new GSpreadsheet(key, json, options); callback(gs); }
Does this have to do with JSON-In-Script? I have looked through the Spreadsheet API and I am afraid that I am lost. Any help or direction would be much appreciated. Thanks.
June 19th, 2008 at 9:24 am
Alright, I still haven’t got this to work with my spreadsheet, but I did get it work with another spreadsheet that I found online (key=pc4wtY50XBCN7dR1cbIJmDA). This narrowed the problem down to the key, because both of the keys that worked did not have any symbols in them. Mine does, and now I am going to look at the gspreadsheet.js to see if I can modify it to correct this problem. Any help would be appreciated. Thanks.
August 15th, 2008 at 1:31 pm
Hey,
Thanks a lot for the article – I have found it very useful.
I have managed to get the code working perfectly in FF with slight modification and the addition of several functions.
I would love to use this code in application however the spreadsheets will not load (client side) in IE as the browser cannot find a privacy policy for the spreadsheets and blocks them =(
Is there any way to get around this?
Thanks again, Colin
September 14th, 2008 at 3:05 pm
I realized that the issue is with the character ‘-’ inside the key.
I patched the definition of callback with 2 replace of the ‘-’ into a ‘_’
var callbackName = “GSpreadsheet.loader_” + key.replace(”\-”,”_”).replace(”\-”,”_”)+”_” + worksheet;
It’s not an elegant solution but really i couldn’t figure out how to realize a replaceAll with js…
September 14th, 2008 at 7:52 pm
Nicola – thanks for the fix!
February 25th, 2009 at 2:09 pm
Nicola – Great example. I’m having the same issue with the key even after I swapped out the function. It looks like your swapping the “-” for an “_”. This changes the key value and causes it not to work. Any way to make this work with a “-” in the ID?
June 23rd, 2010 at 4:48 pm
You can do:
key.replace(/-/g, “_”)
to replace all dashes with underscores.