How to grab Datatables data from a Google Spreadsheet
How to grab Datatables data from a Google Spreadsheet
Hi there.
I banged my head against a wall for a day or two, but I figured out how to dynamically get data into a Datatables object from a google doc, and I figured I would share this info with you all while it's fresh.
1. Google Spreadsheet set up:
First row should contain column descriptors.
Spreadsheet should be published as an RSS feed in LIST mode. (you can make it auto-republish on update if you like).
[ HTML of demo sheet for reference:
https://spreadsheets0.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE&single=true&gid=0&output=html ]
2. You'll get a URL that looks like this:
https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/basic?hl=en_US&alt=rss
Modify this URL by replacing
[code]/public/basic?hl=en_US&alt=rss[/code]
with
[code] /public/values?alt=json [/code]
Both the VALUES and ALT=JSON options are critically important in taking this feed and making it usable for datatables.
3. Initialize dataTables as follows:
[code]
$(document).ready(function() {
$('#example').dataTable( {
"bServerSide":false,
"bProcessing":true,
"sAjaxDataProp": "feed.entry",
"sAjaxSource": "https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json",
"aoColumns": [
{ "mDataProp": "gsx$carmake.$t" },
{ "mDataProp": "gsx$model.$t" },
{ "mDataProp": "gsx$year.$t" },
]
} );
} );
[/code]
IMPORTANT: for some reason the code highlighting is adding an a tag around my Ajaxurl. Ignore this, just plant the URL there.
The important bits described:
[code]"bServerSide":false,[/code]
Because we are simply grabbing the feed, no processing is happening at the server end besides spitting data back at us.
[code]"sAjaxDataProp": "feed.entry",[/code]
This ensures we are using the correct data property, else it will default to aaData and that's no use with this feed.
[code]"sAjaxSource": "https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json",
[/code]
points us to the Google doc URL. Again, ignore the screwy a tag that is being autocreated by the forum.
[code]"aoColumns": [
{ "mDataProp": "gsx$carmake.$t" },
{ "mDataProp": "gsx$model.$t" },
{ "mDataProp": "gsx$year.$t" },
][/code]
Grabs the correct pieces of data from the feed for use in datatables. the gsx$carmake, for example, is populated because carmake is the title of column1 in row1, these will be based on your spreadsheet and must be extracted from the json feed you will be be using.
The $gsxwhatever MUST terminate in .$t in order for the data to be grabbed correctly.
In the HTML section, initialize dataTables as per normal usage.
I hope this has been helpful to someone, somewhere.
-raza
I banged my head against a wall for a day or two, but I figured out how to dynamically get data into a Datatables object from a google doc, and I figured I would share this info with you all while it's fresh.
1. Google Spreadsheet set up:
First row should contain column descriptors.
Spreadsheet should be published as an RSS feed in LIST mode. (you can make it auto-republish on update if you like).
[ HTML of demo sheet for reference:
https://spreadsheets0.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE&single=true&gid=0&output=html ]
2. You'll get a URL that looks like this:
https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/basic?hl=en_US&alt=rss
Modify this URL by replacing
[code]/public/basic?hl=en_US&alt=rss[/code]
with
[code] /public/values?alt=json [/code]
Both the VALUES and ALT=JSON options are critically important in taking this feed and making it usable for datatables.
3. Initialize dataTables as follows:
[code]
$(document).ready(function() {
$('#example').dataTable( {
"bServerSide":false,
"bProcessing":true,
"sAjaxDataProp": "feed.entry",
"sAjaxSource": "https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json",
"aoColumns": [
{ "mDataProp": "gsx$carmake.$t" },
{ "mDataProp": "gsx$model.$t" },
{ "mDataProp": "gsx$year.$t" },
]
} );
} );
[/code]
IMPORTANT: for some reason the code highlighting is adding an a tag around my Ajaxurl. Ignore this, just plant the URL there.
The important bits described:
[code]"bServerSide":false,[/code]
Because we are simply grabbing the feed, no processing is happening at the server end besides spitting data back at us.
[code]"sAjaxDataProp": "feed.entry",[/code]
This ensures we are using the correct data property, else it will default to aaData and that's no use with this feed.
[code]"sAjaxSource": "https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json",
[/code]
points us to the Google doc URL. Again, ignore the screwy a tag that is being autocreated by the forum.
[code]"aoColumns": [
{ "mDataProp": "gsx$carmake.$t" },
{ "mDataProp": "gsx$model.$t" },
{ "mDataProp": "gsx$year.$t" },
][/code]
Grabs the correct pieces of data from the feed for use in datatables. the gsx$carmake, for example, is populated because carmake is the title of column1 in row1, these will be based on your spreadsheet and must be extracted from the json feed you will be be using.
The $gsxwhatever MUST terminate in .$t in order for the data to be grabbed correctly.
In the HTML section, initialize dataTables as per normal usage.
I hope this has been helpful to someone, somewhere.
-raza
This discussion has been closed.
Replies
That's awesome - thanks for posting this. Great use of mDataProp and sAjaxDataProp. I've added your post to the DataTables news feed :-)
Regards,
Allan
-raza
Unfortunately, it's not capturing the table header (first row of my Google spreadsheet). It captures it at some level, because it correctly grabs the data used by mDataProp to find the values in the JSON Google returns. But those headers are not displayed on the table. I have to explicitly drop TH items into the HTML to make headers appear. It'd be more elegant if those were dynamically grabbed, just like the table data is.
(Using version 1.9.0, testing with Firefox, Safari, and Chrome on Mac OS X Lion.)
I am trying to include a google spreadsheet into my website as you have described above, however the code you have provided is outputting nothing for me. I have everything included correctly. Is there a simpler solution at this point in time?
Al the best,
Laerte ljs2905 (@) gmail . com
Allan
I have a big database spreadsheet to keep all sales and I have others that query each seller from the database.
https://www.dropbox.com/s/xnqv9pij9h2qoa6/datatables_trouble.png
The problem: Because the query does the process and brings the results some time later, the results are shown in the datatables as #VALUE. It is shown after several refreshes.
Solutions I can think of:
1- Adding a delay before the results.
2- Making query in the datatables script itself. (where Col5 contains ...)
I'm waiting for your help about that.
If somebody have some debugging skills with IE it would be very appreciated.
Thanks
Anyway, you might want to consider trying to use this script which I've seen recently: https://github.com/chrislkeller/tabletop_to_datatables . If you do use it, let us know how you get on.
Also, if I may ask, how much is your user base that is still using IE7? I'm considering dropping IE6/7 support in DataTables you see.
Allan
Thank you for the link to Chris Keller repo but in browsershots.org service it seems doesn't work with ie :
http://browsershots.org/http://projects.chrislkeller.com/demos/tabletop_to_datatables/
I will test it tomorrow and I'll give you some feedback.
Our DataTables installation is used on an academic library page. Stats : Internet Explorer 10 (0,2%) 9 (8,1%) 8 (12,2%) 7 (0,4%) 6 (0%).
Laurent
It looks like my version of IE was choking on a console.log I left in on line 37 of the tabletop_feed.js script. My fault.
After commenting it out the table seems to render fine in IE8 and in IE7 mode in IE8:
http://projects.chrislkeller.com/demos/tabletop_to_datatables/
I don't have access to IE9 or IE10 to check if it works there, but let the forum know what you see?
Chris K.
However, I am struggling to get mine to work in IE, works in firefox and chrome.
http://live.datatables.net/uvohil/34/edit#javascript,html,live
anyone got any ideas?
anybody can tell me how can i show minimum 25 row by default in table.
please reply !
I am totally new to code so I may be missing something, but it happens that I am able to make the example work with my own Google spreadsheet in the "Datatable live site" but once i copy the code to a txt file, save, and open in my computer in Chrome, it only shows the Column headers but none of the Google data. The Javascript code, i am adding it inside the html, which again, works fine in the datatable live site, but not once i take it out of it. Any idea what may i be doing wrong?
What i am trying to ultimately do, is to have a webpage that pulls data from the spreadsheet but somehow can't take it out of the Data live site. May it have to do with my computer not having something installed?
thanks in advance,
Robert
Please excuse but I am an old geezer trying to keep the grey matter working, and am much in awe of you pros.
I am using modified code from the start of this post. I use the following in ssearch { "sSearch": "<?php echo $_GET['others']; ?> "}, to allow me to do a pre-search. Because the databases has got very slow to load, due to size, I have am now trying "tabletop" which is much faster, but I am unable to insert the search string into ssearch. I have tried various option of stripping the data from url but to no effect, any help would be helpful. The url of what I have at the moment is below (this is using the original version)
http://www.weymouthsands.co.uk/diary/Roll_of_Honour.htm
Colin
Here is the code I am using to strip url for use in "tabletop"
$.extend({
getUrlVars: function(){
var vars = [], hash;
var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');
for(var i = 0; i < hashes.length; i++)
{
hash = hashes[i].split('=');
vars.push(hash[0]);
vars[hash[0]] = hash[1];
}
return vars;
},
getUrlVar: function(name){
return $.getUrlVars()[name];
}
});
and this is the line for ssearch
"oSearch": {"sSearch": $.getUrlVar('name')},
I've taken a look at/tested this, tabletop, some tabletop addons, and written a processing script myself in PHP. What I'm wondering is, why would you want to do this in JS? With my PHP test the table gets built even if the user has no JS, and the DataTables features get added when he does. I would also say it's easier to write a caching function in PHP than in JS (so we don't bother Google every pageload) and we keep the magic (source code) away from visitors with bad intentions. Hope someone can shed some light on this.