Using Google Visualization API with own data source

The Google Visualization API allows you to create charts and maps based on data you provide. This data can be in a Google Spreadsheet or be something you provide yourself. The visualizations themselves are mostly written in Javascript, although there are some written in Flash.

In this article we will create an HTML page with multiple charts in it and a data source that is generated by a Python program. We start with the HTML page that has two named div’s in it (visualization1 and visualization2), including the Javascript code that loads the two charts into these div’s.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
      <title>
         Google Visualization API
      </title>
      <script type="text/javascript" src="https://www.google.com/jsapi"></script>
      <script type="text/javascript">
         google.load('visualization', '1', {packages: ['columnchart', 'linechart']});
      </script>
      <script type="text/javascript">

         var query1, visualization1;
         var query2, visualization2;

         function initialize() {
            visualization1 = new google.visualization.ColumnChart(document.getElementById('visualization1'));
            query1 = new google.visualization.Query('https://jansipke.nl/res/visualization/chart-data.py');
            query1.setRefreshInterval(5);
            query1.send(drawVisualization1);

            visualization2 = new google.visualization.LineChart(document.getElementById('visualization2'));
            query2 = new google.visualization.Query('https://jansipke.nl/res/visualization/chart-data.py');
            query2.setRefreshInterval(5);
            query2.send(drawVisualization2);
         }

         function drawVisualization1(response) {
            if (response.isError()) {
               alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
               return;
            }
            visualization1.draw(response.getDataTable(), {legend: 'bottom', title: 'ColumnChart'});
         }

         function drawVisualization2(response) {
            if (response.isError()) {
               alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
               return;
            }
            visualization2.draw(response.getDataTable(), {legend: 'bottom', title: 'LineChart'});
         }

         google.setOnLoadCallback(initialize);
      </script>
   </head>
   <body>
      <div>
         <div id="visualization1" style="height: 250px; width: 400px; border: 1px solid; float: left;" />
      </div>
      <div>
         <div id="visualization2" style="height: 250px; width: 400px; border: 1px solid; float: left; margin-left: 10px" />
      </div>
   </body>
</html>

There are two things that you really need to do before this works correctly:

  • Make sure that the parent node of the div that holds your chart, is not also the parent of one of the other div’s that holds a chart. In the HTML page above we surrounded the chart div with another div to accomplish this.
  • The chart is updated with new data every 5 seconds. It is a known bug of the Google Visualization API that the data needs to be fetched from another server then the one that is hosting the HTML page for the refresh to actually work. In the HTML page above the data source chart-data.py is therefore fetched from jansipke.nl and the HTML is fetched from www.jansipke.nl.

We will create our own data source in Python. There is a description of how you should do this on the Writing Your Own Data Source page from Google. One of the most common mistakes in writing these data sources is the need for the program to read the request identifier (reqId) and return this value in the response. The API needs this to distinguish between responses for different charts on the same page.

import cgi, random

def index(req):
   reqId = None
   if (req.args):
      for arg in req.args.split("&"):
         (key, value) = arg.split("=")
         if (key == "tqx"):
            for parameter in value.split(";"):
               if (parameter.find("%3A") > 0):
                  (par_key, par_value) = parameter.split("%3A")
               if (par_key == "reqId"):
                  reqId = par_value

   a = str(random.randint(1, 3))
   b = str(random.randint(1, 3))
   c = str(random.randint(1, 3))
   d = str(random.randint(1, 3))

   s = ""
   s += "google.visualization.Query.setResponse(n"
   s += "{n"
   if (reqId != None):
      s += "   reqId:'" + reqId + "',n"
   s += "   status:'ok',n"
   s += "   table:n"
   s += "   {n"
   s += "      cols:n"
   s += "      [n"
   s += "         {id:'Col1',label:'',type:'string'},n"
   s += "         {id:'Col2',label:'Label1',type:'number'},n"
   s += "         {id:'Col3',label:'Label2',type:'number'},n"
   s += "         {id:'Col4',label:'Label3',type:'number'}n"
   s += "      ],n"
   s += "      rows:n"
   s += "      [n"
   s += "         {c:[{v:'a',f:'a'},{v:1.0,f:'1'},{v:1.0,f:'1'},{v:" + a + ",f:'1'}]},n"
   s += "         {c:[{v:'b',f:'b'},{v:2.0,f:'2'},{v:1.5,f:'1'},{v:" + b + ",f:'1'}]},n"
   s += "         {c:[{v:'c',f:'c'},{v:3.0,f:'3'},{v:2.5,f:'1'},{v:" + c + ",f:'1'}]},n"
   s += "         {c:[{v:'d',f:'d'},{v:4.0,f:'1'},{v:2.0,f:'1'},{v:" + d + ",f:'1'}]}n"
   s += "      ]n"
   s += "   }n"
   s += "});"

   return s

We can test this data source by following the link without parameters and following the link with the reqId parameter present:

Notice that the first one does not have reqId present in the response, but the second one does.

Update: it seems that the refreshing of data only happens correctly in Firefox and Opera. IE doesn’t refresh at all and Chrome only refreshes once. Oh joy!

2 thoughts on “Using Google Visualization API with own data source

  1. Permalink  ⋅ Reply

    Brett

    March 19, 2013 at 2:27am

    Thank you so much for your explanation and excellent example. It has really helped me to get started with the visualisation API.
    Cheers.

Leave a Reply

Your email will not be published. Name and Email fields are required.