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
- 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.
- 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.
What does a Google motion chart look like
In this case we are going to create a serialized data file that can be used as input to a Google Gadget, that will look like the example below. Press the play button to see the time dimension.
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.
https://spreadsheets.google.com/a/mcpher.com/tq?authkey=CLrAif0G&range=A1:E738 &gid=5&key=0At2ExLh4POiZdGdMU3VKS01pcGNJWTJkX0ZPa1MxaUEHowever, since we are not using GoogleDocs, we are going to have to simulate the response that GoogleDocs would give in the form of an html file containing the serialized data from your Excel tab. You can then load that to a web site (or indeed keep it locally), then simply reference that address in the data source Url of the gadget
<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 datavar 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 Format
Data 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.
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 IfCode 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 IfEnd 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 here, all of which are freely available for non commercial use. I welcome your feedback, questions and enhancement contributions via our forum.