How to parse JSON from Private Google Sheet

heath22

Hi. I just discovered this very nice library after growing frustrated with the aging and barely maintained Google Visualization API.

I'm trying to test out if I can load the JSON object from a Google Sheet to DataTables. The issue I have is that my spreadsheet is private. I am able to load this into Google's dataTable but now that I'm trying to replicate this with DataTables I'm having issues getting the JSON when I already have the response via google's gapi API.

What's the correct way to skip the url retrieval since I already have the response?

Here's how I do it with gapi API:

var params = {
            valueRenderOption: "FORMATTED_VALUE",
            spreadsheetId: 'yourspreadsheetID',
            range: 'ExampleSheet!A1:B', // Retrieve the values of "A:B".
        };
        var request = gapi.client.sheets.spreadsheets.values.get(params);
        request.then(function(response) {
            var values = response.result.values.map(function(e) {
                return [e[0], e[1], e[2]]
            }); 

And here's the attempt to load the gapi response into dataTables based on a public google sheet example.

      this is not needed in my case
     //var url = "https://spreadsheets.google.com/feeds/list/yourspradsheeturl/od6/public/values?alt=json";

       this is also not needed
      // $.getJSON(request, function(data) {
      //not sure this is re right way to do it
     data = JSON.stringify(response)
        var entry = data.feed.entry;

         $(entry).each(function(){

         $('#data1 tbody').append(
         '<tr><td>'+this.gsx$Customer.$t+'</td><td>'+ '<tr><td>'+this.gsx$Type.$t+'</td><td>');
         });

          $(document).ready(function() {

     var table =$('#data1').DataTable();

      });

     // });
      /*end parsing JSON */

kthorngren

What's the correct way to skip the url retrieval since I already have the response?

If you already have the response as a Javascript variable then you can use data to add the rows during Datatables initialization or rows.add() to add after Datatables has been initialized. This assumes that the data is in a structure supported by Datatables. Please see this doc:
https://datatables.net/manual/data/

Let us know if you have further questions.

Kevin

heath22

Kevin, Thank you.

It was as easy as:

$(document).ready(function() {

$('#data1').DataTable( {

        data: values
 } );
  });

But now I'm facing another issue. It appears that the table headers (row1) are in the data so I basically have a header that I manually created with:

 <table id="data1">
    <thead>
    <th>Customer</th>
...
   </thead>
    <tbody>
    </tbody>
  </table>

And the second "header" that's an actual row in the table. How do I ignore the header in values or how do I instruct dataTables to use the header from values instead of the ones I manually created?

kthorngren

Before initializing Datatables you can get the first element of the values array and build the columns. You can use something like Javascript's shift() method, for example: var headings = values.shift();.

Then use the to define the columns option. Use columns.title to set the thead titles.

Kevin

heath22

Kevin,

Sounds straightforward except the last step.
How do you properly define the columns and set the column's title?

I got the first element and spliced the headings from the values array:

var headings = values[0];
var values_with_no_headings = values.slice(1);

Now when I define the columns, I only get the first Column values back, no headings, just the values of the entire first column.

 $(document).ready(function() {

$('#data1').DataTable( {

        data: values_with_no_headings,
        columns: [headings]
 } );
  });

The example here in the documentation doesn't make sense for my case:

$('#example').dataTable( {
  "columns": [
    { "title": "My column title" },
    null,
    null,
    null,
    null
  ]
} );

Thank you.

kthorngren

See if this example helps:
http://live.datatables.net/huyexejo/1/edit

Kevin

heath22

Kevin,

This solves the column headings but now the rows are invisible. The correct number of total rows is displayed with pagination but the data in the rows is invisible.

html:

 <table id="data1" style="white-space:nowrap">
  </table>

javascript:

var columns = [];
var headings = values[0];
var values_with_no_headings = values.slice(1);

 for (var i in headings) {
          columns.push({data: headings[i], 
                    title: capitalizeFirstLetter(headings[i])});
  }
$(document).ready(function() {
$('#data1').DataTable( {

        data: values_with_no_headings,
        columns: columns

 } );
  });

kthorngren

Look for errors in your browser's console. Without seeing a test case with your specific data it would be hard to diagnose. Seems like the above code should work.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

kthorngren

I tried your code in this example:
http://live.datatables.net/gukuxebi/1/edit

Found the problem. It looks like your data is arrays not objects. The loop you have is creating both columns.data and columns.title options. You only want columns.title. If you had object based data then you would also use columns.data. More information can be found in the data document.

The example above has data: headings[i], removed and it works.

Kevin

heath22

Kevin, that did it!

Thank you so much!