Serializing Excel data for input to any Google visualization

In a previous article, I covered how to embed a google motion chart in your Excel sheet. Here we will look at generalizing that - how to embed any google visualization. However, you may not want to embed in your Excel sheet. You may want to serialize your Excel as input to a web page or gadget. In this article I will cover both topics and provide a downloable tool to illustrate how it is done, as well as skeleton javascript you can use in any web page to take your serialized Excel data as input.

What are google visualizations, and what have they to do with Excel

Google have created a series of APIs which can be used by anyone to create a growing range of charts and other visualizations. The range of prewritten cool charts and visualizations, many of which are interactive, is growing rapidly. You can take a look at their gallery here. Users of GoogleDocs can insert these into their worksheets in much the same way as you can insert regular charts in Excel. Additionally, a growing number of gadgets are available for those building their own websites or blogs using Google Sites and other tools. Plugging in Excel data to these other presentation mediums, or conversely to bring those capabilities into Excel is not immediately straightforward nor intuitively obvious. The purpose of this article is to build on the topic covered previously in this forum, where I looked at embedding google motion charts in Excel. As usual, there will be a fully functional downloadable tool which I will use to illustrate the techniques involved. I also provide a javascript wrapper which you can use as a skeleton for a web page embedding google visualizations. It will use your serialized Excel data as input.

A note about Flash
Google uses Flash to display these interactive charts. By default the Flash player is not able to access files that are held locally. Since you will be embedding these charts in your workbook, the .html file will be created locally and will need to be accessed locally . To get round this you need to first follow the instructions on the macromedia website for creating a trusted location for local, flash enabled files. The excel form in the downloadable example gives you the opportunity to define where that is so that all .html files are created there.

googleVisWrapper.html Structure

To get an grounding on how google visualizations work, we should first take a look at the example skeleton javascript. one of the useful attributes of Google Visualizations is that they all have roughly the same structure, so this generalized script can take a few arguments and create any kind of existing or future chart.

<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

//-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>
<div id="jchart_div"></div>
</body>
</html>


Each visualization has a 'package' which is an argument to google.load() and is implemented as google.visualization.xxxxx. Aside from that there are a few options like height and width implemented. The callback picks up the serialized Excel data, which has been formatted using the google 'wire' protocol - essentially JSON.

The call to this wrapper then, would include the arguments necessary to defined a few options, the type of visualization required and the location of serialized data. This call is generated in the calling form from with Excel should you wish to copy and paste it into a browser, and looks like this.

file://d:\googlechart\googleVisWrapper.html?package=intensitymap&method=IntensityMap&dataurl= file:///D:\googlechart\googleSerializedData.html&height=372&width=690&title= Template%20created%20by%20www.mcpher.com


Serialized plus Wrapper versus Embedded

Not only are the calls to the visualization APIs standardized such that it is possible to use a generalized script with a few parameter changes to load and execute them, but the serialized data format is also standard across visualizations. This means that the serialization of the data can be independent of what it's going to be used for. It is possible to create a script containing both the data and the script to execute the visualization. This is exactly what the provided tool does when embedding a chart inside Excel. It also produces just the serialized data, as below; and it is this that can be used as input to googleVisWrapper.html - or indeed to any Google Gadget or visualization script.

google.visualization.Query.setResponse({version:'0.6',reqId:'0',status:'ok',table:{
cols:[{id:'A',label:'Country',type:'string'},{id:'B',label:'No of Employees',type:'number'},{id:'C',label:'PC per employee',type:'number'},{id:'D',label:'Cost per Employee',type:'number'}],
rows:[
{'c':[{v:'GB',f:'GB'},{v:100,f:'100'},{v:1.3,f:'1.3'},{v:300,f:'300'}]},
{'c':[{v:'US',f:'US'},{v:300,f:'300'},{v:1.7,f:'1.7'},{v:200,f:'200'}]},
{'c':[{v:'CN',f:'CN'},{v:200,f:'200'},{v:0.8,f:'0.8'},{v:400,f:'400'}]},
{'c':[{v:'FR',f:'FR'},{v:80,f:'80'},{v:2.1,f:'2.1'},{v:450,f:'450'}]},
{'c':[{v:'BR',f:'BR'},{v:300,f:'300'},{v:0.7,f:'0.7'},{v:100,f:'100'}]},
{'c':[{v:'MX',f:'MX'},{v:900,f:'900'},{v:0.5,f:'0.5'},{v:120,f:'120'}]},
]}});

The data above is created as googleSerializeddata.html and returned as a response to google.visualization.query as below

function jmakequery() {
var query = new google.visualization.Query(qparams['dataurl']);
query.send(jmotiondraw);
}

The combination of the wrapper script with it's parameterized call and the serialized data then is equivalent to the embedded script, googleEmbedded.html which is also created by the tool.

<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', '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: 690, height:372,title:"Template created by www.mcpher.com"}); }
google.setOnLoadCallback(funIntensityMap);</script></head>
<body>
<div id="divIntensityMap" style="width: 690px; height: 372px;"></div>
</body>
</html>

Implementation in Excel


So let's take a look at how all that is implemented in Excel; in other words, how to create these scripts from spreadsheet data. If you take a look at the tool you will see that there are a number of tabs. One for each type of visualization implemented. Although the data serialization is standard, clearly some type of charts need particular types of data to make sense. I have included a link on each worksheet to the documentation for each type of chart so you can read up on what data goes best with what chart. Here is what the form looks like.


And these are the files created by this form execution



Adding new visualizations

Let's use the task of adding a new visualization to see how this works. We are going to add a Table visualization; this would provide a simple way of including dynamic data from your Excel sheet in a web page for example.

The custom class cGoogleChartInput does most of the work, and contains an eNum of the all the visualizations currently implemented. The first task is to add one for a Table as below

Public Enum eGoogVis
  eGoogVisUnknown
  eGoogVisIntensityMap
  eGoogVisPieChart
  eGoogVisImageAreaChart
  eGoogVisAreaChart
  eGoogVisImageBarChart
  eGoogVisMotion
  eGoogVisTable
End Enum

We then need to define the package and method that implements a Table. These are table and Table. (generally, but not always, the package is simply the method in lowercase), so we need to update the properties of cGoogleChartInput that return the package and method. Since the the package naming convention is standard we dont need to change that.

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 eGoogVisPieChart
      gvMethod = "PieChart"
    Case eGoogVisImageAreaChart
      gvMethod = "ImageAreaChart"
    Case eGoogVisAreaChart
      gvMethod = "AreaChart"
    Case eGoogVisImageBarChart
      gvMethod = "ImageBarChart"
    Case eGoogVisTable
      gvMethod = "Table"
    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, except for exceptions
  Select Case pGoogVis
    Case eGoogVisAreaChart
      gvPackage = "corechart"
    Case Else
      gvPackage = LCase(gvMethod)
  End Select
End Property

That is all that's required in the cGoogleCharInput class. We need now to update the control form, fGoogleChart to add Table as an option.

Private Sub UserForm_Initialize()
' visualizations implemented
  Me.cbTypeofChart.AddItem "Motion Chart"
  Me.cbTypeofChart.AddItem "Intensity Map"
  Me.cbTypeofChart.AddItem "Pie Chart"
  Me.cbTypeofChart.AddItem "Image Area Chart"
  Me.cbTypeofChart.AddItem "Area Chart"
  Me.cbTypeofChart.AddItem "Image Bar Chart"
  Me.cbTypeofChart.AddItem "Table"

' default height/width as per web control
  Me.tbHeight = Me.WebBrowser1.Height
  Me.tbWidth = Me.WebBrowser1.Width

End Sub

Private Function vtypeSelected() As eGoogVis

  Select Case Me.cbTypeofChart.Value
    Case "Intensity Map"
      vtypeSelected = eGoogVisIntensityMap
    Case "Motion Chart"
      vtypeSelected = eGoogVisMotion
    Case "Pie Chart"
      vtypeSelected = eGoogVisPieChart
    Case "Image Area Chart"
      vtypeSelected = eGoogVisImageAreaChart
    Case "Area Chart"
      vtypeSelected = eGoogVisAreaChart
    Case "Image Bar Chart"
      vtypeSelected = eGoogVisImageBarChart
    Case "Table"
      vtypeSelected = eGoogVisTable
    Case Else
      vtypeSelected = eGoogVisUnknown
  End Select

End Function

And we are done. The new visualization Table, will now be availble both as an embedded chart and as a standalone wrapper + serialized data option. Here's how it came out in a browser


Summary

You can find all the code behind this here downloadble tools which I hope you can find a use for as the basis for your own application. The standardization of design of google visualizations make it easy to add new ones, as demonstrated above, and you may need to add more formatting options to the controlling form. As always I welcome suggestions, improvements, questions and bug fixes at excel@mcpher.com. All code is free to use as you wish for non commercial use. In future articles on this subject I will dig a little deeper into the interactivity offerred by Google visualizations.



By bruce mcpherson   Popularity  (4423 Views)