Custom Search Engine that automatically knows what to site restrict GeoSense: I wish I had this in Geography class
Jun 13

GSpreadsheet: JavaScript Helper for Google Spreadsheets

Google, JavaScript, Tech Add comments

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);
}

10 Responses to “GSpreadsheet: JavaScript Helper for Google Spreadsheets”

  1. shark12er Says:

    “enjoyed reading this ,thanks”

    http://www.mp4-converter.net/dvd-to-mp4/

  2. virginia Says:

    if one tries to overhaul two rabbits he can catch no one

  3. new york Says:

    virginia is a fairy

  4. Scott Says:

    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.

  5. Scott Says:

    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.

  6. Col Says:

    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

  7. Nicola Says:

    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…

  8. Scott Says:

    Nicola – thanks for the fix!

  9. Jeff Rule Says:

    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?

  10. Todd Says:

    You can do:

    key.replace(/-/g, “_”)

    to replace all dashes with underscores.

Leave a Reply

Spam is a pain, I am sorry to have to do this to you, but can you answer the question below?

Q: What are the first four letters in the word British?