Google Visualization - On event table sort, keep specific row as first visible record?

Question

When a user clicks the header to sort I want the row labeled "Grand Total" to stay as the first row visible.

I can catch the table sort event as follows:

google.visualization.events.addListener(table.getChart(), 'sort', function() {
      console.log('User clicked to sort.');
};

But I don't know what to do in order to move the total row back to the first record.

How can I achieve this? I appreciate your hints.

Working Example: (starter code)

google.charts.load('current', {
  'packages': ['corechart', 'table', 'gauge', 'controls', 'charteditor']
});

$(document).ready(function() {
  renderChart_onPageLoad();
});

function renderChart_onPageLoad() {
  google.charts.setOnLoadCallback(function() {
    drawDashboard();
  });
}

function drawDashboard() {

  var data = google.visualization.arrayToDataTable([
    ['Name', 'RoolNumber', 'Gender', 'Age', 'Donuts eaten'],
    ['Michael', 1, 'Male', 12, 5],
    ['Elisa', 2, 'Female', 20, 7],
    ['Robert', 3, 'Male', 7, 3],
    ['John', 4, 'Male', 54, 2],
    ['Jessica', 5, 'Female', 22, 6],
    ['Aaron', 6, 'Male', 3, 1],
    ['Margareth', 7, 'Female', 42, 8],
    ['Miranda', 8, 'Female', 33, 6]
  ]);

  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard'));

  var categoryPicker = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'categoryPicker',
    options: {
      filterColumnLabel: 'Gender',
      ui: {
        labelStacking: 'vertical',
        allowTyping: false,
        allowMultiple: false
      }
    }
  });

  var proxyTable = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'proxyTable',
    options: {
      width: '500px'
    }
  });

  var table = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'table',
    options: {
      width: '500px'
    }
  });

  dashboard.bind([categoryPicker], [proxyTable]);
  dashboard.draw(data);

  google.visualization.events.addListener(dashboard, 'ready', function() {
    redrawChart();
  });

  function redrawChart() {

    var sourceData = proxyTable.getDataTable();
    var dataResults = sourceData.toDataTable().clone();

    var group = google.visualization.data.group(sourceData, [{
      // we need a key column to group on, but since we want all rows grouped into 1, 
      // then it needs a constant value
      column: 0,
      type: 'number',
      modifier: function() {
        return 1;
      }
    }], [{
      column: 1,
      id: 'SumRool',
      label: 'SumRool',
      type: 'number',
      aggregation: google.visualization.data.sum
    }, {
      column: 3,
      id: 'SumAge',
      label: 'SumAge',
      type: 'number',
      aggregation: google.visualization.data.sum
    }, {
      // get the average age
      column: 4,
      id: 'SumEaten',
      label: 'SumEaten',
      type: 'number',
      aggregation: google.visualization.data.sum
    }]);

    dataResults.insertRows(0, [
      ['Grand Total', group.getValue(0, 1), null, group.getValue(0, 2), group.getValue(0, 3)],
    ]);

    //Set dataTable
    table.setDataTable(dataResults);
    table.draw();

    // table sort event
    google.visualization.events.addListener(table.getChart(), 'sort', function() {
      console.log('User clicked header to sort.');

      //When a user clicks the header and resorts the table I want the row labeled "Grand Total" to stay as the first row visible.


    });
  }
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>

<div id="dashboard">
  <div id="categoryPicker"></div><br />
  <div id="proxyTable" style='display:none;'></div>
  <div id="table"></div><br /><br />
</div>

Thank you for looking so close to the end of day. We can continue tomorrow. Have a good evening... — cmill, Oct 23 '19 at 21:23

WhiteHat · Accepted Answer · 2019-11-20T12:53:50.937

in order to accomplish, we will need to manually control the sort order.

first, set the sort option on the table chart.

var table = new google.visualization.ChartWrapper({
  chartType: 'Table',
  containerId: 'table',
  options: {
    sort: 'event',  // <-- set sort to 'event'
    width: '500px'
  }
});

next, we need to assign event listeners before the chart is drawn.
first, we must wait for the wrapper to be ready,
then assign the sort event to the chart.
but we only want to do this one time, hence --> addOneTimeListener

google.visualization.events.addOneTimeListener(table, 'ready', function() {
  google.visualization.events.addListener(table.getChart(), 'sort', function(sender) {

the sort event receives properties for which column and direction the sort occurred. (sender)
we'll use these properties to get the sorted rows from the data table.
afterwards, we find the index of the grand total row, remove it from the sort order,
then add it back as the first index.

// table sort event
google.visualization.events.addOneTimeListener(table, 'ready', function() {
  google.visualization.events.addListener(table.getChart(), 'sort', function(sender) {
    // sort data table according to sort properties
    var tableData = table.getDataTable();
    var sortIndexes = tableData.getSortedRows({column: sender.column, desc: !sender.ascending});

    // find grand total row
    var grandTotal = tableData.getFilteredRows([{
      column: 0,
      value: 'Grand Total'
    }]);
    if (grandTotal.length > 0) {
      // find grand total in sort
      var grandTotalSort = sortIndexes.indexOf(grandTotal[0]);

      // remove grand total from sort
      sortIndexes.splice(grandTotalSort, 1);

      // add grand total as first index
      sortIndexes.unshift(grandTotal[0]);

      // set table sort arrow
      table.setOption('sortAscending', sender.ascending);
      table.setOption('sortColumn', sender.column);

      // set table view
      table.setView({rows: sortIndexes});

      // re-draw table
      table.draw();
    }
  });
});

see following working snippet...

google.charts.load('current', {
  'packages': ['corechart', 'table', 'gauge', 'controls', 'charteditor']
});

$(document).ready(function() {
  renderChart_onPageLoad();
});

function renderChart_onPageLoad() {
  google.charts.setOnLoadCallback(function() {
    drawDashboard();
  });
}

function drawDashboard() {
  var data = google.visualization.arrayToDataTable([
    ['Name', 'RoolNumber', 'Gender', 'Age', 'Donuts eaten'],
    ['Michael', 1, 'Male', 12, 5],
    ['Elisa', 2, 'Female', 20, 7],
    ['Robert', 3, 'Male', 7, 3],
    ['John', 4, 'Male', 54, 2],
    ['Jessica', 5, 'Female', 22, 6],
    ['Aaron', 6, 'Male', 3, 1],
    ['Margareth', 7, 'Female', 42, 8],
    ['Miranda', 8, 'Female', 33, 6]
  ]);

  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard'));

  var categoryPicker = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'categoryPicker',
    options: {
      filterColumnLabel: 'Gender',
      ui: {
        labelStacking: 'vertical',
        allowTyping: false,
        allowMultiple: false
      }
    }
  });

  var proxyTable = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'proxyTable',
    options: {
      width: '500px'
    }
  });

  var table = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'table',
    options: {
      sort: 'event',
      width: '500px'
    }
  });

 // table sort event
 // Moved this listener to main drawDashboard() because having this in redrawChart() was adding a new listener every time someone filtered categoryPicker.
    google.visualization.events.addOneTimeListener(table, 'ready', function() {
      google.visualization.events.addListener(table.getChart(), 'sort', function(sender) {
        // sort data table according to sort properties
        var tableData = table.getDataTable();
        var sortIndexes = tableData.getSortedRows({column: sender.column, desc: !sender.ascending});

        // find grand total row
        var grandTotal = tableData.getFilteredRows([{
          column: 0,
          value: 'Grand Total'
        }]);
        if (grandTotal.length > 0) {
          // find grand total in sort
          var grandTotalSort = sortIndexes.indexOf(grandTotal[0]);

          // remove grand total from sort
          sortIndexes.splice(grandTotalSort, 1);

          // add grand total as first index
          sortIndexes.unshift(grandTotal[0]);

          // set table sort arrow
          table.setOption('sortAscending', sender.ascending);
          table.setOption('sortColumn', sender.column);

          // set table view
          table.setView({rows: sortIndexes});

          // re-draw table
          table.draw();
        }
      });
    });

  dashboard.bind([categoryPicker], [proxyTable]);
  dashboard.draw(data);

  google.visualization.events.addListener(dashboard, 'ready', function() {
    redrawChart();
  });

  function redrawChart() {
    var sourceData = proxyTable.getDataTable();
    var dataResults = sourceData.toDataTable().clone();
 
    var group = google.visualization.data.group(sourceData, [{
      // we need a key column to group on, but since we want all rows grouped into 1,
      // then it needs a constant value
      column: 0,
      type: 'number',
      modifier: function() {
        return 1;
      }
    }], [{
      column: 1,
      id: 'SumRool',
      label: 'SumRool',
      type: 'number',
      aggregation: google.visualization.data.sum
    }, {
      column: 3,
      id: 'SumAge',
      label: 'SumAge',
      type: 'number',
      aggregation: google.visualization.data.sum
    }, {
      // get the average age
      column: 4,
      id: 'SumEaten',
      label: 'SumEaten',
      type: 'number',
      aggregation: google.visualization.data.sum
    }]);

    dataResults.insertRows(0, [
      ['Grand Total', group.getValue(0, 1), null, group.getValue(0, 2), group.getValue(0, 3)],
    ]);
    
   //Reset view to clear any user sorting initiated by event listener table.getChart(), 'sort'
    table.setView(null);   
   
    //Set dataTable
    table.setDataTable(dataResults);
    table.draw();
  }
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>

<div id="dashboard">
  <div id="categoryPicker"></div><br />
  <div id="proxyTable" style='display:none;'></div>
  <div id="table"></div><br /><br />
</div>

Would you please execute the following steps to replicate an error. 1. Run the snippet. 2. Click a column 1 to sort. 3. Filter for female. Can you explain the resulting error? I have been unable to resolve it myself. I can get to `table.draw();` and have gotten stuck. The error message is `Invalid row index 5. Should be in the range [0-4].` Thanks again. — cmill, Nov 19 '19 at 15:49
I think I figured it out. The error was coming from the fact that the `setView()` was not being reset in the `redrawChart()` function. You may want to review my edit in the working snippet. Someone rejected it without looking that it corrected the error described in my previous comment. — cmill, Nov 19 '19 at 20:51
Is this the proper way to resolve the error? `table.setView(null)` — cmill, Nov 19 '19 at 21:01
yes, that is correct, thanks. glad you were able to resolve. I've made the change you suggested... — WhiteHat, Nov 20 '19 at 12:54

Google Visualization - On event table sort, keep specific row as first visible record?

1 Answers1

Linked