The Google Visualization API is fun to play around with and it’s quite powerful. There are a number of charts and graphs to choose from and filling them up with data from ColdFusion is a snap. At least it was for this table example here. Most of this code comes straight out of Google’s documentation which was pretty good and easy to follow.

First things first…get some data. For simplicity sake, a query object is created and populated within a cfscript tag. In the real world you would pull this from a database.

<cfscript>
bookQuery = QueryNew("LastName,FirstName,Title,ISBN,PurchaseLink");
newRow = QueryAddRow(bookQuery);
QuerySetCell(bookQuery, "LastName", "Clifton" );
QuerySetCell(bookQuery, "FirstName","Brian" );
QuerySetCell(bookQuery, "Title","Advanced Web Metrics with Google Analytics" );
QuerySetCell(bookQuery, "ISBN","0470253126" );
QuerySetCell(bookQuery, "PurchaseLink","<a href='http://www.amazon.com/dp/0470253126?tag=jensbits-20&camp=14573&creative=327641&linkCode=as1&creativeASIN=0470253126&adid=0B10Q2FF6KHJB6ATNA5C&'>Amazon.com</a>" );
newRow = QueryAddRow(bookQuery);
QuerySetCell(bookQuery, "LastName", "Gilmore" );
QuerySetCell(bookQuery, "FirstName","W. Jason" );
QuerySetCell(bookQuery, "Title","Beginning PHP and MySQL: From Novice to Professional, Third Edition" );
QuerySetCell(bookQuery, "ISBN","1590598628" );
QuerySetCell(bookQuery, "PurchaseLink","<a href='http://www.amazon.com/gp/product/1590598628?ie=UTF8&tag=jensbits-20&link_code=as3&camp=211189&creative=373489&creativeASIN=1590598628'>Amazon.com</a>" );
newRow = QueryAddRow(bookQuery);
QuerySetCell(bookQuery, "LastName", "Forta" );
QuerySetCell(bookQuery, "FirstName","Ben" );
QuerySetCell(bookQuery, "Title","Adobe ColdFusion 8 Web Application Construction Kit, Volume 1: Getting Started " );
QuerySetCell(bookQuery, "ISBN","032151548X" );
QuerySetCell(bookQuery, "PurchaseLink","<a href='http://www.amazon.com/gp/product/032151548X?ie=UTF8&tag=jensbits-20&link_code=as3&camp=211189&creative=373489&creativeASIN=032151548X'>Amazon.com</a>" );
</cfscript>

Then a little query of a query so we have data to work with.

<cfquery dbtype="query" name="qryBooks">
SELECT * FROM bookQuery ORDER BY LastName
</cfquery>

Now for the magic. Load up Google’s API script.

 <!--Load the AJAX API-->
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>

And, load the visualization package for what you are creating.

// Load the Visualization API and the table package.
  google.load('visualization', '1', {'packages':['table']});

Set the callback and load the callback function with data. In the drawVisualization function ColdFusion sets the number of rows with the query recordCount and loops through the query records and fills each row with data. The setCell method uses row and column coordinates to set the data. The rows start at 0 (zero) and since ColdFusion likes to start at 1 (one), we have to subtract 1 from the currentRow to get the row correct for the setCell method.

All single quotes need to be escaped. That can be done easily with the ColdFusion Replace function.

Create the table visualization object and call the draw method on it. The allowHtml option is set to true so the links work in the generated table.

  // Set a callback to run when the API is loaded.
  google.setOnLoadCallback(drawVisualization);

  // Callback that creates and populates a data table
  function drawVisualization() {
  	  var dataTable = new google.visualization.DataTable();
  	  dataTable.addColumn('string', 'Last Name');
  	  dataTable.addColumn('string', 'First Name');
  	  dataTable.addColumn('string', 'Title');
  	  dataTable.addColumn('string', 'ISBN');
  	  dataTable.addColumn('string', 'Buy Book');
  	  dataTable.addRows(#qryBooks.recordCount#);

  <cfloop query="qryBooks">
      dataTable.setCell(#currentRow#-1, 0, '#LastName#');
      dataTable.setCell(#currentRow#-1, 1, '#FirstName#');
      dataTable.setCell(#currentRow#-1, 2,'book-title', '#Title#', {style: 'font-weight:bold;'});
      dataTable.setCell(#currentRow#-1, 3, '#ISBN#');
      dataTable.setCell(#currentRow#-1, 4, '#Replace(PurchaseLink,"'","\'","all")#');
  </cfloop>

  //Create the table visualization object and call the draw method on it.
  var table = new google.visualization.Table(document.getElementById('table'));
  table.draw(dataTable, {allowHtml: true});

}

Stick a div in the body as the target for the table.

 <body>

	<!---   Div to hold the table   --->
    <div id="table"></div>

  </body>

You’ll end up with a table with click to sort columns that looks like this:

This example just uses one short page of code so give the Google Visualization API a try. It’s an easy way to show data and trends.

Download code