How to do pie charts with Google Charts and Sheets

After I explained in the last post how to do pie charts with highcharter in R, the following will explain how to create similar pie charts with Google charts and Google sheets.

Data preparation

As a first step, I’ve created a sheet with the election data from the German Bundestag in 2013. The data can be found together with other election results on http://www.electionresources.org/de/. The sheet needs to be publicly available in order to access them from the page without registration as a web app.

To store the data used for drawing the charts, I create a global variable dataView to store the data.

var dataView;

In the first step, we need to query the data from the Google sheet. Therefore, a query is created and sent together with a callback method that handles the result.

function initChartView() {
  var query = new google.visualization.Query("https://docs.google.com/spreadsheets/d/1z4iWtsN9ftY2YX1IPcBzSyjOQCLv8OMLXkxAoXJUHu8/edit#gid=0");
  query.send(handleQueryResponse);
}

The callback function evaluates the response if it is a valid response. It reads the data from the response as DataTable. However, the data in my Google sheet contain more data than only the ones we want to draw. Therefore, I create a DataView from it and filter the results on national level (only “DE”).

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  // Create a DataTable and a DataView
  var data = response.getDataTable();
  dataView = new google.visualization.DataView(data);
  dataView.setRows(data.getFilteredRows([{column: 0, value: "DE"}]));
  // Call the method to draw the charts
  redrawChart("votes")
}

Drawing the pie chart

After I have selected the rows that I need to create my pie chart(s), I call my function that further prepares the drawing. This function has one argument which serves as a selector to draw pie charts either for the amount of votes a party received or a for the amount of seats that resulted from these votes. For the drawing I limit the data view to the relevant columns and call my function responsible for drawing a pie chart. Thereby, I hand over the id of the element in the HTML that represents the chart (e.g. “firstVotesChart”), the title of the chart (e.g. “Election Results Bundestagswahl…”), and the created data view.

function redrawChart(chartContent) {
  if (chartContent == "votes") {
    dataView.setColumns([2, 3]);
    drawChart("firstVotesChart", 'Election Results Bundestagswahl 2013 - First Votes', dataView);
 
    dataView.setColumns([2, 6]);
    drawChart("secondVotesChart", 'Election Results Bundestagswahl 2013 - Second Votes', dataView);

  } else if(chartContent == "seats") {
    dataView.setColumns([2, 5]);
    drawChart("firstVotesChart", 'Election Results Bundestagswahl 2013 - Direct Seats', dataView)

    dataView.setColumns([2, 8]);
    drawChart("secondVotesChart", 'Election Results Bundestagswahl 2013 - List Seats', dataView)
  }
}

The function for drawing the chart just calls the responsible function from the google visualisation. I configure with the parameters the chart type, the ID of the HTML element that should be replaced, the data and some further options like the title. Check out the documentation for more available options.

function drawChart(containerId, title, data) {
 google.visualization.drawChart({
   chartType:"PieChart",
   containerId:containerId,
   dataTable:data,
   options:{
     title: title,
     colors:["#000000","#0088CE","#FF0000","#8C3473","#008B00","#FFFF00","#009EE0","#FF820A","#8B4726","#63B8FF","#808080"],
     is3D:true
   }
 })
}

However, in order to call the google visualization function, I have to load the library first.

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

After the library is loaded, I can start with the creation of the chart. I do this by setting the previously defined method as callback when the loading is finished.

google.charts.setOnLoadCallback(initChartView);

Resulting pie chart

The resulting pie charts are shown below and the complete HTML can be found here.