In a previous section , I explained how to create javascript to create a Google Motion Chart directly from your from Excel data, and optionally embed it in your workbook. Since it is of course possible to embed a Google chart gadget in a web page or a Google document, and to provide it with the address of the data stream , there may be times that you would rather configure the chart, and simply provide it with data from your workbook. This article shows how to serialize your excel data for this scenario, and of course contains a fully functional downloadable application to demonstrate these techniques. It is recommended that you read the section Embedding Google Charts in Excel before this one, and at the very least, read the section on running flash enabled scripts if you plan to run this locally.
Background
Google provides a number of very smart API’s to create an array of charts. We are going to focus on the Motion Chart; a bubble chart (a 3 dimensional chart), with the extra dimension of Time. In this section we will update the work previously done to serialize your Excel data into a format that can be used as input to a Google Motion Gadget. This is a simple operation if you are using googleDocs. The intention with this article and code contribution is to make it easy for Excel users also.
What you will be able to do with the downloadable example
The Download contains
- Test Excel Data used to create the examples on this page, to show you the formatting expectations
- A form with the opportunity to select the headings of the range of data you want to chart. All data up to the first blank line will be charted.
- A webbrowser object on the form that will display the embedded Google Motion Chart created from your data.
- All the code and classes required to build your own application
- The opportunity to select the destination for the standalone .html version of the script, as well as the serialized data that can be used as input to the gadget or web page of your choice.
- A wrapper web page containing a google chart visualization you can use to model your own, showing how to include the serialized data.
On execution of the form. the default action is that the motion chart will be plotted. You will also find the following files created in the directory you specify as being ‘trusted to Flash’, which you can either run locally (assuming you have followed these preparatory steps to enable local Flash) , or load to a web site.
- Googmot.html – a standalone html file containing both your spreadsheet data and chart script that can be posted as a web page. as covered in Embedding Google Charts in Excel.
- GoogmotSerializedData.html. – the response object to a query.send request containing your spreadsheet data in JSON format. This can be used as the input data URL to a wrapper .html file such as the one in the downloable package, or that can be specified in the Data URL property of a Motion Chart gadget in a web page or a googleDocs spreadsheet. This serialization emulates the response that a googleDocs spreadsheet would provide to a request for data.
Embedding a chart from sheets
Depending on which method you were using, you’d either embed the chart from sheets or use a chart gadget this way you would bring up the properties box and add a reference to a googledocs spreadsheet in the datasource URL box. That reference would look something like this. Note that Google has now removed gadgets from sites therefore you may have to find an alternative to gadgets. Below, you will see how to code it directly in your web page. A skeleton wrapper is provided in the downloads section, and is reproduced below. You just need to change the dataurl variable to point to wherever you have posted the serialized data. The remainder of this article is about how to create the serialized data.
<html>
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
//--change this to the address of the serialized data
var dataurl = '........';
//---------------------------------------------------
google.load('visualization', '1', {packages: ['motionchart']});
google.setOnLoadCallback(jmakequery);
function jmakequery() {
var query = new google.visualization.Query(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.MotionChart(document.getElementById('jchart_div'));
jchart.draw(jdata, {width: 400, height:300});
//remember to put a jchart_div where you want the chart in the body of the html
}
</script>
</head>
<body>
<div id="jchart_div" style="width: 400px; height: 300px;"></div>
</body>
</html>
Serialized data FormatData serialized for a google motion chart essentially means creating a JSON formatted string from your excel data, and would typically be in an html file that is streamed to a google motion chart gadget from a website location. In our example workbook the generated file looks like this.
google.visualization.Query.setResponse({version:'0.6',reqId:'0',status:'ok',table:{
cols:[{id:'A',label:'Function',type:'string'}, {id:'B',label:'Load Date',type:'date'},{id:'C',label:'Differentiator', type:'number'},{id:'D',label:'Complexity',type:'number'},{id:'E',label:'Volume',type:'number'}],
rows:[{'c':[{v:'Box C',f:'Box C'},{v:new Date(2009,8,7),f:'7-Sep-09'},{v:40.34,f:'40.34'},{v:1.35,f:'1.35'},{v:47,f:'47'}]},{'c':[{v:'Box F',f:'Box F'},{v:new Date(2009,8,7),f:'7-Sep-09'}, {v:32.05,f:'32.05'},{v:0.95,f:'0.95'},{v:120,f:'120'}]},
... repeat many times ....
{'c':[{v:'Box S',f:'Box S'},{v:new Date(2009,8,2), f:'2-Sep-09'},{v:30.26,f:'30.26'},{v:1.2,f:'1.2'},{v:259,f:'259'}]},
]}});
This section will show how the code previously implemented has been enhanced as below, with an extra optional argument – the name of a file in which to create the serialized data (in this case fGoogleChart.tbTrusted.Value & “googmotSerializedData.html”). If specified, then it will create a data file in addition to the regular .html file containing the API code. This will allow you to provide this file as the data file URL parameter in an embedded Google chart gadget.
Public Sub showChart()
Dim GoogMot As cGoogleChartInput, rHeadings As Range
'create the chart Set GoogMot = New cGoogleChartInput
If Len(fGoogleChart.RefEdit1.Text) > 0 Then
Set rHeadings = Range(fGoogleChart.RefEdit1.Text)
With fGoogleChart
If GoogMot.init(fGoogleChart.tbTrusted.Value & "googmot.html", _ rHeadings, , _ .Width, .Height, _ fGoogleChart.tbTrusted.Value & "googmotSerializedData.html") Then
' create a standalone chart
GoogMot.createmotionFile ' create serialized data for input to a motion gadget
GoogMot.createserializedData ' show the chart
If .cbShowonForm.Value Then
.WebBrowser1.Navigate GoogMot.htmlName
Do
DoEvents
Loop Until .WebBrowser1.ReadyState = READYSTATE_COMPLETE
End If
End If
End With
Else
MsgBox ("Please supply a range where the column titles are")
End If
End Sub
Code to serialize Excel data
The cgoogleChartInput class will create this serialized data file as follows. You will note a reference to various custom classes which abstract the data from the spreadsheet. These are not the subject of this article, but you can find out more about them here. A selection of code is reproduced here, and you can look at the example workbook for the full code..
Public Sub createserializedData()
Dim s As String, hcell As cCell, dcell As cCell, sr As String, dr As cDataRow, hand As Integer
' generate html file with the serialized data to allow reference by a google chart gadget
If Len(pSerializedDataName) > 0 Then
If createSerializedDataFile Then
hand = pSerializedDataHandle
Print #hand, motionSerializationStart & "{"
' column headings
Print #hand, ColumnHeadings & ","
Print #hand, "rows:["
For Each dr In pdSet.Rows
sr = ""
For Each hcell In pHeadOrder
Set dcell = dr.Cell(hcell.Column)
sr = sr & "{v:" & getTextforType(dcell) & ",f:'" & dcell.toString & "'},"
Next hcell
If Len(sr) > 0 Then
sr = Left(sr, Len(sr) - 1)
End If
sr = "{'c':[" & sr & "]},"
Print #hand, sr
Next dr
Print #hand, "]}});"
Close #hand
End If
End If
End Sub
Summary and next steps
Since this is a trivial processing activity, the sample application as written will always create a standalone .html file to create the chart, as well as the serialized data. To use the serialized data in a web based Google gadget, you simple need to load the creates file “googmotSerializedData.html” to a web server, and to provide that location to your already created Google Charts Gadget.
In a future article I will enhance these classes to make enable additional Google visualizations, as well as a discussion on how to automate the ‘initial state’ (set the default options) of a google visualization gadget (this is tricky) so watch this space. As usual, you can find the example application and all the necessary classes hereDownloads, all of which are freely available for non commercial use. I welcome your feedback, questions and enhancement contributions via our forum.