Serializing Excel data for Google motion charts

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
  1. Test Excel Data used to create the examples on this page, to show you the formatting expectations
  2. 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. 
  3. A webbrowser object on the form that will display the embedded Google Motion Chart created from your data.
  4. All the code and classes required to build your own application
  5. 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.
  6. 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.
  1. 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 . 
  2. 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.

Chart1


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=0At2ExLh4POiZdGdMU3VKS01pcGNJWTJkX0ZPa1MxaUE

However, 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 


The google gadget xml can be found at http://www.google.com/ig/modules/motionchart.xml, or if you are using google sites to develop your web page, it can be inserted directly from the insert gadgets menu item.

Alternatively, you can 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 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.

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 here, all of which are freely available for non commercial use. I welcome your feedback, questions and enhancement contributions via our forum.

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.