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.

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.

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.

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..

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.