JQuery GSpreadsheet Datasource
A jquery plugin for interactic with data stored in a google doc spreadsheet. The google doc spreadsheet has to be published and exported as rss. The plugin performs an AJAX request for retrieving the RSS file: in order to avoid cross-domain script issues you may need to have a local proxy to forward the request to the server serving the RSS file.
How to use it
- Download the plugin here.
- Create a Google docs spreadsheet document, then click on publish and export as rss.
- Copy the link to the rss resource. It should be something like this: https://spreadsheets.google.com/feeds/list/0An1B2ThQBxFRdEJ5TWNyOW1PV2h2TXY2WFhzWDNfRnc/od6/public/basic?alt=rss.
- Reference JQuery and the gspreadsheetdatasource plugin, and add some code (the callback function should do something with your data)
// clean up
$('#webcomics').html("");
// define your fields
var fields = new Array();
fields["1"] = "webcomic";
fields["2"] = "author";
fields["3"] = "url";
var callback = function(records){
for (var record_key in records) {
var record = records[record_key];
//Print your datasource here
var webcomic = record["webcomic"];
var author = record["author"];
var url = record["url"];
$('<h4></h4>').html(webcomic).appendTo('#webcomics');
$('').html(author).appendTo('#webcomics');
$('<a></a>').html(url).attr("href", url).appendTo('#webcomics');
}
}
// call the function
var records = $().gspreadsheetsdatasource("./proxy.php?contentType=application/xml&url=https%3A%2F%2Fspreadsheets.google.com%2Ffeeds%2Flist%2F0An1B2ThQBxFRdEJ5TWNyOW1PV2h2TXY2WFhzWDNfRnc%2Fod6%2Fpublic%2Fbasic%3Falt%3Drss", fields, callback);
};
Live Example
The example shows how to load a datasource and displaying it in a page.
click here to trigger the live example.Webcomics I enjoy reading:
Proxy
It is not possible to retrieve the rss content directly from google servers via an AJAX call. A local proxy provides the functionality of performing the request and returning the response as if the requests were local. The code for a simple proxy in php is the following.
if ($contentType==null)
{
$contstyle="border:1px solid #999999;padding:10px;margin:10px;"entType="text/html";
}
header('Content-type: '.$contentType);
$url = $_REQUEST['url'];
$url = urldecode($url);
$handle = fopen($url, "r");
if ($handle) {
whilestyle="border:1px solid #999999;padding:10px;margin:10px;" (!feof($handle)) {
$buffer = fgets($handle, 4096);
echo $buffer;
}
fclose($handle);
}
Implementation notes
- The first column of the spreadsheet is exported in rss as title and not consider in the plugin. If one is interested to the content of the first column then he or she should add an empty column as first column.
- Field name are searched inside the description element of the rss item. Everything will work if the fieldname is not repeated in the text of any field, that's why it would be great to have univoque field names. A good practice is to use field name starting and ending with an underscore (like '_author_').