Google Charts API

Clickable Charts

Introduction

This page was written because of a query by Isaac Saenz in the API user-supported group

Isaac simply wanted to know if there was a way to make the individual columns and bars clickable in a chart.

After a bit of experimentation I found that there is, but it is not immediately obvious from the documentation. An event listener needs to be added to the chart using addListener().

When a listener is triggered by a click on the chart then a variation of the getSelection() function is called. Unfortunately the documentation is not very clear on how to do this, but it can be done.

The Charts

I have tested this on various types of chart and it certainly works on Bar, Column, Pie, and Scatter. It does not work on Area charts.

Using a switch statement

For this chart, the first 5 data rows and the first two data columns (which exclude the third column which contains the URLs in the Google Sheet) from this Google Sheet are going to be used as the data source. The URLs are controlled using a switch statement.

The code for the above chart is:


<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(switchboard);

function drawSwitchViz() {
   var queryString = encodeURIComponent('SELECT A,B ORDER BY A LIMIT 5');
   var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=0&headers=1&tq=' + queryString);
   query.send(handleQueryResponse);
}
	 
function handleQueryResponse(response) {
   if (response.isError()) {
   alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
   return;
}
  
var switchData = response.getDataTable();
var switchChart = new google.visualization.ColumnChart(document.getElementById('switch-chart'));
switchChart.draw(switchData);
// Add the event handler and processing
google.visualization.events.addListener(switchChart, 'select', switchHandler);
function switchHandler(e) {
   var selection = switchChart.getSelection();
   var switchPizza=switchData.getFormattedValue(selection[0].row,0);
   switch (switchPizza) {
      case 'Boscaiola':
         window.open('https://it.wikipedia.org/wiki/Boscaiola','_blank');
		 break;
      case 'Bufalina':
		 window.open('https://en.wikipedia.org/wiki/Buffalo_mozzarella','_blank');
		 break;
      case 'Caprese':
         window.open('https://en.wikipedia.org/wiki/Caprese_salad','_blank');
		 break;
	  case 'Capricciosa':
         window.open('https://en.wikipedia.org/wiki/Pizza_capricciosa','_blank');
		 break;
	  case 'Carbonara':
         window.open('https://en.wikipedia.org/wiki/Carbonara','_blank');
		 break;
   }
}
}
</script>

Using URLs in the data

For this chart, the first 5 data rows and all three data columns (which include the third column which contains the URLs in the Google Sheet) from this Google Sheet are going to be used as the data source. The URLs come from the data, but are not displayed in the chart.

The code for the above chart is:


<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawURLViz);
function drawURLViz() {
   var queryString = encodeURIComponent('SELECT A,B,C ORDER BY A LIMIT 5');
   var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=0&headers=1&tq=' + queryString);
   query.send(handleQueryResponse);
}
	 
function handleQueryResponse(response) {
   if (response.isError()) {
   alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
   return;
}
  
var URLData = response.getDataTable();
var URLView = new google.visualization.DataView(URLData);
URLView.setColumns([0, 1]);
var URLChart = new google.visualization.BarChart(document.getElementById('URL-chart'));

URLChart.draw(URLView);
// Add the event handler and processing
google.visualization.events.addListener(URLChart, 'select', URLHandler);
function URLHandler(e) {
   var selection = URLChart.getSelection();
   var URLPizza=URLData.getFormattedValue(selection[0].row,2);
   window.open(URLPizza,'_blank');
}
}
</script>

var selection = [chart].getSelection();

What this does is return an array of objects with one object - selection[0]. That object contains the object's row and column. Those objects contain the row number and column number that was clicked on. In these examples the column number is always 1, but the row number contains the actual row number, starting from 0, that was clicked on.

From the console log which is accessed in Chrome using console.log(selection[0]); the result could be {row: 3, column: 1}. Hence the use of selection[0].row in the getFormattedValue function.

getFormattedValue(selection[0].row,0);

The line getFormattedValue(selection[0].row,0); is dependant on the underlying data table or view. It may be necessary to change it to getFormattedValue(selection[0].column,0); depending on the orientation of the table. In the second example given, row(0) is not used but row(2) is. This is because the URL to be used for the link is in the third column (remember that the columns are counted from 0 in code).

getSelection() Function

If you use the getSelection() function as written in the documentation, what you get is an array of the data with not much help in isolating which column or row was clicked on. An example of the result of the function as it is written is:

You selected Platinum{"cols":[{"label":"Element","type":"string"},{"label":"Density","type":"number"}],"rows":[{"c":[{"v":"Copper"},{"v":8.94}]},{"c":[{"v":"Silver"},{"v":10.49}]},{"c":[{"v":"Gold"},{"v":19.3}]},{"c":[{"v":"Platinum"},{"v":21.45}]}]}

Which is not much help at all.

Special Note

The code given for the charts above are written as if just one chart is used on a page. Draw Multiple Charts in the documentation shows how to draw multiple charts by putting the code into the head section of a web page. The actual code used on this page is:


<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
   var queryString = encodeURIComponent('SELECT A,B,C ORDER BY A LIMIT 5');
   var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=0&headers=1&tq=' + queryString);
   query.send(handleQueryResponse);
}
	 
function handleQueryResponse(response) {
   if (response.isError()) {
   alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
   return;
}
  
var data = response.getDataTable();
	
var switchView = new google.visualization.DataView(data);
var switchChart = new google.visualization.ColumnChart(document.getElementById('switch-chart'));
switchView.setColumns([0, 1]);
switchChart.draw(switchView);

var URLView = new google.visualization.DataView(data);
URLView.setColumns([0, 1]);
var URLChart = new google.visualization.BarChart(document.getElementById('URL-chart'));
URLChart.draw(URLView);
	
// Add the event handler and processing for the switchChart
google.visualization.events.addListener(switchChart, 'select', switchHandler);
function switchHandler(e) {
   var selection = switchChart.getSelection();
   var switchPizza=switchView.getFormattedValue(selection[0].row,0);
   switch (switchPizza) {
      case 'Boscaiola':
         window.open('https://it.wikipedia.org/wiki/Boscaiola','_blank');
		 break;
      case 'Bufalina':
		 window.open('https://en.wikipedia.org/wiki/Buffalo_mozzarella','_blank');
		 break;
      case 'Caprese':
         window.open('https://en.wikipedia.org/wiki/Caprese_salad','_blank');
		 break;
	  case 'Capricciosa':
         window.open('https://en.wikipedia.org/wiki/Pizza_capricciosa','_blank');
		 break;
	  case 'Carbonara':
         window.open('https://en.wikipedia.org/wiki/Carbonara','_blank');
		 break;
   }
}
	
// Add the event handler and processing forthe URLChart
google.visualization.events.addListener(URLChart, 'select', URLHandler);
function URLHandler(e) {
   var selection = URLChart.getSelection();
   var URLPizza=data.getFormattedValue(selection[0].row,2);
   window.open(URLPizza,'_blank');
}
}
</script>