Applying Google Visualization CategoryFilter to Sp

问题:

I have created a Table Chart according to Google's Visualization API and published the web app here. The data source is a Google Spreadsheet with Google Form responses. A couple columns contain a list of comma separated values, which are responses of the form's Checklist items. I have applied a "CategoryFilter" to these columns. However, instead of individualizing each comma separated value it treats it as one value.

Image of CSV Values not Separated

I would like for the filter to separate the values based on the comma and then stack them in a dropdown list. Is there a way to do this?

I have tried creating the values according to the API for the values for the "Grade Level(s)" filter, but when I choose one of the values in the filter it finds no result (I assume because the CategoryFilter does not do partial matches).

 var gradeLevels = ['K-2','3-5'];

   var GradeLevelCategoryFilter = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'GradeLevelCategoryFilter',
      'options': {
       'filterColumnIndex': '11',
       'matchType': 'any',
       'allowTyping': true,
       'values' : gradeLevels,
       'ui': {
       'labelStacking': 'horizontal',
       'label': '',
       'caption' : 'Filter by Grade Level(s)',
       'selectedValuesLayout': 'aside',
       'sortValues' : false,
       }
      }
    });  

回答1:

when the spreadsheet loads,
use data table method getDistinctValues(index) on the column
this will return a distinct list of the column values
then split each value on comma and build a unique list of the choices
use the choices for the values option on the filter

then on the filter's 'statechange' event,
use data table method getFilteredRows() to find the row indexes
which contain the filter's selected choice(s)
use the row indexes to set a view on the chart

see following working snippet, a table chart is used...

google.charts.load('current', {
  packages:['controls', 'corechart', 'table']
}).then(function () {
  var sheet = 'https://docs.google.com/spreadsheets/d/1DOTezFuzpH8wzeh25Cgv9e9q577zd_HN1TiIxVQzbUQ/edit#gid=0';

  var table = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'chart-table',
    options: {
      allowHtml: true
    }
  });

  var filter = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'filter-grade',
    options: {
      filterColumnIndex: 12,
      matchType: 'any',
      allowTyping: true,
      ui: {
        labelStacking: 'horizontal',
        label: '',
        caption: 'Filter by Grade Level(s)',
        selectedValuesLayout: 'aside',
        sortValues: false
      }
    }
  });

  new google.visualization.Query(sheet).send(function (response) {
    var data = response.getDataTable();
    var gradeAnswers = data.getDistinctValues(12);
    var gradeChoices = [];
    gradeAnswers.forEach(function (answer) {
      if (answer !== null) {
        var choices = answer.split(',');
        choices.forEach(function (choice) {
          choice = choice.trim();
          if (gradeChoices.indexOf(choice) === -1) {
            gradeChoices.push(choice);
          }
        });
      }
    });
    gradeChoices.sort();

    google.visualization.events.addListener(filter, 'statechange', function () {
      var state = filter.getState().selectedValues;
      table.setView(null);
      if (state.length > 0) {
        table.setView({
          rows: data.getFilteredRows([{
            column: 12,
            test: function (value) {
              var found = false;
              if (value !== null) {
                state.forEach(function (selected) {
                  if (value.indexOf(selected) > -1) {
                    found = true;
                  }
                });
              }
              return found;
            }
          }])
        });
      }
      table.draw();
    });

    filter.setDataTable(data);
    filter.setOption('values', gradeChoices);
    filter.draw();
    table.setDataTable(data);
    table.draw();
  });
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="filter-grade"></div>
<div id="chart-table"></div>

EDIT

you can set the view on the initial definition...

var table = new google.visualization.ChartWrapper({
  chartType: 'Table',
  containerId: 'chart-table',
  options: {
    allowHtml: true
  },
  view: {
    columns: [0]
  }
});

or use the setView method...

table.setView({
  columns: [0]
});

EDIT 2

  var view = {
    columns: [0]
  };
  if (state.length > 0) {
    view.rows = data.getFilteredRows([{
      column: 12,
      test: function (value) {
        var found = false;
        if (value !== null) {
          state.forEach(function (selected) {
            if (value.indexOf(selected) > -1) {
              found = true;
            }
          });
        }
        return found;
      }
    }]);
  }
  table.setView(view);

EDIT 3

you can combine multiple filters into an array,
before passing to getFilteredRows

var view = {
  columns: [0]
};
var viewFilters = [];
if (stateGrade.length > 0) {
  viewFilters.push({
    column: 12,
    test: function (value) {
      var found = false;
      if (value !== null) {
        stateGrade.forEach(function (selected) {
          if (value.indexOf(selected) > -1) {
            found = true;
          }
        });
      }
      return found;
    }
  });
}
if (stateSubject.length > 0) {
  viewFilters.push({
    column: subjectIndex,
    test: function (value) {
      var found = false;
      if (value !== null) {
        stateSubject.forEach(function (selected) {
          if (value.indexOf(selected) > -1) {
            found = true;
          }
        });
      }
      return found;
    }
  });
}
view.rows = data.getFilteredRows(viewFilters);
table.setView(view);