DataView for Chart

97 views
Skip to first unread message

Gavin Dundee

unread,
Jul 29, 2025, 6:02:11 PM Jul 29
to Google Visualization API
I am seeking help.  Here is my datatable setup:
var grouped_data = google . visualization . data . group (
proxyDataTable ,
    [ yearIndex ],
    [
      { 'column' : sschIndex , 'aggregation' : google . visualization . data . sum , 'type' : 'number' },
      { 'column' : enrollmentsIndex , 'aggregation' : google . visualization . data . sum , 'type' : 'number' },
      { 'column' : crnIndex , 'aggregation' : google . visualization . data . count , 'type' : 'number' }
    ]
  );

Now I am creating a view:
var view = new google . visualization . DataView ( grouped_data );

Then I draw the view:
view . setColumns ([[ 0 , 1 ],[ 0 , 1 ],[ 0 , 1 ]]);
chart0 . setDataTable ( view );
chart0 . draw ();

My issue is with setColumns().  There are 3 columns being displayed.  I thought I would be able to use this code:
view.setColumns([1,2]);
To display only columns 1 and 2 and not show column 3.  My assumption is that [1,2,3] would show all the columns, but that does not work either.  I am not sure where to go from here I have tried setColumns with many different arrays, but I am just guessing at this point.  Can anyone help determine how I could hide a column using setColumns?

I attached a screenshot of what my chart looks like.

Thanks in advance for any help!!
Screenshot 2025-07-29 164613.png

Gavin Dundee

unread,
Jul 30, 2025, 10:33:55 AM Jul 30
to Google Visualization API
With array inputs:
[0,1,2]
or
[0,1,3]

I usually get an error like this:
Invalid column index 3. Should be an integer in the range [0-2].

If I set the array to:
[0,1,2,3]
I get all three columns per row.

I am still at a loss.  Any help would be greatly appreciated! :)

Alvin Galit

unread,
Jul 30, 2025, 10:47:55 AM Jul 30
to Google Visualization API
[0,1, 2] doesn't work? Index starts at [0] and since you have 3 columns, I thought maybe that should be working. 
It makes sense that [0, 1, 3] doesn't work because you only have 3 columns and so the 3rd column is at index [2].

Gavin Dundee

unread,
Jul 30, 2025, 11:10:15 AM Jul 30
to Google Visualization API
It really doesn't work.  I truly am puzzled by this.  I am a newbie to this though.

Alvin Galit

unread,
Jul 30, 2025, 11:17:02 AM Jul 30
to Google Visualization API
Are you able to share your whole code?

Gavin Dundee

unread,
Jul 30, 2025, 2:04:12 PM Jul 30
to Google Visualization API
I finally got it figured out.  Column charts cannot change the number of columns.  I had to build the view with hidden columns.  Thanks for taking a look at this Alvin!

  Here is my new helper function:
//returns a view with hidden columns
//expects a Google Charts dataTable and an array of indexes of the desired columns to show
function buildViewWithSpecifiedColumns ( dataTable , visibleIndexes ) {
const totalCols = dataTable . getNumberOfColumns ();
const viewColumns = [];

for ( let i = 0 ; i < totalCols ; i ++ ) {
if ( visibleIndexes . includes ( i )) {
viewColumns . push ( i );
    } else { //the column should be hidden
viewColumns . push ({
type : dataTable . getColumnType ( i ),
label : dataTable . getColumnLabel ( i ),
calc : () => null
      });
    }
  }

const view = new google . visualization . DataView ( dataTable );
view . setColumns ( viewColumns );
return view ;
}

Here is the updated code to redraw the chart:

//logic to show/hide columns when clicking on the legend
google . visualization . events . addListener ( chart0 , 'select' , function () {
var sel = chart0 . getChart (). getSelection ();
if ( sel . length > 0 ) { //either a legend item or a chart element has been selected
if ( sel [ 0 ]. row === null ) { //legend has been clicked
var selectedCol = sel [ 0 ]. column ;

//toggle column visibility in the activeColumns array
var indexInactive = activeColumns . indexOf ( selectedCol ); //get the index in the array for the selected column
if ( indexInactive > - 1 ) { //if it is -1 it was not in the active columns array
activeColumns . splice ( indexInactive , 1 ); //remove if present (hide)
        } else {
insertSorted ( activeColumns , selectedCol ); //add column if not present (show)
        }
      }
    } // else //an element in the chart has been deselected

const view = buildViewWithSpecifiedColumns ( grouped_data , activeColumns );
chart0 . setDataTable ( view );
chart0 . draw ();
  });

Gavin Dundee

unread,
Jul 30, 2025, 2:04:35 PM Jul 30
to Google Visualization API
I sent my whole code earlier.  If you did not get it, please let me know.

Update:
I tried the removeColumn() method, but I still get the same results.  It is doing the same as the setColumn.  I verified this with the getViewColumns() method.

I also am able to duplicate columns.  If I do this:
view.setColumns([0,1,2,2])

It does actually produce column 2 twice which makes column 3 not visible any longer.  It also updates the legend.  I have attached a photo of this behavior.
Screenshot 2025-07-30 111700.png

Alvin Galit

unread,
Jul 30, 2025, 3:49:16 PM Jul 30
to Google Visualization API
Hey Gavin. I think I just see snippets of your code. I can take a closer look if you provide the whole file.

Gavin Dundee

unread,
Jul 30, 2025, 4:29:51 PM Jul 30
to Google Visualization API
I'm not sure why it never sent my message with my code attached.  I have attached it here.  Like I said though, I got it figured out.  Column charts cannot have the number of columns hidden.  They must be psuedo hidden.  I have attached my code here in case you still want to take a look!  Thanks for being willing to help.  My faith in humanity was boosted because of you, Alvin. <3


var params = new URLSearchParams ( window . location . search );
var data = null ;
var options = null ;
var proxyTable = null ;
let proxyDataTable = null ;
var chart0 = null ;
var filters = [];

//variables for showing/hiding columns
var activeColumns = [];
let chartSelectListener = false ; //prevent duplicate listeners from being added

$ ( function () {
google . charts . load ( 'current' , { 'packages' : [ 'corechart' , 'controls' ]});
google . charts . setOnLoadCallback ( function () {
getData ()
    . done ( function ( response ) { //runs once response is returned from getData()
let processedData = processData ( response ); //add new rows (Fall, Spring, Academic Year)
drawChart ( processedData );
    })
    . fail ( function ( jqXHR , textStatus , errorThrown ) {
console . error ( "Error fetching data:" , textStatus , errorThrown );
$ ( '#loading' ). text ( "Error loading data." ); // Update loading message on error
    })
    . always ( function () {
$ ( '#loading' ). remove (); // Remove loading indicator once done (success or fail)
    });
  });

$ ( '#contents' ). append ( '<div style="display: none;" id="proxyTable"></div>' ); //add the proxyTable to the DOM because it is necessary for Google Charts to operate properly
//make the chart auto resize based on window size
$ ( window ). resize ( function () {
if ( this . resizeTO ) clearTimeout ( this . resizeTO );
this . resizeTO = setTimeout ( function () {
$ ( this ). trigger ( 'resizeEnd' );
    }, 500 );
  });

//redraw graph when window resize is completed
$ ( window ). on ( 'resizeEnd' , function ( e ) {
chart0 . draw ();
  });
});

function getData () {
$ ( '#ef_left' ). append ( '<div id="loading" style="width: 100%; text-align: center;">Loading</div>' );
const baseDataURL = window . location . pathname . replace ( /index.php. * $ / , 'index.php/json/' );
let data = $ . getJSON ( baseDataURL + '/data' , $ . param ({ 'func' : 'classsections' , 'json' : '' }));
return data ;
}

//add a column for the Period(Academic Year, Fall, or Spring) and Year for filtering and remove null/empty values for any columns with number as the type
function processData ( rawData ) {
const transformedData = {
cols: rawData . cols . filter ( col => col . label !== "Academic Year" ),
rows: []
  };

//add the two columns to the beginning
transformedData . cols . unshift (
    { id: "" , label: "Period" , pattern: "" , type: "string" },
    { id: "" , label: "Year" , pattern: "" , type: "string" }
  );

const academicYearIndex = rawData . cols . findIndex ( col => col . label === "Academic Year" );
const termIndex = rawData . cols . findIndex ( col => col . label === "Term" );

rawData . rows . forEach ( row => {
const term = row . c [ termIndex ]?. v || "" ;
const academicYear = printAYperiod ( row . c [ academicYearIndex ]?. v || "" );
const baseRow = row . c . filter (( _ , idx ) => idx !== academicYearIndex );

transformedData . rows . push ({
c: [
        { v: "Academic Year" , f: null },
        { v: academicYear , f: null },
... baseRow
      ]
    });

if ( term . endsWith ( "70" )) {
transformedData . rows . push ({
c: [
          { v: "Fall" , f: null },
          { v: term . substring ( 0 , 4 ), f: null },
... baseRow
        ]
      });
    }

if ( term . endsWith ( "20" )) {
transformedData . rows . push ({
c: [
          { v: "Spring" , f: null },
          { v: term . substring ( 0 , 4 ), f: null },
... baseRow
        ]
      });
    }
  });

//after manipulating the data, make sure to resolve any null values for numbers
//identify numeric column indexes
const numericIndexes = transformedData . cols
    . map (( col , idx ) => col . type === "number" ? idx : - 1 )
    . filter ( idx => idx !== - 1 );

//iterate over all rows
transformedData . rows . forEach ( row => {
numericIndexes . forEach ( idx => {
const cell = row . c [ idx ];
const value = cell ?. v ;

//if cell missing, null, empty string, or invalid — set to 0
if ( ! cell || cell . v === null || cell . v === "" || isNaN ( cell . v )) {
row . c [ idx ] = { v: 0 , f: null };
      } else {
//force all to type number just in case any were saved as strings
row . c [ idx ]. v = Number ( value );
      }
    });
  });

return transformedData ;
}

function drawChart ( d ) {
data = new google . visualization . DataTable ( d ); //data is transformed to a Google dataTable

//desired filters (must be a column in the returned data)
const filterList = [
    { name: 'Period' , allowMultiple: false , allowNone: false },
    { name: 'Campus' , allowMultiple: true , allowNone: true },
    { name: 'College' , allowMultiple: true , allowNone: true },
    { name: 'Department' , allowMultiple: true , allowNone: true },
    { name: 'Course Level' , allowMultiple: true , allowNone: true },
    { name: 'Detail Level' , allowMultiple: true , allowNone: true },
    { name: 'Concurrent' , allowMultiple: false , allowNone: true }, //don't allow multiple because if both Y and N are selected it is the same as not selecting either one
    { name: 'General Education' , allowMultiple: false , allowNone: true } //don't allow multiple because if both Y and N are selected it is the same as not selecting either one
  ];

//create the array of filters containing the Google controlWrappers
filterList . forEach ( filterInfo => {
let filter_id = filterInfo . name . replace ( / / g , "_" ) + '_filter' ; //replace all spaces with _ and append _filter
$ ( "#ef_right" ). append ( '<div id="' + filter_id + '"></div>' ); //create all filters in the ef_right div

filters . push ( new google . visualization . ControlWrapper ({
controlType: 'CategoryFilter' ,
containerId: filter_id ,
options: {
filterColumnLabel: filterInfo . name , //this is the label from your dataTable/the returned column names from the database
ui: {
allowMultiple: filterInfo . allowMultiple ,
allowTyping: false ,
sortValues: true ,
selectedValuesLayout: 'below' ,
labelStacking: 'vertical' ,
allowNone: filterInfo . allowNone
        }
      }
    }));

  });

//invisible table used for filtering
proxyTable = new google . visualization . ChartWrapper ({
'chartType' : 'Table' ,
'containerId' : 'proxyTable'
  });

//build the chartWrapper for the column graph
options = {
'hAxis' : {
'title' : null
    },
'vAxis' : {
'format' : '#,###' ,
'minValue' : 0 ,
'maxValue' : 'auto' ,
'textPosition' : 'in' ,
'title' : null
    },
//'title': 'Class Sections and SSCH',
'legend' : {
'position' : 'bottom'
    },
'chartArea' : {
'left' : '10' ,
'right' : '10' ,
'top' : '50' ,
'bottom' : '50' ,
'width' : '100%' ,
'height' : '100%' ,
    },
'width' : '100%' ,
'height' : 450 ,
'viewWindowMode' : 'pretty' ,
'tooltip' : {
'trigger' : 'selection' ,
'isHtml' : true
    },
'annotations' : {
'alwaysOutside' : true ,
'highContrast' : true ,
    }
  };

options = JSON . parse ( JSON . stringify ( options ));
chart0 = new google . visualization . ChartWrapper ({
'chartType' : 'ColumnChart' ,
'containerId' : 'chart0' ,
'options' : options
  });

var dash = new google . visualization . Dashboard ( document . getElementById ( 'dashboard' ));
dash . bind ( filters , proxyTable );
google . visualization . events . addListener ( proxyTable , 'ready' , proxyTableReady ); //call proxyTableReady once the proxy table is fully drawn

dash . draw ( data ); //draw the dashboard with retrieved JSON data which has been converted to a google dataTable
}

function proxyTableReady () {
proxyDataTable = proxyTable . getDataTable (); //retrieve filtered datatable (filters are applied to proxyTable)

//get indexes and then aggregate/group data for graphing
let yearIndex = proxyDataTable . getColumnIndex ( 'Year' ); //23
let sschIndex = proxyDataTable . getColumnIndex ( 'SSCH' ); //0
let enrollmentsIndex = proxyDataTable . getColumnIndex ( 'ENROLLMENTS' ); //1
let crnIndex = proxyDataTable . getColumnIndex ( 'CRN' ); //2

var grouped_data = google . visualization . data . group (
proxyDataTable ,
    [ yearIndex ],
    [
      { 'column' : sschIndex , 'aggregation' : google . visualization . data . sum , 'type' : 'number' },
      { 'column' : enrollmentsIndex , 'aggregation' : google . visualization . data . sum , 'type' : 'number' },
      { 'column' : crnIndex , 'aggregation' : google . visualization . data . count , 'type' : 'number' }
    ]
  );

if ( activeColumns . length === 0 ) { //only run if activeColumns is not populated
const x = grouped_data . getNumberOfColumns ();
for ( let i = 0 ; i < x ; i ++ ) {
activeColumns . push ( i ); //build array of active Columns for showing/hiding them in the chart
    }
  }

var view = buildViewWithSpecifiedColumns ( grouped_data , activeColumns );
chart0 . setDataTable ( view );
chart0 . draw ();
setChartActionDownload ( chart0 );

if ( chartSelectListener == false ) {
//logic to show/hide columns when clicking on the legend
google . visualization . events . addListener ( chart0 , 'select' , function () {
var sel = chart0 . getChart (). getSelection ();
if ( sel . length > 0 ) { //either a legend item or a chart element has been selected
if ( sel [ 0 ]. row === null ) { //legend has been clicked
var selectedCol = sel [ 0 ]. column ;

//toggle column visibility in the activeColumns array
var indexInactive = activeColumns . indexOf ( selectedCol ); //get the index in the array for the selected column
if ( indexInactive > - 1 ) { //if it is -1 it was not in the active columns array
activeColumns . splice ( indexInactive , 1 ); //remove if present (hide)
          } else {
insertSorted ( activeColumns , selectedCol ); //add column if not present (show)
          }

proxyTableReady (); //recursively call this function to redraw the chart with selected filters
        }
      } // else //an element in the chart has been deselected
    });

chartSelectListener = true ; //prevent the listener from being added more than once
  }
}

//makes the chart downloadable by adding a download button to the tooltip.
function setChartActionDownload ( chart ) {
chart . getChart (). setAction ({
'id' : chart . getContainerId () + '_tooltip' ,
'text' : 'Download Data' ,
'action' : function () { downloadData ( chart ); },
'visible' : function () { return isDownloadVisible ( chart ); }
  });
}

function downloadData ( chart ) {
var chartData = chart . getDataTable ();
var selection = chart . getChart (). getSelection ();
var filter_data = {};

//in the future it could limit the download to the single academic year that is clicked on
//filter_data['field'] = chartData.getColumnLabel(selection[0]['column']); //grabs the name of the column the user clicked in Google Charts

//grab the filter options
$ ( filters ). each ( function () {
filter_data [ this . getOption ( 'filterColumnLabel' )] = this . getState ()[ 'selectedValues' ];
  });

window . location . href = 'classsections/csv?' + $ . param ( filter_data );

/* this whole section will download the data directly from the dataTable
  //manually build the header row
  let headerRow = [];
  for (let i = 0; i < proxyDataTable.getNumberOfColumns(); i++) {
    let label = proxyDataTable.getColumnLabel(i);
    headerRow.push('"' + label.replace(/"/g, '""') + '"');
  }
  let csvHeaders = headerRow.join(',') + '\n';

  let csvFormattedDataRows = google.visualization.dataTableToCsv(proxyDataTable);

  //combine headers and data
  let fullCsvContent = csvHeaders + csvFormattedDataRows;
  let encodedUri = 'data:application/csv;charset=utf-8,' + encodeURIComponent(fullCsvContent);

  //download link creation
  let $link = $('<a>')
    .attr('href', encodedUri)
    .attr('download', 'class_sections_ssch.csv')
    .css('display', 'none')
    .appendTo('body');

  $link[0].click(); //execute the downloading by "clicking" the link
  $link.remove();   //clean up the DOM
  */
}


function isDownloadVisible ( chart ) {
let chartData = chart . getDataTable ();
let selection = chart . getChart (). getSelection ();

if ( selection . length > 0 ) {
if ( chartData . getColumnRole ( selection [ 0 ][ 'column' ]) == 'annotation' ) //prevent the download button from showing on columns with the role of annotation
return false ;
else
return true ;
  } else
return false ;
}

//returns the period of years when given the academic year
function printAYperiod ( academicYear ) {
const endYear = parseInt ( academicYear , 10 ); //convert to integer
if ( ! isNaN ( endYear )) { //check if conversion was successful
const startYear = endYear - 1 ;
return ` ${ startYear } - ${ endYear } ` ;
  } else {
return false ;
  }
}

//inserts a value into an array in numerical order
//the array is assumed to consist of numbers
function insertSorted ( arr , val ) {
let i = 0 ;
while ( i < arr . length && arr [ i ] < val ) {
i ++ ;
  }
arr . splice ( i , 0 , val ); // Insert val at index i
}

//returns a view with hidden columns
//expects a Google Charts dataTable and an array of indexes of the desired columns to show
function buildViewWithSpecifiedColumns ( dataTable , visibleIndexes ) {
const totalCols = dataTable . getNumberOfColumns ();
const viewColumns = [];

for ( let i = 0 ; i < totalCols ; i ++ ) {
if ( visibleIndexes . includes ( i )) {
viewColumns . push ( i );
    } else { //the column should be hidden
viewColumns . push ({
type: dataTable . getColumnType ( i ),
label: dataTable . getColumnLabel ( i ),
calc : () => null
      });
    }
  }

const view = new google . visualization . DataView ( dataTable );
view . setColumns ( viewColumns );
return view ;
}

Alvin Galit

unread,
Jul 30, 2025, 4:32:02 PM Jul 30
to Google Visualization API
Oh gotcha! Glad you got it figured out. That was all YOU!
Reply all
Reply to author
Forward
0 new messages