Using Google SpreadSheet with JavaScript Overlay

Google SpreadSheet is today Microsoft Access, thanks to it's open API and a powerfull scripting language. One interesting feature when publishing a sheet as a Web Page is the possibility to use the ATOM format, with which we can obtain an url like this:

Here we can notice that, after some "header" information, starts an array of "entry". Hidden inside a lot of unuseful information we can read the "content" property that again have a "$t" property with our row data as a String. So bad, this can be automatically converted to a Java object by the magic JSO mechanism, and also it needs some string parsing, so hated by Internet Explorer 7 JavaScript engine (and me too !!!).

Visualization API to the rescue

Nothing is lost, because Google itself has an alternative way to call spreadsheet data, used by the Visualization API. Infact reading the article "Google Spreadsheet as a DataSource" we can learn how to obtain another url to render our data:

{version:'0.6',status:'ok',sig:'1659638240',table:{cols:[{id:'A',label:'ID',type:'number',pattern:'#0.###############'},{id:'B',label:'ALBUM',type:'string',pattern:''},{id:'C',label:'ARTIST',type:'string',pattern:''}],rows:[{c:[{v:4019601.0,f:'4019601'},{v:'A Tribute To Queen'},{v:'Queens Own '}]},

Here we have an header with columns data, and then an array of rows, with data in the same order that we read in the column header.

Please, call me back !!!!

But the story is not yet finished. Infact this version of the url doesn't accept a callback parameter (or at least, I don't know how to pass it one) and always callbacks the "google.visualization.Query.setResponse" method. This is really uncompatible with GWT's JsonpRequestBuilder, that instead want to create a custom private callback method to manage results, one new for every call:

__gwt_jsonp__.I0.onSuccess(param)

The I0 property use a counter to have a unique identifier at every JSON call. Worst, the JsonpRequestBuilder is the only class allowed to build the JsonpRequest that internally contains the callbackId.

But this is not a huge problem, because the callback can be done only on the global JavaScript space, so that we can emulate from one side the Visualization API callback, and from another side the JsonpRequest behaviour:

This native method implementation build the callback exactly how will be returned by the SpreadSheet and inside that callback the method built by GWT. The two strings '__gwt_jsonp__' and '__gwt_jsonp_counter__' are private constants of the JsonpRequest class, and as such I was unable to reference them, but I don't think they will change and in case is a matter of seconds to correct them appropriately.

Finally we can call it !!!

Yes, with that short hack we can call the SpreadSheet data with the common and easy JsonRequestBuilder:

The callback method is created just after the call (in the hope the http request is not soooo fast) because only then we know the ID of the call, and though we can call only one SpreadSheet that return Visualization API callback at once, we can call a lot of other JSONP source and so we must get the counter ID as soon as possible after is created.

The JavaScript Overlay Types

The last thing we have to do is to code the class target of all this, the Overlay Type:

publicfinalclass SpreadSheetResponseJso

extends JavaScriptObject implements SpreadSheetResponse {

protected SpreadSheetResponseJso() {}

publicstaticfinalclass Column extends JavaScriptObject {

protected Column() {

}

...

...

}

publicstaticfinalclass Album extends JavaScriptObject {

protected Album() {

}

...

...

}

publicnative JsArray<Column> getColumns() /*-{

returnthis.table.cols;

}-*/;

publicnative JsArray<Album> getRows() /*-{

returnthis.table.rows;

}-*/;

publicnative String getVersion() /*-{

returnthis.version;

}-*/;

publicnative String getStatus() /*-{

returnthis.status;

}-*/;

}

that can be easily used in the AsyncCallback implementation:

requestSpreadSheetData(new AsyncCallback<SpreadSheetResponseJso>() {

@Override

publicvoid onFailure(Throwable caught) {

GWT.log(caught.getMessage(), caught);

}

@Override

publicvoid onSuccess(SpreadSheetResponseJso result) {

GWT.log(result.getStatus());

GWT.log(result.getVersion());

GWT.log(result.getColumns().toString());

Window.alert(result.getStatus());

}

});

An import thing to notice is that these Overlay Type Implementation are generic, can be reused for every spreadsheep, indipendently from the data they contains. I guess why Google doesn't write a GWT implementation of those APIs.

APPENDIX

For your convenience I paste here the complete code for Album and Column JavaScript Overlay Type (I used them as nested classes inside the SpreadSheetResponseJso):