Google provides a growing number of cool charts that are not available in Excel. For this article we are going to focus on the the Google Motion Visualizations. Please take a look here to become familiar with what they are.
There are a number of ways to create a google chart, some which are;
In this article we will look at how to create a web page directly from Excel, and embed it in your excel spreadsheet. The web page created can also be loaded on to a web site, as per this example here. As usual a fully functional workbook with all the code is available in the downloads section.
Google Motion is one of a family of visualization capabilities available through the Google Visualization API. If you are using googledocs, it is quite straightforward to include in your spreadsheet, just as it is simple to create a webpage with an embedded chart that takes its data from your googledocs spreadsheet.
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.
We are going to write a handler in excel that will convert spreadsheet data, that looks like this in our example
....to an html file that looks like this
At least 3 columns of data are required, the first being what's called the entity, the second a date, and the third would likely be a number. Subsequent fields would also be numbers and would be available interactively through the chart. The downloadable example does not validate this but a production example probably should, otherwise you could leave it to the chart to complain about the data type if there is a problem.
To get you started a downloadable workbook will provide all the necessary code, including a form to control the process and display the finished product. This is the tool in action. You simply select the range where the column headings are and push the make the chart button. If you need to load the result to a web page, the temporary file (googMot.html) can be found in the directory you specify on the form as being trusted to Flash (and for which you have previously followed the instructions on Flash trusted locations above)
You will find a number of classes in the workbook that you should just be able to use as is in whatever application you plan to develop, and which I'll go into later. For the example, the main module is pretty straightforward, and yours should be too.
This is called from the form as follows on the appropriate command button click.
Whats happening here is
There are a number of classes provided, one of which is the topic of this article namely cGoogleChartInput. This makes use of a set of other custom classes, cCell, CDataColumn, cDataRow, cDataSet, cDataSets and cHeadingRow. These are a suite of classes that allow the separation of physical spreadsheet location and data manipulation to be separated and encapsulated, thus simplifying the coding for this and other processes. They are not the subject of this article but you can read about them here.
This is the class that does all the work. For future articles I will be extending it to deal with other google visualization capabilities, but in this example only the motion chart has been implemented. All the code for this in the example workbook. We will just look at selected methods.
Lets take a look at the .init method of this class. You will notice an optional argument headOrderArray. This is to allow you to vary the order of the columns as supplied by the range rWhere. If you recall, Google has rules about the first 3 columns, so if your data doesn't happen to be in the right order, this allows you to modify the order, or omit columns, without re-arranging your spreadsheet. A typical argument might be Array("Function","Load Date","Volume"). If it is omitted then all the columns in the range will be used in the natural order.
Another item of interest here is that we are reading in the data here, as well as setting up the parameters, using the .getdata method.
The getdata method uses the data manipluation classes mentioned earlier, so collecting the data to be transformed is a simple matter of instantiation of a cDataSet class, followed by a call to its .populateData method.
The only other Public method, aside from .init is .createmotionFile, which actually generates the html and javasscript required to execute the google chart. Again you will see the use of the cDataSet, cCell and cDataRow class to traverse the data previously read by .populateData.
The only public property available is htmlName, which returns the name of the file that has been generated by .createmotionFile.
This kind of technique opens the growing world of google charts and visualizations to excel users who do not want to use googledocs. Watch this space for future articles that serialize Excel data so it can be used as input to embedded google gadgets, as well as further implementations of additional google visualization. You can download the workbook associated with this code and other useful items here. All code is freely available for non commercial use. Please feel free to contact me with suggestions, updates, comments, bug fixes or code enhancements.
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Visualization >