Advertisement

Creating Flash Charts From Google Spreadsheets

by

With Google docs gaining popularity, it is anticipated that in the near future there will be a great demand for connecting it to external systems for various purposes such as data interchange, data visualization, etc. In this article, I will show you how to connect Flash based charts (FusionCharts) to Google Spreadsheets and plot live data using JavaScript.

Before I delve into the technicalities involved in retrieving data from Google spreadsheets, I will provide an overview to FusionCharts and begin my explanation from root level – this is for the benefit of those who have come across FusionCharts for first time. A no-restriction version of FusionCharts can be downloaded from www.fusioncharts.com/download. FusionCharts also has a totally free version at www.fusioncharts.com/free; but this article uses a lot of features from FusionCharts v3, so it is recommended to download FusionCharts v3.

All the code associated with this article (and some more examples as well) are present as a download with this article.

Quick Introduction to FusionCharts

FusionCharts is a Flash charting solution that helps you create animated & interactive charts for web pages. FusionCharts is essentially a collection of SWF files which run inside Adobe Flash player, and accept data and configuration settings in XML, thereby making it cross-script and cross-platform compatible. The XML is intuitive and it can be learnt easily with the help of an exhaustive online documentation, which can be accessed from www.fusioncharts.com/docs.

Additionally, if you're not a die-hard fan of XML, FusionCharts comes with an extensive range of API’s, which help in its implementation with all forms of web programming technologies such as ASP, ASP.NET, PHP, Ruby on Rails, Python etc.

Quick Example of Building a Chart with FusionCharts

FusionCharts SWF’s can be embedded in a HTML page either using the <object>/<embed> tags or through FusionCharts JavaScript class. Using the JavaScript class for embedding the chart is advisable, as this ensures that the charts will function smoothly on all browsers. The XML data source can be provided to the chart either as an external file, or it can be directly incorporated within the HTML page.

Let's quickly build a chart to depict the data shown in the table below.

Appliance

Units in Watt

Lighting

1200

Heater

1800

Desktop

1500

Cooler

1200

Elevator

1500

Microwave Oven

1600

Television

1500

Washing Machine

2800

Music System

2200

Cleaners

600

The following piece of code constitutes the XML data source pertaining to the data table above:

Quite simple and intuitive - isn't it? The <chart> element is the root element that contains visual and functional configuration of chart. Each <set> element represents a data item in the chart. This data structure pertains to single series charts in FusionCharts, where you've only dataset. Multiple data-sets call for multi-series XML format, which involves a couple more XML elements.

Now that the XML is ready (we've saved it as myXML.xml), we need to embed the chart into an HTML page with the help of the following code:

The code (above) creates a DIV named firstChartDiv which will serve as a container for the chart. Up next, an instance of the FusionCharts object is created using JavaScript and following parameters are passed into it.

  • Name of the SWF file to be used (Pie3d.swf in this case)
  • Name of the Chart (firstChartId in this case)
  • Height of the chart in pixels
  • Width of the chart in pixels
  • >

The subsequent line of code provides the name of the external XML data source file (myXML.xml in this case). And the final line of the JavaScript code indicates the name of the container DIV in which the chart will render. While using the JavaScript class for embedding the chart, it is necessary to create a container DIV for the chart.

The following chart is generated by the code. The image belows shows a static version; the actual chart is animated and allows interactivity like slicing of pies, rotation of chart, tool-tips etc.

Now, that we have covered the process of creating charts using static data source, let's move on to the process of rendering a chart using data obtained from Google spreadsheet.

Connecting these charts to Google Docs Spreadsheet

Google Docs spreadsheets transfer data in form of JSON feeds. So, in order to render charts using data obtained from Google spreadsheet, it is important to convert the acquired JSON feeds into XML format as required by FusionCharts.

There are multiple ways to convert JSON into XML. Either, you could write your own code to take each JSON element and convert into XML. Or, you could make use of rule based engines like JsonT to aid in this process. We'll use JsonT for this article, as it helps us do things easier and quicker. JsonT can be downloaded from http://goessner.net/articles/jsont/

Using JsonT one can easily define a rule to convert JSON data into a string of desired format. Following is an example of JSON data:

The following code converts the JSON data (displayed abve) into FusionCharts XML format and then creates a chart out of it.

For the code to work, it is essential to link jsont.js JavaScript file to the HTML page.

The code (above) builds XMLstring from JSON data with the help of a rule, that iterates through each key-value pairs of JSON data and treats key names as categories and values as data values for the chart. You may read more on JsonT rules at http://goessner.net/articles/jsont/. Next, we use that XML and build the chart.

In order to use a Google spreadsheet as a dynamic data source for the chart, it is essential to publish it for public viewing. At the time of publishing the document for public viewing – Google generates a unique key. This key is used for requesting data feeds from Google; hence it is important to preserve the key.

Please note that, in order to work with Google's JSON feeds it is essential to modify josnt.js slightly. I have made minor modifications to jsont.js in order to prevent the conflict that arises due to Jsont.js’s use of $ as an object pointer and Google generated JSON feed's use of $ as property name. I have modified Jsont.js so that it uses # as an object pointer.

For this demonstrative example I created a Google spreadsheet, which included three sheets – each of which, contained yearly sales data for the years 2006, 2007 and 2008. The spreadsheet can be viewed through the following link http://docs.google.com.

    
   

The process of creating a Google spreadsheet

The following code will help you render a chart that is plotted using data drawn from Google spreadsheet:

Here's how the code works:

  1. The program initiates by declaring a variable named ‘sheets’ for storing the array of spreadsheet definitions obtained from the specified Google Spreadsheet. It then declares the ‘sheetCount’ counter variable which keeps track of number of spreadsheets from which data has been received. This counter ensures that the chart renders, once all the data has been received.

  2. Up next, the ‘chartConfigJSON’ object is declared to store the chart configuration settings.

  3. The XML that would be gradually built, will be stored in an array named ‘strXML’. When XML building process is finally complete, the elements of the array would be joined to form the XML string.

  4. Moving on, the initpage() function placed in the body of the HTML is called when the onload event occurs. This function in turn calls the getGoogleSpreadsheetData() which requests for the spreadsheet data in form of JSON feeds. The getGoogleSpreadsheetData() function requests for the spreadsheet data by sending the Google generated spreadsheet key, it also specifies the feed format and handles the feed once it has been received.

  5. Once this feed has been received, a callback function parseSpreadsheet () is fired. This function gets the spreadsheet name from jsonsheets.feed.title.$t and then it loops through each spreadsheet entry. Each spreadsheet entry provides feed links to all available feed types (spreadsheet/list/cell/gViz) in the link array. After acquiring a list of feed type, getGoogleSpreadsheetData() function requests for JSON feeds from each spreadsheet.



    The link[0].href element contains the URL for available feed types, which is passed to the function. After the feed has been received the callback function parsespreadsheet is called and JSON data is passed to it.

  6. Once the feed for each spreadsheet is received, the sheetCount is increased by 1.
  7. Feeds obtained from each spreadsheet would be regarded as a dataset for the chart. Hence, the feed title is taken as the series name for each dataset.
  8. The configuration settings of the chart are stored in strXML[0]. Please note that the function parsespreadsheet() would be called multiple times for as many times as the number of spreadsheets contained within the Google spreadsheet. The code contains a condition which prevents further repetition of this process.
  9. Chart categories are defined using the first spreadsheet. The list of category name is built using jsonT.
  10. Finally, jsonT is called into action to populating the dataset values.:
  11. When this code is executed, the following chart would be generated in your page.

The multi-series chart created using data obtained from Google spreadsheet document

Now that we've been able to generate a chart, let's take it a step further and add the ability to export it as images or PDFs. This will be very useful for emailing to your colleagues.

PDF Export

The latest version of FusionCharts (v3.1) provides comprehensive range of export features. You can export the charts as PDFs, PNGs or JPEGs - both at client-side or save them on server as well.

In our example, since we're not using any server-side scripts, we'll stick to client side export. At client side, the export feature is implemented with the help of FusionCharts Client-side export components - collective name given to FusionChartsExportComponent.js and FCExporter.swf. FCExporter.swf is the client-side export engine that allows charts to be exported. The JavaScript file provides an interface between the chart and the FCExporter.swf.

This JavaScript class facilitates rendering of the FCExporter.swf, which is the engine for export and also serves as a graphical user interface. With the help of export API's you can easily customize the appearance of the GUI. The API's also enable you to control the functional aspects of the GUI. The following code illustrates the process:

The code (above) configures the export GUI so that, it displays a button which triggers the export process. Additionally, each chart would be listed in the GUI panel and the user would be presented with an option to export all the charts into a single PDF file. The GUI will also allow the user to change the default export format from PDF to jpeg or png.

In order to demonstrate the export process, I have added the code for configuring the export GUI, to the code that renders a chart based on data derived from Google spreadsheet document. However, I have made some modifications to the code, so that it will now render three single-series charts instead of a single multi-series chart. Each chart will displaying data pertaining to a particular spreadsheet.

Following is the combined code:

Following is the depiction of the export process as seen from the client side:

  • The charts render in the browser.
  • Charts are converted to bitmap when 'Export As PDF' button is clicked.
  • The export GUI displays a list of charts to be exported.
  • The charts are saved into a single PDF file when the user clicks the 'As Single PDF' button.
  • And with that, we end this article. There are a lot more features offered by FusionCharts that can potentially be put into use. Creating charts from data stored in Google spreadsheets is just one example of innovations which are possible with FusionCharts. Thanks for reading!