Geckoboard is a great tool for any organisation that needs to track KPI’s, statistics or other pertinent live information. It is particularly well suited to displaying live information on large screens around the office.
Whilst Geckoboard integrates into a lot of different services already there’s a fair chance you’ll want to display information from other sources too.
Fortunately it features a relatively new API that allows for external data to pushed up to the board in the form of ‘datasets’ which can then be displayed in various ways.
In my case I wanted to use VBscript to pull data out of Microsoft SQL Server and display them on the board. Unfortunately the samples of the site were for other programming environment and did not seem to directly apply to me. It took a friendly soul on the message boards to point out that the API is actually a REST service and the various demo code was just a wrapper for that.
I hadn’t done much using RESTful APIs in the past, although I have done a fair bit using SOAP/XML so I figured this would be straightforward.
I found that this wasn’t too hard, and I will document the various samples. If you already know how to drive node.js or Ruby then those environments might suit you also. In my case I wanted to use VBscript because it’s light and simple, and will run on any Windows computer without having to install any tools or frameworks.
The API Key
In the sample code in the Geckoboard API docs, the API key is specified. A base64 encoded version of the API key actually needs to be sent in the query and I imagine the node.js framework takes care of that part. In my sample code I just send the base64 version of the key. Follow the Geckoboard instructions on how to retrieve your unique key, and then Google for a tool to base64 encode it. Use the encoded version of the string in the samples provided. Obviously I could convert the key to base64 every time but I just don’t see the point because it only ever really needs doing once.
Authentication
The Datasets API only accepts secure connections. So you must make all requests over HTTPS.
You can test your API key is working with the following sample code. Replace the base64 encoded auth key with your own.
Dim restReq, url, authkey
URL = "https://api.geckoboard.com/"
authkeybase64 = "putyourkeyhere-inbase64format!"
Set restReq = CreateObject("Microsoft.XMLHTTP")
restReq.open "GET", url, false
restReq.setRequestHeader "Authorization", "Basic " & authkeybase64
restReq.send ""
WScript.Echo restReq.responseText
You should get a 200 response containing {}
Make sure you have this working before trying to create a dataset, or before updating a dataset!
Creating a new dataset
Edit the URL string and specify the name of your dataset. When you update the dataset later on you’ll specify the same name. Edit the JSONstring to suit your own needs – see the API docs for more info on the various data types and formatting
Dim restReq, url, authkey, JSONstring
Set restReq = CreateObject("Microsoft.XMLHTTP")
url = "https://api.geckoboard.com/datasets/yourdatasetname"
JSONstring = "{""fields"": { ""anamountfield"": { ""type"": ""number"", ""name"": ""Amount""} } }"
authkey = "putyourkeyhere-inbase64format!"
restReq.open "PUT", url, false
restReq.setRequestHeader "Authorization", "Basic " & authkey
restReq.setRequestHeader "Content-Type", "application/json"
restReq.send JSONstring
WScript.echo("Geckoboard responded with: " & restReq.responseText
A response string containing your JSONstring data should come back. The dataset will also be available in Geckoboard, although of course there’s no data in it yet!
Replace all data in a dataset
Of course edit the URL to match a dataset that you want to replace. Also substitute your own API key that has been encoded in base64 format. Your JSONstring will also be your own.
Dim restReq, url, authkey, JSONstring
URL = "https://api.geckoboard.com/datasets/yourdatasetname/data"
authkeybase64 = "putyourkeyhere-inbase64format!"
JSONstring = "{""data"": [ {""anamountfield"": 7} ] }"
restReq.open "PUT", url, false
restReq.setRequestHeader "Authorization", "Basic " & authkeybase64
restReq.setRequestHeader "Content-Type", "application/json"
restReq.send JSONstring
WScript.echo("Geckboard responded with: " & restReq.responseText)
A response string containing your JSONstring data should come back. The dataset will also be available in Geckoboard, although of course there’s no data in it yet!
The formatting of your JSON string is highly situational – you can test it using plenty of online JSON checkers. Remember in VBscript that you escape a double quote character by using two of them. So replace any instances of “” with a single ” in any checkers.
Appending data to a dataset
This is now possible. Only one small change is needed to the code that replaces the dataset – replace the PUT verb with POST and you’re set. Brilliant!
To be complete, change the appropriate line to
restReq.open "PUT", url, false
Hope this helps!