All of us have had situations in which, the enterprise that we are working with, would like to have quick snapshots of the various metrics on the dashboard. These should not only be readily available, but also be refreshed with real data in real-time so that the latest on the health of the organization/business is at the fingertips.
We had a similar situation when we were working with a large chain of restaurants in UK. The idea of the project sponsor was that as soon as the manager of a particular restaurant enters the dashboard, he should be able to see all the statistics which would make him take instant decisions. An often used way is to create charts using tools like JFreeChart and extract data from the database to fill up the dashboard.
Fortunately for us (since the client had already gone Google with Google Apps) the use of Google Sites and Google Spreadsheet made the job pretty easy. Let us look at what the situation looked like for us.
If you look at the diagram (created with SimpleDiagrams), the heart of the system is the Google Spreadsheet which is present in the cloud. In our case, we were populating the Spreadsheet with data from the datastore, however you could easily extend the model since the spreadsheet is shared, there could be multiple people working on the same. The idea is that it would be updated in real-time. In our case, we were updating the spreadsheet from the datastore on the basis of an onEdit event which triggered a Google App Script which would fetch the data from the datastore as a JSON object and populate the spreadsheet. Once the spreadsheet is populated it can feed data directly into the management dashboard.
Here is a sample code of how data is fetched from the datastore
[sourcecode language=”javascript”]
function onEdit(event)
{
Logger.log("Edit Event Triggered");
var ss = SpreadsheetApp.openById("0AnT_TzsvVi4hdE78RUtZbVJLbDJrcHA4UkFEeUpseWs");
var raw = ss.getSheetByName(‘raw’);
if (raw.getRange("D4").getValue() > raw.getRange("D5").getValue() ) {
reloadRawData(ScriptProperties.getProperty(‘appengine_domain’),
ScriptProperties.getProperty(‘restaurantId’));
}
raw.getRange("F1").setValue(Logger.getLog());
}
function forceLoad() {
reloadRawData(ScriptProperties.getProperty(‘appengine_domain’),
ScriptProperties.getProperty(‘restaurantId’));
}
[/sourcecode]
Now assuming that our spreadsheets is populated and has dummy data represented as follows, (assume that this data is some data for a class which we would embed in the sample classroom site provided by Google as a template)
Now lets us see how do we get this data on the management dashboard in a chart format. Remember, that the spreadsheet is available to us on the cloud. That is the key.
In your Google sites page, you would be able to insert a gadget. Goto Insert-> More Gadgets and select the column chart from the list of gadgets. Note that this is just one of the many many charting options that you have to make your dashboard look great.
Once you hit ok, you would be presented with the list of options which are necessary for setting up the gadget.
The first option is Datasource URL and this is where the magic of having your data right on the cloud comes in handy. Our spreadsheet is available at the following location http://spreadsheets.google.com/a/inphina.com/ccc?key=0AkxKGwnO-B5KdHBraWJDUldrX3BYa3RONkE1VmlQU3c&hl=en#gid=1
hence our Datasource URL becomes
As you would notice, in the above URL, we have the key of the spreadsheet that we are referring to, we also have the range of columns that we are interested in which is B1:N3 in our case.
Once you define the datasource, you can preview the chart and you would get something like this
Of course, you can play around with other configuration settings to make the chart prettier. Once you are satisfied, you can embed it into your site and it would look something similar to this
and of course most of the charts that come along by default are interactive too, so you just have to point your mouse on the columns to get more details.
Hence, we were able to quickly generate a spiffy looking dashboard for our client in a matter of minutes using out of the box charting capabilities provided by Google and the power of Google Sites and Google Docs. The population of the spreadsheet could either be done by using Google App Script or it could be done manually if many people in the field are working on the same spreadsheet. The key is that once the spreadsheet has data you can have all the fancy looking charts.
