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.
GeckoDocsUPDATE: 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.
- 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?”
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.
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 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].
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.
- 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.
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.
This is very useful. Thanks.
Any idea how to secure the information? I don’t want just anyone to be able to view the xml published from my spreadsheet, just my geckoboard.
Dave,
>>> security? <<<
Very good question. I've thought about this a lot and while I'm no security expert, I believe the key is is literally related to the "key". In each Geckoboard dashboard there's a field for API Key. I believe this key will provide the needed methodology for connecting to Google Docs URLs using basic HTTP authentication. I haven't tried this, but I believe it will work.
Another option (if it works for your situation) is to whitelist certain IP addresses. Read only dashboards have URLs which are hard to guess but for an extra level of security they have allowed you to restrict the access to these URLs by using a white list. If you're jumping around across a wide spectrum of dynamically assigned DNSs, this might not be ideal. The whitelist field for a dashboard is in the dashboard config area, not in each of the widgets.
I did verify that if a sheet is unpublished with the “stop publishing” button, all the URLs to the XML feeds are still valid, they just cannot be accessed without authenticating into Google Docs. I suspect a request to such XML URLs will work if the header passes in the API key for your Google Docs account. Pure speculation on my part (at this date) but it looks like a promising security approach.
Just curious – how did you find this post?
Thanks for the response. Will investigate using the API key. Bit of a novice to all this though. I would be interested to hear if you make any more progress.
I think this post was tweeted by @geckoboard, otherwise I would have found it while searching for geckoboard and google docs.
Dave, here’s a little more on security:
Another reader recently asked –
Absolutely concerned. Google Docs (as many know) is a security nightmare, but there are some promising avenues that can help.
Imagine GeckoDocs sending the XML documents to a secure location on a server (say, App Engine), and the server arbitrates access to the Gecko-feeds by passing a key. A simple Python script could perform the arbitration process making the feed available to Geckoboard securely.
We know this will work because there are examples using App Engine with Geckoboard. The Google scripting API also supports web services over HTTP including SOAP. As such, the GeckoDocs script could publish the Gecko-feeds straight to App Engine, securing the entire process and negating the need to publish your spreadsheet publicly.
Overall, there is also some degree of security through obscurity in the current model. It’s just not smart to rely on obscurity as a security platform. However, there are also many dashboard-related data points that are not necessarily a security risk. In aggregate, sensitive data is generally vulnerable, but there are additional ways to obscure views and access to the aggregate information stored in Google Docs and used in Geckoboard.
Consider… It is possible to create a spreadsheet that serves only as a dashboard aggregation point. It can pull data from Google documents that aren’t shared publicly and even obscure the data further by transforming it into percentages, and scores. Only the final sheet where the XML feeds are generated into, need to be publicly shared.
Hi Brian
Excellent, excellent post. I came to it via the geckoboard blog.
I’m not a JS or XML expert by ANY stretch of the imagination… but I got it working from your post and associated files. You’ve done a fantastic job. Thank you.
Quick question…
You mention that the GIC “can exist in the same sheet or a separate sheet”. If I have it in a separate sheet, how do I reference ranges in other sheets?
Thanks again for your contribution.
Warmest regards
Pete
Pete,
Using data key references, any spreadsheet document can access the data of another spreadsheet assuming you have access rights to the other sheet. ImportRange() for example allows you to do this in formulas. You can also do it in scripts using Google Apps Scripting services.