General Google visualization tool

We previously looked at how to embed a motion chart in excel, as well as how to serialize data as input to one. As a matter of fact, the interaction with the visualization API and the format of the input data is rather standard across different kinds of charts, so it is very simple to build on what we've previously done to create a general purpose tool that can create any visualization chart. Adding new charts to the repertoire will therefore be very simple using this framework. You can find the generalized tool in the downloads section. This means that the previous single use implementations have now been replaced with this single tool. 

Recap

We now know how to create a Google Motion Chart , Embed it in our Excel Sheet, or serialize data for input to a web page or gadget containing a motion chart. All this is achieved by a single click in our example Excel Form. To take it to the next level, we want to create a framework to make it easy to add new visualizations. The example so far includes at least Motion Charts and Intensity Maps. By the time you download it, there may have been more added. 

Example Application

The downloadable example provides a form to create all the necessary web pages and data, and optionally to display the chart. It looks like this.


This application does the following;
  1. Identifies and processes the columns of data on your active sheet that are to be used as input to Google Visualization.
  2. Generates an html file containing the code and data combined. This can be directly loaded in a browser locally (assuming you have followed the instructions on enabling Flash locally), or it could be loaded on a web site and executed from there. 
  3. Navigates to that local file and shows the chart embedded in the form.
  4. Creates a serialized data file that you can use as input to a Google visualization Gadget or some other script that requires JSON serialized data using  Google Wire Protocol. An example wrapper script is provided so you can customize it.
  5. Provides a command line with the parameters needed to your wrapper script in order to generate the required chart and link it to the serialized data. This would look something like this 
file://d:\googleVisWrapper.html?package=intensitymap&method= IntensityMap&dataurl= file:///D:\googleSerializedData.html&height=402&width=690

Structure of the example application

  1.  A form as above to select options and parameters
  2. An application module to execute the options and parameters - googleChartModule. 
  3. cGoogleChartInput class. This class does all the work to serialize the Excel data as well as to generate the required javascript. 
  4. cDataSet and related classes. This is a set of utility classes that abstract data from their spreadsheet location, and are used by the cGoogleChartInput class. They are not the subject of this article, but more information can be found here if required.

cGoogleChartInput class

We are going to use a single class with common methods to create and serialize data.  I have introduced an eNum in the cGoogleChartInput Class, as follows. This will be expanded as new chart types are added. 

Public Enum eGoogVis
    eGoogVisUnknown
    eGoogVisIntensityMap
    eGoogVisMotion
End Enum

It is initialized with these parameters. The headOrderArray argument is provided so that the data from the sheet can be re-organized into a different order in case it does not meet the needs of the various Google visualizations. I have not implemented this in the example application, but columns can be omitted or reorganized by specifying and array of column headings that should be included, for example array("Country", "Cost per Employee") would use only 2 of the columns in that order. If the argument is missing, all columns identified by the column heading range are used. Note also that there is no validation that the input data is of the correct type for the selected visualization. You will need to read up on them to see the type of data expected.

Public Function init(htmlfn As String, rWhere As Range, _
                            Optional headOrderArray As Variant = Empty, _
                            Optional gwidth As Long = cWidth, _
                            Optional gheight = cHeight, _
                            Optional serializeddatafn As String = "", _
                            Optional wrapperfn As String = "", _
                            Optional vType As eGoogVis = eGoogVisMotion) As Boolean

Aside from minor changes in the methods that construct the javascript syntax, there was really not much more new  in this class from the previous excercise. Only the google.visualization method name and the package name varies from chart to chart, so this is easily abstracted as follows

Public Property Get gvMethod() As String
    ' this can handle multiple types of visualizations.. this will be expanded as more are added
    Select Case pGoogVis
        Case eGoogVisIntensityMap
            gvMethod = "IntensityMap"
        Case eGoogVisMotion
            gvMethod = "MotionChart"
        Case Else
            MsgBox ("Programming error - chart type not implemented")

    End Select
            
End Property
Public Property Get gvPackage() As String
    ' these are the same as the method, but just in lcase
    gvPackage = LCase(gvMethod)
            
End Property

along with some parameterizations to create the javascript

Private Function ScriptStart() As String
    ScriptStart = _
        "google.load('visualization', '1', {packages: ['" & gvPackage & "']});" & vbCrLf & _
        "function " & funName & "() {" & vbCrLf & _
        "var " & datName & " = new google.visualization.DataTable();"

End Function
Private Function ScriptFinish() As String
    ScriptFinish = _
        "var " & varName & " = new google.visualization." & gvMethod & "(document.getElementById('" & divName & "'));" & vbCrLf & _
        varName & ".draw(" & datName & ", {width: " & CStr(pWidth) & ", height:" & CStr(pHeight) & "}); }" & vbCrLf & _
        "google.setOnLoadCallback(" & funName & ");"
End Function

In order to minimize name conflicts should the output of multiple charts be combined, function, data, div ID, and variable names are changed according to the type of chart we are creating 

Private Function divName() As String
    divName = cDivPrefix & gvMethod
End Function
Private Function funName() As String
    funName = cFunPrefix & gvMethod
End Function
Private Function varName() As String
    varName = cVarPrefix & gvMethod
End Function
Private Function datName() As String
    datName = cDatPrefix & gvMethod
End Function

Example output
Here is what the generated script for an embedded IntensityMap comes out like.

<html>
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

google.load('visualization', '1', {packages: ['intensitymap']});
function funIntensityMap() {
var datIntensityMap = new google.visualization.DataTable();
datIntensityMap.addColumn('string', 'Country');
datIntensityMap.addColumn('number', 'No of Employees');
datIntensityMap.addColumn('number', 'PC per employee');
datIntensityMap.addColumn('number', 'Network cost per employee');
datIntensityMap.addRows(6);
datIntensityMap.setValue(0, 0, 'GB');
datIntensityMap.setValue(0, 1, 100);
datIntensityMap.setValue(0, 2, 1.3);
datIntensityMap.setValue(0, 3, 300);
datIntensityMap.setValue(1, 0, 'US');
datIntensityMap.setValue(1, 1, 300);
datIntensityMap.setValue(1, 2, 1.7);
datIntensityMap.setValue(1, 3, 200);
datIntensityMap.setValue(2, 0, 'CN');
datIntensityMap.setValue(2, 1, 200);
datIntensityMap.setValue(2, 2, 0.8);
datIntensityMap.setValue(2, 3, 400);
datIntensityMap.setValue(3, 0, 'FR');
datIntensityMap.setValue(3, 1, 80);
datIntensityMap.setValue(3, 2, 2.1);
datIntensityMap.setValue(3, 3, 450);
datIntensityMap.setValue(4, 0, 'BR');
datIntensityMap.setValue(4, 1, 300);
datIntensityMap.setValue(4, 2, 0.7);
datIntensityMap.setValue(4, 3, 100);
datIntensityMap.setValue(5, 0, 'MX');
datIntensityMap.setValue(5, 1, 900);
datIntensityMap.setValue(5, 2, 0.5);
datIntensityMap.setValue(5, 3, 120);
var varIntensityMap = new google.visualization.IntensityMap(document.getElementById('divIntensityMap'));
varIntensityMap.draw(datIntensityMap, {width: 718, height:566}); }
google.setOnLoadCallback(funIntensityMap);</script></head>
<body>
<div id="divIntensityMap" style="width: 718px; height: 566px;"></div>
</body>
</html>

Wrapper

The wrapper in the previous section is an example of a web page you might want to write to include serialized data, where you want to separate the data and the chart.  You can download it (googleVisWrapper.html), or use the code below as a starter. You will note it takes 5 arguments. The example application provided generates a command line that can be used directly in a browser as follows

file://d:\googleVisWrapper.html?package=intensitymap&method= IntensityMap&dataurl= file:///D:\googleSerializedData.html&height=402&width=690

<html>
<head>

     <script type="text/javascript" src="https://www.google.com/jsapi"></script>
     <script type="text/javascript">
//skeleton wrapper script provided by www.mcpher.com for use with serialized data generated by googlecharts.xlsm
//free to use for non-commercial use
//excel@mcpher.com for comments, suggestions, improvements 

//-expecting the method, package and the dataurl to be an argument with motionchart default
var qparams = new Array();
var jchartoptions = {};

// default parameters
qparams['method'] = "MotionChart"
qparams['dataurl'] = 'file:///D:/googlechart/googSerializedData.html';
qparams['package'] = qparams['method'].toLowerCase();
qparams['width'] = 400;
qparams['height']= 300;
qparams['title']= "";

// get parameters if any
function getqparams(){
var htmlquery = window.location.search.substring(1);
var htmlparams = htmlquery.split('&');
for ( var i=0; i < htmlparams.length;i++) {
var k = htmlparams[i].indexOf('=');
if (k > 0) qparams[ htmlparams[i].substring(0,k) ] = htmlparams [i].substring(k+1);
}
}
getqparams();
jchartoptions ['width'] = decodeURI(qparams['width']);
jchartoptions ['height'] = decodeURI(qparams['height']);   
jchartoptions ['title'] = decodeURI(qparams['title']);  

// implement google visualization
google.load('visualization', '1', {packages: [qparams['package']]});
google.setOnLoadCallback(jmakequery);
 
       function jmakequery() {
         var query = new google.visualization.Query(qparams['dataurl']);
         query.send(jmotiondraw);
       }
 
       function jmotiondraw(response) {
         if (response.isError()) { alert('Did not get valid JSON data input')
       }
        var jdata = response.getDataTable();
var jchart = new google.visualization[qparams['method']](document.getElementById('jchart_div'));
jchart.draw(jdata, jchartoptions);

//remember to put a jchart_div where you want the chart in the body of the html
      
}
    </script>

  </head> 
 
  <body> 


For help and more information join our forumfollow the blogfollow me on twitter


Comments