How to update the underlying google datatable of a google visualization dashboard and refresh the dashboard view?

Question

I created a google visualization dashboard using the Google HTML Service. The underlying datatable (jjdt) is joined from several data tables like jadt2,fa.

jjdt = google.visualization.data.join(jadt2,fa,'left',[[0,0]],[1,2,3,4,5,6,7,8,9,10,11],[2,3]);   

And then I created a chart wrapper (finalTable) to show the table in the dashboard.

    finalTable = new google.visualization.ChartWrapper({
    'chartType': 'Table',
    'containerId': 'table-div',
    'options': {
      'showRowNumber': false, 
      'width': '100%', 
      'height': '100%', 
      'allowHtml': true, 
      'pageSize': 20
    }
  });

dashboard.bind(wwPicker, [finalTable]);
dashboard.draw(jjdt);

These code worked OK.

Now what I want to do is to allow a click of button to set certain cells in the underlying table (jjdt) to new values and get it reflected on the dashboard. But that is not as simple as I thought it would be.

I tried 2 things:

1) Using javascript to update the jjdt table for the corresponding cell (x,y) and call the finalTable.draw();

    jjdt.setCell(x,y,'New Value');
    finalTable.draw();

But this has no effect and no error message.

2) I think maybe the chart wrapper created a copy of datatable. So I try to get that datatable back:

 var mydt = finalTable.getDataTable();
 mydt.setCell(x,y,'New Value');
 finalTable.draw();

I thought this is correct way described in the documentation https://developers.google.com/chart/interactive/docs/reference#methods_4

However, I got an error message saying setCell is not a function of mydt even though I can call mydt.getValue(). Seems like my datatable is "somehow" read-only? But it is a DataTable not a DataView.. Why is that?

Answer 1

I tried PaulH's suggestion but it still does not work.

But after a bit further digging, I finally found the solution. The problem is that the way I called SetCell does not update the "formatted value" of the cell. That's why even thought I updated the table cell "value", its format stays the same as before.

The correct way to do it is:

jjdt.setCell(x,y,'New Value','New Display Value');

And this way only the "New Display Value" will show up in the table chart. But the chart will sort based on "New Value".

Also, to update the view, it seems either calling

dashboard.draw(jjdt)

or

finalTable.draw()

will have the same effect. So I went with just updating the table with finalTable.draw().

Hope this helps others.