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.