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:

http://spreadsheets.google.com/feeds/list/tH7qadCgXznEIgpvQq14cCQ/od6/public/basic

that render the ATOM XML view of our data. But in GWT the new JSO Types needs a JSON representation and as such we can add the parameter "alt=json-in-script":

http://spreadsheets.google.com/feeds/list/tH7qadCgXznEIgpvQq14cCQ/od6/public/basic?alt=json-in-script

to obtain a JSON view. More than this, this url also accept the universal "callback" parameter-name to make cross-site-call and be called back to a known function name.

All seems to be so good, well done and works in minutes but.......

...but the JSON that Google Spreadsheet returns is not so good for GWT JSO. Let's make an example with the previous url:

{
   "encoding":"UTF-8",
     "feed":{
      "author":[
         {
            "email":{
               "$t":"luca.masini@gmail.com"
            },
                        "name":{
               "$t":"luca.masini"
            }
         }
      ],
            "category":[
         {
            "scheme":"http://schemas.google.com/spreadsheets/2006",
                        "term":"http://schemas.google.com/spreadsheets/2006#list"
         }
      ],
            "entry":[
         {
            "category":[
               {
                  "scheme":"http://schemas.google.com/spreadsheets/2006",
                                    "term":"http://schemas.google.com/spreadsheets/2006#list"
               }
            ],
                        "content":{
               "$t":"album: A Tribute To Queen, artist: Queens Own ",
                               "type":"text"
            },
                        "id":{
               "$t":"http://spreadsheets.google.com/feeds/list/tH7qadCgXznEIgpvQq14cCQ/od6/public/basic/cokwr"
            },
                        "link":[
               {
                  "href":"http://spreadsheets.google.com/feeds/list/tH7qadCgXznEIgpvQq14cCQ/od6/public/basic/cokwr",
                                    "rel":"self",
                                    "type":"application/atom+xml"
               }
            ],
                        "title":{
               "$t":"4019601",
                               "type":"text"
            },
                        "updated":{
               "$t":"2010-06-11T09:36:26.995Z"
            }
   ...
   ...

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:

https://spreadsheets.google.com/tq?key=0AiX0qL3YM2vndEg3cWFkQ2dYem5FSWdwdlFxMTRjQ1E&gid=0

and the result is really good:

{
   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:

   private static native void registerGoogleVisualizationAPICallbackMethod() /*-{
            $wnd.google = new Object();

            var google = $wnd.google;

            google.visualization = new Object();
            var query = google.visualization.Query = new Object();
            var counter = $wnd['__gwt_jsonp__']['__gwt_jsonp_counter__']-1;

            query.setResponse = function(param) {
                  $wnd['__gwt_jsonp__']['I'+counter].onSuccess(param);
            };
      }-*/;

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:

   private static void requestSpreadSheetData(
                  AsyncCallback<SpreadSheetResponseJso> callback) {

   JsonpRequestBuilder jsonp = new JsonpRequestBuilder();

            jsonp.requestObject("https://spreadsheets.google.com/tq?key=0AiX0qL3YM2vndEg3cWFkQ2dYem5FSWdwdlFxMTRjQ1E&gid=0", callback);

      registerGoogleVisualizationAPICallbackMethod();
      }

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:

public final class SpreadSheetResponseJso
      extends JavaScriptObject implements SpreadSheetResponse {

      protected SpreadSheetResponseJso() {}

      public static final class Column extends JavaScriptObject {
            protected Column() {
   }
       ...
       ...
   }

      public static final class Album extends JavaScriptObject {
            protected Album() {
   }
       ...
       ...
   }

      public native JsArray<Column> getColumns() /*-{
            return this.table.cols;
      }-*/;

      public native JsArray<Album> getRows() /*-{
            return this.table.rows;
      }-*/;

      public native String getVersion() /*-{
            return this.version;
      }-*/;

      public native String getStatus() /*-{
            return this.status;
      }-*/;
}

that can be easily used in the AsyncCallback implementation:

requestSpreadSheetData(new AsyncCallback<SpreadSheetResponseJso>() {

   @Override
      public void onFailure(Throwable caught) {
       GWT.log(caught.getMessage(), caught);
   }

      @Override
      public void 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):

   public static final class Column extends JavaScriptObject {
   protected Column() {
            }

            public native String getId() /*-{
                  return this.id;
            }-*/;

            public native String getLabel() /*-{
                  return this.label;
            }-*/;

            public native String getType() /*-{
                  return this.type;
            }-*/;

            public native String getPattern() /*-{
                  return this.pattern;
            }-*/;

      }

      public static final class Album extends JavaScriptObject {
   protected Album() {
            }

            public native String getId() /*-{
                  return this.c[0].v;
            }-*/;

            public native String getDescription() /*-{
                  return this.c[1].v;
            }-*/;

            public native String getArtist() /*-{
                  return this.c[2].v;
            }-*/;

      }

Conversation Element

Comments

	Search this site