How to properly format a JSON object into a dataTable for Google Visualization Chart

Question

I'm trying to create a simple chart from a JSON Object.

The Line Chart will not accept a JSON directly so I need to convert it to a datable but it looks like every situation is unique depending on how your JSON is setup.

What would be the correct way to pass this as a dataTable if the JSON looks like this when I convert it to a string:

    var data = JSON.stringify(myJSONObject);
    var dataTableData = new google.visualization.DataTable(data); //throws error
    //var dataTableData = new google.visualization.DataTable(myJSONObject); //throws error
    //var dataTableData = myJSONObject //throws error
    var chart = new google.charts.Line(document.getElementById('line_top_x'));
    chart.draw(dataTableData, google.charts.Line.convertOptions(options));

Whatever I do, I get "Error: First row is not an array"

JSON myJSONObject:

(13) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]
0: {month: "February", column1: "1", column2: "2", column3: "3", column4: "4", …}
1: {month: "March", column1: "1", column2: "2", column3: "3", column4: "4", …}
2: {month: "April", column1: "1", column2: "2", column3: "3", column4: "4", …}
3: {month: "May", column1: "1", column2: "2", column3: "3", column4: "4", …}
4: {month: "June", column1: "1", column2: "2", column3: "3", column4: "4", …}
5: {month: "July", column1: "1", column2: "2", column3: "3", column4: "4", …}
6: {month: "August", column1: "1", column2: "2", column3: "3", column4: "4", …}
7: {month: "September", column1: "1", column2: "2", column3: "3", column4: "4", …}
8: {month: "October", column1: "1", column2: "2", column3: "3", column4: "4", …}
9: {month: "November", column1: "1", column2: "2", column3: "3", column4: "4", …}
10: {month: "December", column1: "1", column2: "2", column3: "3", column4: "4", …}
11: {month: "Total", column1: "1", column2: "2", column3: "3", column4: "4", …}
12: {month: undefined, column1: undefined, column2: undefined, column3: undefined, column4: undefined, …}
length: 13
__proto__: Array(0)

String of myJSONObject (console.log(data)):

[{"month":"February","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"March","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"April","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"May","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"June","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"July","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"August","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"September","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"October","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"November","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"December","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"Total","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{}]

In this case `[....]` = Array Literal, `{...}` = Object Literal. If I recall a DataTable class is comprised of arrays. — zer00ne, Dec 06 '19 at 15:33
Please post the **code** of the JSON, not an image of the JSON. — zer00ne, Dec 06 '19 at 15:42

score 2 · Accepted Answer · answered Dec 06 '19 at 21:13

need to convert each object to an array.

first, create a blank data table...

  // create blank data table
  var data = new google.visualization.DataTable();

then add the columns and rows...

  // parse json
  jsonData.forEach(function (jsonRow, indexRow) {
    // add columns
    if (indexRow === 0) {
      for (var column in jsonRow) {
        if (column === 'month') {
          data.addColumn('string', column);
        } else {
          data.addColumn('number', column);
        }
      }
    }

    // add row
    var dataRow = [];
    for (var column in jsonRow) {
      if (column === 'month') {
        dataRow.push(jsonRow[column]);
      } else {
        // convert string to number
        dataRow.push(parseFloat(jsonRow[column]));
      }
    }
    if (dataRow.length > 0) {
      data.addRow(dataRow);
    }
  });

see following working snippet...

google.charts.load('current', {
  packages: ['line']
}).then(function () {
  var jsonData = [{"month":"February","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"March","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"April","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"May","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"June","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"July","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"August","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"September","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"October","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"November","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"December","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{"month":"Total","column1":"1","column2":"2","column3":"3","column4":"4","column5":"5"},{}];

  // create blank data table
  var data = new google.visualization.DataTable();

  // parse json
  jsonData.forEach(function (jsonRow, indexRow) {
    // add columns
    if (indexRow === 0) {
      for (var column in jsonRow) {
        if (column === 'month') {
          data.addColumn('string', column);
        } else {
          data.addColumn('number', column);
        }
      }
    }

    // add row
    var dataRow = [];
    for (var column in jsonRow) {
      if (column === 'month') {
        dataRow.push(jsonRow[column]);
      } else {
        // convert string to number
        dataRow.push(parseFloat(jsonRow[column]));
      }
    }
    if (dataRow.length > 0) {
      data.addRow(dataRow);
    }
  });

  var options = {
    chart: {
      title: 'test'
    }
  };
  var chart = new google.charts.Line(document.getElementById('line_top_x'));
  chart.draw(data, google.charts.Line.convertOptions(options));
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="line_top_x"></div>

note: if you would like to create the data table, directly from json...

  // create data table from json
  var data = new google.visualization.DataTable(jsonData);

then the json must be in a specific format, as found here...
Format of the Constructor's JavaScript Literal data Parameter

{
  cols: [{id: 'A', label: 'NEW A', type: 'string'},
         {id: 'B', label: 'B-label', type: 'number'},
         {id: 'C', label: 'C-label', type: 'date'}
  ],
  rows: [{c:[{v: 'a'},
             {v: 1.0, f: 'One'},
             {v: new Date(2008, 1, 28, 0, 31, 26), f: '2/28/08 12:31 AM'}
        ]},
         {c:[{v: 'b'},
             {v: 2.0, f: 'Two'},
             {v: new Date(2008, 2, 30, 0, 31, 26), f: '3/30/08 12:31 AM'}
        ]},
         {c:[{v: 'c'},
             {v: 3.0, f: 'Three'},
             {v: new Date(2008, 3, 30, 0, 31, 26), f: '4/30/08 12:31 AM'}
        ]}
  ],
  p: {foo: 'hello', bar: 'world!'}
}

How to properly format a JSON object into a dataTable for Google Visualization Chart

1 Answers1