Bill French
[email protected]

Geckoboard, which I recently wrote about at iPadCTO, continues to be one of those tools that I can’t stop thinking about.

GeckoDocs

UPDATE: The demand for this example and support necessary to help users become familiar with the integration process has compelled me to charge ten bucks for the code. It’s still a bargain because you get two full days of R&D for the price of a six-pack of tacos. And if that’s too pricey, you can probably glean the approach and replicate it from from the details in this article.

Small businesses, especially in the current economic climate, need all the help they can get and if there are services that can quickly and painlessly improve their ability to manage costs, identify revenue opportunities, or expand awareness to better deal with issues before they become full-blown problems, I like to investigate it fully.

Google Docs has emerged as an ideal cloud service that small businesses and organizations adore. It provides a good alternative to pricey Office suite applications from Microsoft. Geckoboard also falls into favor by small firms because it puts business intelligence and key performance indicators (KPI dashobards) into reach at affordable prices.

Small businesses face challenges leveraging Geckoboard for two key reasons.

1. Making essential business data available as a maintenance-free web service.
2. Creating and hosting Gecko-compliant XML feeds.

The juxtaposition of Google Docs and Geckoboard wormed its way into my brain and eventually led to this hypothesis.

“What if data from Google Docs spreadsheets could be magically transformed into Gecko-compliant XML feeds?”

An Approach

In my attempt to see if this hypothesis could be validated, I stumbled on a number of possibilities that suggested this would elevate the value of Google Docs in a business intelligence context, especially with Geckoboard. The most important test of this hypothesis, is based in this question, for without this possibility, we cannot address key challenge #2.

“Is it possible for a script (inside a spreadsheet) to generate an XML feed that can also be hosted as a web service?”

Ideally, I needed to demonstrate the ability to transform a simple, but real-world Google Docs spreadsheet into a data source for Geckoboard widgets, and without changing the way people work with their spreadsheets. Diagram A [below] shows the example I decided to create; sales data by district and some of the fields, such as average selling price, are calculated.

Diagram A

Let’s skip to the ideal Geckoboard solution outcome – a collection of widgets based on the data in the spreadsheet. The objective is to get to the screenshot in Diagram B with as little effort and complexity as possible. You can see this example dashboard in its live state here.

Diagram B

Alternative Integration Approaches

There is one approach that an integration specialist will typically follow when presented with the reality of Diagram A and the ideal outcome of Diagram B. He will start with the assumption that the data must be exported into a server-based transformation service that can re-sculpt the data to meet Geckoboard XML requirements and hosted in a manner that allows Geckoboard to access it. Furthermore, the service must perform regular updates to keep the data updated and fresh. It’s possible to build custom functions that are registered to execute on a schedule and return XML documents directly into specific cells in the sheet.

As evidenced by my hypothesis, and in keeping true to the objective that this solution work for small companies, I chose a different path.

GeckoDocs Interface Controller (GIC)

My solution starts with the premise that every Gecko-enabled Google Docs spreadsheet requires an interface controller, a GIC. This is simply a table where we describe the Geckoboard widgets the spreadsheet will provide. The table needs just a few fields and it can exist in the same sheet or a separate sheet.

Diagram C [below] shows the example GIC for the ideal outcome in Diagram B [above].

Example GIC – Diagram C

Widget Type, and Data Range are the only functional fields in this table. The former defines the widget type, and the latter establishes data range (or ranges) that the widget will require. It’s important to point out that the range column actually supports non-contiguous ranges. This is extremely important for cases where labels are not in an adjacent column to data elements. Rather than force spreadsheet users to recast their data to accommodate a simple range of labels plus data, I felt compelled to magically address multiple ranges in the supporting script. The XML and Feed URL fields are generated by the GeckoDocs script. More about these a bit later.

The Script

As I mentioned earlier, Google Docs script is very powerful and it can be embedded in any spreadsheet to expand the functionality of the sheet (learn more about Google Apps Scripting). The Google Apps scripting engine is fully integrated into Google spreadsheets. It’s possible to build custom functions that are registered in a chron-process and when they fire, they can return XML documents directly into specific cells in the sheet. Furthermore, cell ranges from the sheet can be passed into scripts or referenced by scripts directly, opening the door for some very useful automation scripts.

Surprisingly, my own GeckoDocs script library is very efficient and relatively easy to implement in the context of existing business spreadsheets.

At a high level, the GeckoDocs script works like this.

1. The function geckoDocs() reads the GIC table
2. Enumerates each item in the table
3. Generates individual Gecko-compliant XML documents for each GIC item
4. Publishes each Gecko-compliant XML document back to the GIC table in the XML column

The cells containing the script-generated XML, can be published as a URL. Wait a second… What? Yes, I’m not kidding – look at the Publish options under Sharing for a spreadsheet and you’ll discover that Google Docs will return the URL for the data in that cell, and only that cell. Jackpot! – we have an XML web service hosting model at our fingertips.

With these two key capabilities, the dots are completely connected. It is possible to use Google Docs spreadsheets instrumented to acquire, manipulate, and publish custom XML web services that Geckoboard can utilize straightaway.

geckoDocs()

As you review my script, know that it can most certainly be improved, streamlined, and hardened. This is an example that is intended to demonstrate how to connect the dots, not how to support every nuance and feature in Geckoboard or to fabricate elegant code.

The geckoDocs() function [diagram D] is simple. It opens the Sales sheet and enumerates across the GIC configuration items. Note – there are two hard-coded items in this script; the sheet name, and the enumeration of the GIC items. I didn’t have time to refactor these for intelligent processing. As such, if you use this script for other sheets, know that these two items must be configured manually.

geckoDocs() Function – Diagram D

The geckoDocs() function calls only one other function, genXML(), for each GIC item and is responsible for creating the Gecko-compliant XML feed that drives its widgets. Rather than walk through every line of this function, let’s start with a look at the code that generates just one of the widget types, the Rag Numbers widget.

Rag Type XML Generator – Diagram E

This part of the genXML() function, shown in Diagram E, senses the widget type (Rag) and creates the appropriate XML blending the data range specified in the GIC range column.

Automatic Updates

Users don’t want to be bothered with remembering to run scripts to update the XML documents in a sheet. One way to avoid this is to set up the geckoDocs() function to be executed every hour or perhaps at intervals suitable to the flow of the data being monitored. Google Docs scripting environment provides the ability to do this easily. Alternatively, you could create an event that causes the function to fire whenever changes are made to the sheet. These options are accessed from the script editor – see Triggers on the main menu.

Published XML Documents

As mentioned earlier, Google Docs sheets and specifically cells, each have a web address for published sheets. The address for a given cell is easily known by selecting the Share | Publish as a web page option in the upper right of the Google Docs application.

This option opens a dialog that allows you to select the sheet, the output of the cell (use Plain Text for XML documents), and the cell itself (D10 in this example).

Note – despite my intentions to automate the generation of the published XML address for each GIC widget configuration, I haven’t completed this task. In the meantime, you’ll need to get the published address for each GIC widget. In the example spreadsheet, I have manually pasted the XML feed addresses for each widget so that you’ll have the complete example to refer to. This is the same address that the widgets will require in the Geckoboard dashboard.

Diagram F

Configuring Geckoboard Widgets

Now that we have an array of Gecko-compliant XML feeds, we can build our widgets. The address shown in Diagram F is all that’s required to create the first widget. The URL data feed field contains the XML feed address shown above.

Summary

It’s obvious from these examples that Google Docs can provide a framework for increased agility and power by blending Geckoboard with existing business data. The example spreadsheet and dashboard was created in less than 40 minutes and demonstrates that an integration plugin doesn’t have to be intrusive on users, or complex in its implementation.

The code required to achieve seamless Geckoboard integration is fairly straightforward and the examples provide a pattern that can be extended with some scripting and XML skill. You can find the Google Apps Script, the example spreadsheet, and the sample dashboard with the purchase of the GeckoDocs starter kit.

If you’re really only interested in the outcome, consider using my comprehensive library of scripts and integration solutions, GeckoDocs, or my consulting services. With this library, I can liberate your Google Docs data to provide key business intelligence to your team or your customers.