How to grab Datatables data from a Google Spreadsheet

rahmad

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

allan

Hi raza,

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

rahmad

Thank you, Allan, for creating such a powerful and flexible tool. Mad props.

-raza

jonathaneunice

It's great to have a direct grab from Google Spreadsheets!

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.)

brandon93s

Thanks for posting this. I realize I'm reviving an ancient thread, but could use the help!

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?

Laertejs

Hi I want to have spreadsheet data returned to user based on sheet calculations and have not clue how to do this. Can anyone do this for me?


Al the best,


Laerte ljs2905 (@) gmail . com

mossimac

My spreadsheet has 9000 rows and the rss is only pulling 118 rows. Any ideas?

cihat74

It would be really great to add a plain example or add the entire html page code for somebody totally dummy about how to create html like me. Could you please add this?

allan

The code above is a complete example. If I simply dump it into the DataTables live site, you can see it running: http://live.datatables.net/uvohil/edit#javascript,html .

Allan

cihat74

I succeeded. Thank you very much @allan.

cihat74

A DEVELOPMENT ISSUE:

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.

cihat74

I resolved the above problem with adding another page pulling the data from the first page with arrayformula. Therefore it became faster to load. But still it could be great to query with a containing word.

bibliogum

Unfortunately this very useful feature doesn't work in Internet Explorer 7 and above. As mentioned here : http://datatables.net/forums/discussion/6654/ie8-problems-
If somebody have some debugging skills with IE it would be very appreciated.
Thanks

allan

Any indication at all as to why it doesn't work?

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

bibliogum

I don't know why... Here some screenshots from browsershots.org : http://browsershots.org/http://live.datatables.net/uvohil/

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

bibliogum

Finally Internet Explorer does not display the dataTables from Google Spreadsheet with rahmad method neither with Chris Keller method.

chrislkeller

Thanks for the link to the tabletop_to_datatables/repo and even more thanks for creating the DataTables library.

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.

Bama5150

Chris, yours worked for me in IE9.

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?

Bama5150

disregard above post. It is working fine....

yash3131

hi,
anybody can tell me how can i show minimum 25 row by default in table.

please reply !

saldivarr

Hello,
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

colgreen

Hi

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

colgreen

Senior moment


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')},

Brugman

Thanks for making this thread Rahmad, without it I had never realized you can get an rss/json feed for any Google doc.

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.