Google Visualization API and ColdFusion: Create a Data Table

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:
Google Table

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

Posted in ColdFusion. Tags: , . Permalink. Both comments and trackbacks are closed.

6 Comments

  1. October 20, 2010 at 12:40 pm | Permalink

    @stuttsdc
    Call the external js file a cfm file. For example custom.js.cfm and include it in the page. That way the server will process the CF variables provided you have them in a cfoutput tags.

    <script language="javascript" type="text/javascript" src="custom.js.cfm"></script>

  2. stuttsdc
    October 20, 2010 at 12:30 pm | Permalink

    @jen. Thanks for the feedback. I was actually to figure the bulk of my issue out but the real problem is when I try to take the <script> above and place it in an external file ( <script type="text/javascript" src="geomapscript.js"> as an example.)

    My issue seems to be placement of the <cfoutput> tags you have places around the <script> tags in the <head> above. I can not seem to move these to an appropriate place when the script is moved to it's own .js file.

    Suggesions?

  3. October 20, 2010 at 10:56 am | Permalink

    @stuttsdc

    Long shot, but I think it might be here:

    data.setValue(#currentRow#-1, 0, 'US-' + '#state#');

    I would try:

    data.setValue(#currentRow#-1, 0, "'US-" + "#state#'");

    to get those single quotes around the state designation properly.

    And, do you need an options['dataMode'] for what you are trying to display?

  4. stuttsdc
    October 20, 2010 at 9:03 am | Permalink

    I'm trying to use this as a template to populate the geoMap visualization tool but can not get it to work at all.

    My query..

    <cfquery name="myQuery" datasource="xxx">
    SELECT tblLoc.StateUSAbb as state, Count(tblLoc.StateUSAbb) AS statecount
    FROM …
    GROUP BY …
    HAVING …
    </cfquery>

    and the heart of my code for the map:

    google.load('visualization', '1', {packages: ['geomap']});

    function drawVisualization() {
    var data = new google.visualization.DataTable();
    data.addRows(#myQuery.recordcount#);
    data.addColumn('string', 'state');
    data.addColumn('number', 'statecount');
    <cfloop query="myQuery">
    data.setValue(#currentRow#-1, 0, 'US-' + '#state#');
    data.setValue(#currentRow#-1, 1, #statecount#);
    </cfloop>

    var options = {};
    options['region'] = 'US';

    var geomap = new google.visualization.GeoMap(
    document.getElementById('visualization'));
    geomap.draw(data, options);
    }

    google.setOnLoadCallback(drawVisualization);

  5. EstebanD
    August 24, 2010 at 9:23 am | Permalink

    Great post simple and to the point!
    Thanks!

  6. May 25, 2010 at 3:45 pm | Permalink

    awesome, thank you for this tutorial