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="http://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('http://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('http://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 http://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!

Advertisements
This entry was posted in programming and tagged , , . Bookmark the permalink.

One Response to Using Google Visualization API with own data source

  1. Pingback: Using Google Visualization API with own data source « Data Warehouse in the Cloud

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s