How to construct url when asking data from Google spreadsheet

hoekbrwr

I used this post https://datatables.net/forums/discussion/25948/datatables-not-working#Comment_71339 in my website and it functions as expected.
Now I have to construct a url for my own datatable. How do I have to publish the spreadsheet and how would I have to change the last part as shown in the post?

My link looks like this https://docs.google.com/spreadsheets/d/<spreadsheet-id/pubhtml?gid=885578005&single=true. So this is a website link and I reformed it like this: https://docs.google.com/spreadsheets/d/<spreadsheet-id/values?gid=885578005&alt=json

In the mentioned post I understand this has been published as RSS feed, but at the moment this is not an option in Google spreadsheet anymore!

The console starts complaining about access control header not being present like this:
https://docs.google.com/spreadsheets/<spreadsheet-id/values?gid=885578005&alt=json&_=1448710659312. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://ndo.dev' is therefore not allowed access. The response had HTTP status code 404. When I publish it should be public!

Anyone trying to do this recently?

hoekbrwr

Sorry the blog logic change my url at the point where I used ">"!

hoekbrwr

OK I took Tabletop.js to my help and figured it all out how to get data from Google spreadsheets. The solution looks like this:

jQuery(document).ready(function(){
        var table = jQuery('#deelnemers');
        var public_spreadsheet_url = 'https://docs.google.com/spreadsheets/d/key/pubhtml';
        Tabletop.init({ key: public_spreadsheet_url,
                             callback: showInfo,
                             simpleSheet: true,
                             debug: false });
        function showInfo(data) {
            table.DataTable({
                data: data,
                paging: false,
                scrollY: 600,
                columns: [
                    {title: 'SN', 'sTitle': 'Start#', 'sClass': 'center', sWidth: '5%'},
                    {title: 'Naam Heer', 'sTitle': 'Naam Heer', 'sClass': 'left', sWidth: '35%'},
                    {title: 'Naam Dame', 'sTitle': 'Naam Dame', 'sClass': 'left', sWidth: '35%'},
                    {title: 'Klassen', 'sTitle': 'Klassen', 'sClass': 'center', sWidth: '15%'},
                    {title: 'Leraar', 'sTitle': 'Leraar', 'sClass': 'center', sWidth: '10%'},
                 ],
                 "order": [[0, "asc"]]
            });
            //
        }
}

Just fill in the complete url you get when you publish your sheet in Google! Does not matter if you use the complete url or only fill in the key value of your sheet, tabletop.js gets the right spreadsheet.

allan

Superb - thanks for posting back with your solution!

Allan

hoekbrwr

At least this is a method to get Google Spreadsheet data, but I have the feeling it should be possible to do that with DataTables! Than I can leave out a whole piece of JS. With what you see can you show how to do that? From the huge number of possibilities I did not find a clear example to do the same as in Tabletop!

allan

Can you get the data back from Google in JSON (it looks like it probably is)? If so, what is that format? Also, if there is no Access-Control-Allow-Origin HTTP header, then you might need to use a proxy at the server-side to get the data.

Allan

hoekbrwr

I get the data from Tabletop in the form of an array of Objects and the Objects are arrays of named strings like this first object:
0: Object
C: "."
F: "✓"
Klassen: "S55M/SOB"
Leraar: "BRE"
NaamDame: "Wilma Vierhout-Rooswinkel"
NaamHeer: "Fred Vierhout"
Opmerking: "15"
SN: "1"

That is not the problem! The table does not except the data elements in the <td>'s. I see the info "1 to 94 from 94 items". I have no idea how to get the data in JSON format, I am lost in the documentation!

hoekbrwr

Hooray I found it somewhere in the documentation. I just needed to change the columns option array title: into data: to let the data appear. Leaves the question: Can I do it without Tabletop too? But now I have result and I can go on with the sideline things of it to make it nicer!

allan

Are you able to show use the JSON data that Google returns? DataTables will work with most JSON data structures.

Thanks,
Allan

hoekbrwr

So I am back again to this problem(other things to attend to).
The solution really works and Tabletop returns the data in an array of Objects. I am not an expert in IT terms, but maybe you call that json?
So I use another peace of JS to get those data and are interested if I could do without Tabletop.js completely and get the job done with DataTables only!

allan

It probably is, but I'd need to know what the structure of the JSON is that Google spreadsheet gives. You should be able to see that int he Network requests panel in your browser's developer tools.

Allan

hoekbrwr

The array [Object,....] looks like this(from Tabletop):
0: Object
Afkorting: "BAK"
Dansleraar/trainer: "Bakker, dhr. P."
Formlijst: "0;Bakker, dhr. P., Almere;Bakker, dhr. P., Almere"
Plaatsnaam: "Almere"
Provincie: "Flevoland"
Website: " "
Websiteadres: ""
emailadres1: "cyntiakolijn@metro.com"
emailadres1actief: "cyntiakolijn@metro.com"
emailadres1test: "BAK@mailinator.com"
emailadres2: ""
ledendropdownlijst: "Bakker, dhr. P., Almere"
proto: Object
.
.
.
.

I took it from the console. This looks pretty straightforward! Can I get this format also from DataTable() somehow?

allan

You said you wanted to do it without TableTop. So the information showing what TableTop gives isn't going to help in this case to be honest.

As I say, we need to know what the JSON structure is that Google gives when you request that data from Google.

Allan

hoekbrwr

I don't know how to get data from Google spreadsheet with DataTable().. That is what I am trying to find out. TableTop got the info from Google spreadsheet indeed! That data works fine for me, but it seems double up to use Tabletop when DataTable can do the same job. I only really don't know how to code that in DataTable()

allan

I will tell you how it can be used with DataTables, but I need to know what the data structure is first! I'm not sure how much clearer I can state that I'm afraid. There is some kind of JSON data being loaded form Google - I need to know what the data is so we can then tell DataTables what that structure is and how to display it.

Allan

hoekbrwr

This starts to be a dog-tail running around.
I have a few Google spreadsheets to read. Till now only one table at a time and they look all alike with a 1 header row and a few hundred data rows. Nothing special!
Tabletop just gives me an array of objects in the form
{Object, Object, ...} and each Object like
{ columnname1: "data",
columnname2: "data",
.....}
How to get those data with DataTable() settings?

allan

I've just done a bit of digging via Google since I think we are going around in circles. This is a Google spreadsheet via the JSON API.

Looking at it it would probably need some kind of transform in order to be displayed in a DataTables since it isn't a simple key / value set of values. So you could do it without TableTop, but you'd still need to do a little processing.

Allan