This is a guest post by Bill French, Sr Editor of iPhoneCTO and founder of iPadCTO, and MyST Technology Partners. Bill provides training and strategic industry briefings to c-level executives who are planning and deploying iPad and iOS devices in their enterprises. He also provides innovative services to help companies with unique technology challenges and is heavily involved with providing XML web services for content management and syndication. You can reach Bill at firstname.lastname@example.org.
This article was originally published on the iPadCTO blog but Bill kindly allowed us to reproduce it in full here.
Note from Geckoboard: This article is in 3 separate stages:
STAGE 1 - Creating the spreadsheet.
The first step is to create the spreadsheet you would like to use for the tutorial. I used the example spreadsheet which can be found here:
STAGE 2 - Creating the script
The second stage here is to create the script that will transfer your Google Docs spreadsheet into an XML file that Geckoboard can read. This can be copied and pasted from here:
STAGE 3 - Publishing to the web.
The final stage in this process is publishing the XML documents to the web. Details about this are explained at the end of this blog post under the heading "Published XML Documents".
An example of the entire spreadsheet, code and example dashboard can be found here:
And now, back to the guest post:
Geckoboard, which I recently wrote about at iPadCTO, continues to be one of those tools that I can’t stop thinking about.
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.
- Making essential business data available as a maintenance-free web service.
- 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?”
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.
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.
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 chron-based updates to keep the data updated and fresh.
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].
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.
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.
- The function geckoDocs() reads the GIC table
- Enumerates each item in the table
- Generates individual Gecko-compliant XML documents for each GIC item
- 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.
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.
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.
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.
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.
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.
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 links right here.
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.