proper format and commands for Google Sheet - Json fed Datatables

user_6589922 0
Viewed 191

Trying to feed a Datatables table a set of json data straight from Google Sheet I got this kind of error in the first place:

DataTables warning: table id=example - Requested unknown parameter 'name' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

Here is my json vs the one provided in their example which of course has a different format

{
  "range": "json!A1:Y1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "name",
      "position",
      "salary",
      "start_date",
      "office",
      "extn"
    ],
    [
      "Unity Butler",
      "prova",
      "$85,675",
      "2009/12/09",
      "San Francisco",
      "5384"
    ]
  ]
}

{
  "data": [
    {
      "id": "1",
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Edinburgh",
      "extn": "5421"
    },
    {
      "id": "2",
      "name": "Garrett Winters",
      "position": "Accountant",
      "salary": "$170,750",
      "start_date": "2011/07/25",
      "office": "Tokyo",
      "extn": "8422"
    },
    {...}
]
}

I understood different formats need different treatments and I was curious to know how. The answer provided by @andrewjames which I have accepted explains both the nature of the different formats and how to treat them.
Basically all it took was removing the "columns" parameter from the ajax option, as you can see from this jsfiddle where you can find the complete working code (along with libraries as well).

UPDATE: supplementary task

Now, suppose you want to arrange some extra information into a set of toggle hide-show child rows.

As long as the data format respects the original one provided by the example I can easily do that: I can even grab data from a mysql db via a .php file and arrange it into json! jsfiddle

 <?php
$con=mysqli_connect("","","","");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$out = array();
 
if ($result = mysqli_query($con, "SELECT * FROM your_table")) {
  $out = $result->fetch_all(MYSQLI_ASSOC);
}
 
echo "{ \"data\": ", json_encode( $out ), "}";

mysqli_close($con);
?>

Problems start when I try to pull data from my usual GSheet-fed json array: how can I conjugate child rows toggle capabilities and a GSheet typically formatted json file ?

datatables google-sheets-api google-sheets json javascript
1 Answers

The JSON data you show from the DataTables example is an array of objects:

[ {...}, {...}, ... ]

The JSON data you have created is an array of arrays:

[ [...], [...], ... ]

DataTables can consume both structures as its data source - but it has to do so in different ways..

For the array of objects, each object represents a (potential) row of your DataTables data - and each value in the row consists of a name and a value - for example:

"salary": "$320,800"

And that is why the DataTable uses column definitions like this:

{ "data": "salary" }

That means: for this cell's data, use the value which has a name of "salary".

But in your case there are no names - there are only values:

"$85,675"

So, you can't use { "data": "salary" }, because that name ("salary") does not exist in your JSON.

Furthermore, your structure contains the column headings as well as the row data in its first array:

[
  "name",
  "position",
  "salary",
  "start_date",
  "office",
  "extn"
],

To work around these differences you have 2 basic options:

  1. Re-arrange your JSON so it matches the "array of objects" structure.

  2. Look at one of the relevant DataTables examples which is based on an array of arrays - for example Ajax data source (arrays).

Option (2) is easier - closer to your starting point.

But in either case, you still need to handle the column headings, since these are separate from the column body rows, and need to be defined separately.

Currently, your first row (which looks like column headings) would end up being displayed as the first row of data - not what you want.

One way to do this is to define your <html> table to contain the headings already (hard-coded):

<table id="example" class="display" style="width:100%">
    <thead>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Extn.</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </thead>
</table>

Alternatively, you can use this:

<table id="example" class="display" style="width:100%">
</table>

And define your column headings in the Datatable definition, using the DataTables title keyword:

<script>
 
$(document).ready(function() {
    $('#example').DataTable( {
        columns: [
            { title: "Name" },
            { title: "Position" },
            { title: "Office" },
            { title: "Extn." },
            { title: "Start date" },
            { title: "Salary" }
        ]
    } );
} );

</script>

And when you do this, you need to remove the headings array from your JSON:

{
  "range": "json!A1:Y1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "Unity Butler",
      "prova",
      "$85,675",
      "2009/12/09",
      "San Francisco",
      "5384"
    ],
    [
        // more array data here...
    ], ...
  ]
}

Now for the array of arrays, there is no need to specify data values for each column. Because each row is a plain array, DataTables automatically places the first value in the first cell of the row, the second value in the second cell, and so on...

Final note: In your example you used this:

dataSrc: 'values' // values instead of data in this case

This is good. It tells DataTables to look for your array of arrays in a top-level field called values.

Update

For the child rows example, you will have a much easier time if you stop using an array of arrays and start using an array of objects. That way you can use the object names to explicitly choose which values belong to the parent row and which belong to the child row.

So, if you have source data in a sheet, with headings:

heading_1 heading_2 heading_3
value 1 value 2 value 3
value 4 value 5 value 6

Then you need to convert that data from this structure:

[ 
  [ "value 1", "value 2", "value 3" ],
  [ "value 4", "value 5", "value 6" ]
]

{ "data": [ 
  { "heading_1": "value 1", "heading_2": "value 2", "heading_3": "value 3" },
  { "heading_1": "value 4", "heading_2": "value 5", "heading_3": "value 6" }
] }

JavaScript can do this. Maybe you can try that yourself? If you get stuck you can ask a specific question.

Once you have the array of objects, you can follow the example much more easily. Now you can choose which field names to use in the parent row and which to use in the child row.

user_125673650
Related