3

I have been having trouble getting getFilteredRows to work with null values with google chart API. I followed the code in this answer, but I can't get it to work. What am I doing wrong?

With the code below, the chart does not draw and I get the error message: "Invalid row index 8. Should be in the range [0-7]". Which would usually means I have entered 8 as a column reference which I haven't.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

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


//CREAT CONTAINER FUNCTION
      function drawVisualization() {

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

            var data = google.visualization.arrayToDataTable([

            ['Frequency', 'Area', 'Old Fruit', 'Cases Ordered', 'Discount', 'Advanced orders', '', '' ],
            ['Rolling', 'Alamo', 'Apples', 85, 'New Customer', 3, 'John', 13], 
            ['Rolling', 'Alamo', 'Oranges', 93,'Cash', 9, 'Mary', 9], 
            ['Rolling', 'Alamo', 'Grapes', 25, 'Prepaid', 1, 'Bob', 2],
            ['Rolling', 'Alamo', 'Lemons', 25, null, null, 'Joy', 5],
            ['Rolling', 'Alamo', 'Limes', 37, null, null, 'Alfred', 15],
            ['Rolling', 'Alamo', 'Plums', 115, null, null, null, null],
            ['Rolling', 'Alamo', 'Peaches', 22, null, null, null, null],
            ['Rolling', 'Alamo', 'Apricots', 13, null, null, null, null],
            ['Rolling', 'Brazos', 'Apples', 40, 'New Customer', 4, 'Allen', 5], 
            ['Rolling', 'Brazos', 'Oranges', 16,'Cash', 8, 'Louise', 15], 
            ['Rolling', 'Brazos', 'Grapes', 99, 'Prepaid', 3, 'Cathy', 3],
            ['Rolling', 'Brazos', 'Lemons', 80, null, null, 'Mark', 10],
            ['Rolling', 'Brazos', 'Limes', 49, null, null, null, null],
            ['Rolling', 'Brazos', 'Plums', 22, null, null, null, null],
            ['Rolling', 'Brazos', 'Peaches', 47, null, null, null, null],
            ['Rolling', 'Brazos', 'Apricots', 90, null, null, null, null],
            ['YTD', 'Alamo', 'Apples', 33, 'New Customer', 8, 'John', 12], 
            ['YTD', 'Alamo', 'Oranges', 18,'Cash',5, 'Mary', 3], 
            ['YTD', 'Alamo', 'Grapes', 75, 'Prepaid', 5, 'Bob', 5],
            ['YTD', 'Alamo', 'Lemons', 40, null, null, 'Joy', 8],
            ['YTD', 'Alamo', 'Limes', 33, null, null, null, null],
            ['YTD', 'Alamo', 'Plums', 84, null, null, null, null],
            ['YTD', 'Alamo', 'Peaches', 12, null, null, null, null],
            ['YTD', 'Alamo', 'Apricots', 13, null, null, null, null],
            ['YTD', 'Brazos', 'Apples',65, 'New Customer', 8, 'Allen', 1], 
            ['YTD', 'Brazos', 'Oranges',89,'Cash', 2, 'Louise', 7], 
            ['YTD', 'Brazos', 'Grapes',22, 'Prepaid', 6, 'Cathy', 7],
            ['YTD', 'Brazos', 'Lemons', 94, null, null, 'Mark', 4],
            ['YTD', 'Brazos', 'Limes', 16, null, null, null, null],
            ['YTD', 'Brazos', 'Plums', 62, null, null, null, null],
            ['YTD', 'Brazos', 'Peaches', 47, null, null, null, null],
            ['YTD', 'Brazos', 'Apricots', 80, null, null, null, null],



      ]);

        var frequencyFilter = new google.visualization.ControlWrapper({
          'controlType': 'CategoryFilter',
          'containerId': 'frequency_control_id',
          'options': {
            'filterColumnLabel': 'Frequency',
            'ui': {
                'labelStacking': 'vertical',
                'selectedValuesLayout': 'below',
                'allowTyping': false,
                'allowMultiple': false
            }
          },

          'state':{selectedValues:['Rolling']}

        });

        var areaFilter = new google.visualization.ControlWrapper({
          'controlType': 'CategoryFilter',
          'containerId': 'area_filter_id',
          'options': {
            'filterColumnLabel': 'Area',
            'ui': {
                'labelStacking': 'vertical',
                'selectedValuesLayout': 'below',
                'allowTyping': false,
                'allowMultiple': false

            }
          },

          'state':{selectedValues:['Alamo']}

        });

       salesChart  = new google.visualization.ChartWrapper({
        'chartType': 'BarChart',
          'containerId': 'sales_chart',
          'options': {
            'width': 320,
              'height': 680,
                'animation':{duration:500},
              'title':'By Board Percent of Target',
              },

            'view': {'columns': [2,3]}
        });

         salesPie  = new google.visualization.ChartWrapper({
        'chartType': 'PieChart',
          'containerId': 'sales_pie_id',
          'options': {'height': 175, 'width': 300},

          'view': {'columns': [4,5]}

        });

       salesTable  = new google.visualization.ChartWrapper({
        'chartType': 'Table',
          'containerId': 'numbers_table_id',
          'options': {'height': 225, 'width': 250},

          'view': {'columns': [6, 7], 
          'rows' : data.getFilteredRows([{column: 7, minValue: 1}])} 


        });

      dashboard.bind([frequencyFilter, areaFilter, ], [salesChart, salesPie, salesTable]);
      dashboard.draw(data);


    }//END DRAWVISUALIZATION

    </script>
  </head>
  <body>

   <div>
      <table>
        <tr>
        <font size="5" face="calibri" color="#00688b"><strong>Sales</strong></font>
        <br />
        <font size="3" face="calibri" color="#00688b">March 2016 Report</font>
        </tr>
      </table>
    </div> 

  <div>
      <table class="columns">
        <tr>
          <td>
            <div id="frequency_control_id" style="padding-left: 2; width: 250"></div>
            <div id="area_filter_id" style="padding-left: 2; width: 250"></div>
            <br />
            <br />
            <br />
            <div id="numbers_table_id"></div>
            <br />
            <br />
            <div id="sales_pie_id"></div>
          </td>
          <td>
            <div id="sales_chart"></div>
          </td>
        </tr>   
       </table>
    </div>


</body>
</html>
Community
  • 1
  • 1
Natalie
  • 45
  • 3

1 Answers1

0

The error is referring to an invalid row index, not column.

The problem stems from the initial state of the Category Filters.
Which limits the data.

Since getFilteredRows is used on the full data set, several of the row indexes will not exist when the dashboard is actually drawn.

An easy fix would be to draw the Table, after the other charts have drawn, using the filtered data table from another chart. 

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

function drawVisualization() {
    var dashboard = new google.visualization.Dashboard(
      document.getElementById('dashboard_id'));

    var data = google.visualization.arrayToDataTable([
        ['Frequency', 'Area', 'Old Fruit', 'Cases Ordered', 'Discount', 'Advanced orders', '', '' ],
        ['Rolling', 'Alamo', 'Apples', 85, 'New Customer', 3, 'John', 13],
        ['Rolling', 'Alamo', 'Oranges', 93,'Cash', 9, 'Mary', 9],
        ['Rolling', 'Alamo', 'Grapes', 25, 'Prepaid', 1, 'Bob', 2],
        ['Rolling', 'Alamo', 'Lemons', 25, null, null, 'Joy', 5],
        ['Rolling', 'Alamo', 'Limes', 37, null, null, 'Alfred', 15],
        ['Rolling', 'Alamo', 'Plums', 115, null, null, null, null],
        ['Rolling', 'Alamo', 'Peaches', 22, null, null, null, null],
        ['Rolling', 'Alamo', 'Apricots', 13, null, null, null, null],
        ['Rolling', 'Brazos', 'Apples', 40, 'New Customer', 4, 'Allen', 5],
        ['Rolling', 'Brazos', 'Oranges', 16,'Cash', 8, 'Louise', 15],
        ['Rolling', 'Brazos', 'Grapes', 99, 'Prepaid', 3, 'Cathy', 3],
        ['Rolling', 'Brazos', 'Lemons', 80, null, null, 'Mark', 10],
        ['Rolling', 'Brazos', 'Limes', 49, null, null, null, null],
        ['Rolling', 'Brazos', 'Plums', 22, null, null, null, null],
        ['Rolling', 'Brazos', 'Peaches', 47, null, null, null, null],
        ['Rolling', 'Brazos', 'Apricots', 90, null, null, null, null],
        ['YTD', 'Alamo', 'Apples', 33, 'New Customer', 8, 'John', 12],
        ['YTD', 'Alamo', 'Oranges', 18,'Cash',5, 'Mary', 3],
        ['YTD', 'Alamo', 'Grapes', 75, 'Prepaid', 5, 'Bob', 5],
        ['YTD', 'Alamo', 'Lemons', 40, null, null, 'Joy', 8],
        ['YTD', 'Alamo', 'Limes', 33, null, null, null, null],
        ['YTD', 'Alamo', 'Plums', 84, null, null, null, null],
        ['YTD', 'Alamo', 'Peaches', 12, null, null, null, null],
        ['YTD', 'Alamo', 'Apricots', 13, null, null, null, null],
        ['YTD', 'Brazos', 'Apples',65, 'New Customer', 8, 'Allen', 1],
        ['YTD', 'Brazos', 'Oranges',89,'Cash', 2, 'Louise', 7],
        ['YTD', 'Brazos', 'Grapes',22, 'Prepaid', 6, 'Cathy', 7],
        ['YTD', 'Brazos', 'Lemons', 94, null, null, 'Mark', 4],
        ['YTD', 'Brazos', 'Limes', 16, null, null, null, null],
        ['YTD', 'Brazos', 'Plums', 62, null, null, null, null],
        ['YTD', 'Brazos', 'Peaches', 47, null, null, null, null],
        ['YTD', 'Brazos', 'Apricots', 80, null, null, null, null],
    ]);

    var frequencyFilter = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'frequency_control_id',
      'options': {
        'filterColumnLabel': 'Frequency',
        'ui': {
            'labelStacking': 'vertical',
            'selectedValuesLayout': 'below',
            'allowTyping': false,
            'allowMultiple': false
        }
      },

      'state':{selectedValues:['Rolling']}

    });

    var areaFilter = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'area_filter_id',
      'options': {
        'filterColumnLabel': 'Area',
        'ui': {
            'labelStacking': 'vertical',
            'selectedValuesLayout': 'below',
            'allowTyping': false,
            'allowMultiple': false

        }
      },

      'state':{selectedValues:['Alamo']}

    });

   var salesChart = new google.visualization.ChartWrapper({
    'chartType': 'BarChart',
      'containerId': 'sales_chart',
      'options': {
        'width': 320,
          'height': 680,
            'animation':{duration:500},
          'title':'By Board Percent of Target',
          },

        'view': {'columns': [2,3]}
    });

   var salesPie = new google.visualization.ChartWrapper({
    'chartType': 'PieChart',
      'containerId': 'sales_pie_id',
      'options': {'height': 175, 'width': 300},

      'view': {'columns': [4,5]}

    });

  dashboard.bind([frequencyFilter, areaFilter], [salesChart, salesPie]);
  google.visualization.events.addListener(dashboard, 'ready', function () {
   new google.visualization.ChartWrapper({
    'chartType': 'Table',
      'containerId': 'numbers_table_id',
      'dataTable': salesChart.getDataTable(),
      'options': {'height': 225, 'width': 250},

      'view': {
        'columns': [6, 7],
        'rows': salesChart.getDataTable().getFilteredRows([{column: 7, minValue: 1}])
      }
   }).draw();
  });
  dashboard.draw(data);


}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div>
  <table>
    <tr>
    <font size="5" face="calibri" color="#00688b"><strong>Sales</strong></font>
    <br />
    <font size="3" face="calibri" color="#00688b">March 2016 Report</font>
    </tr>
  </table>
</div>

<div id="dashboard_id">
  <table class="columns">
    <tr>
      <td>
        <div id="frequency_control_id" style="padding-left: 2; width: 250"></div>
        <div id="area_filter_id" style="padding-left: 2; width: 250"></div>
        <br />
        <br />
        <br />
        <div id="numbers_table_id"></div>
        <br />
        <br />
        <div id="sales_pie_id"></div>
      </td>
      <td>
        <div id="sales_chart"></div>
      </td>
    </tr>
   </table>
</div>

I also noticed the id attribute was missing on the dashboard div element...

WhiteHat
  • 59,912
  • 7
  • 51
  • 133