David's Tech Blog

I was recently asked to review Google Apps Scripts as a potential platform for some simple business apps, tying spreadsheet data to Google sites.  The organization already uses Google Apps, and it seems like a short leap to using Google Apps scripting.  My conclusion from the review was to recommend looking for another platform for building apps, and integrate with other information in Google Apps in other ways.  It might be that Google Apps Scripts are just too young, and the feature set will grow to fulfill its promise.  At the moment, its just not there yet.

The first hurdle is that building apps in Google Apps Scripts is clunky, and doesn’t really feel like working with javascript.  Javascript is flexible and easy to work with, if at times hard to wrap your head around its quirks.  Working with Google App Scripts felt like going back a decade.  A large part of that is because the Script platform is built around GWT.  You don’t really work with HTML, CSS or the DOM.  You can’t generate client side javascript or any kind of markup directly.  Everything has to pass through a series of overlaid panels with grids and widget objects that you put field objects, labels, and text into.  This works, and in the end you can make decent looking web pages out of it, but its very cumbersome.

There is no CSS that you can work with in Google Apps Scripts.  In my original version of this post I had my workaround here, but in the interest of letting people find that without having to read through the rest of this, I pulled that out into its own post.  The approach let me apply the whole set of styles to all “tableheaders” across the whole app, and if I wanted to change the styles I could change it one place and have it applied everywhere. That’s not particularly wonderful, but it sure saves a lot of setStyleAttribute calls. Without this approach, applying the four styles in my appStyles example above to the table headers would have taken four uses of setStyleAttribute for each column in each table that appeared in the app. This approach could be improved on significantly, but it solved my immediate headache.

What killed my ability to use Google Apps Scripts, though, was the lack of searches or queries for data in spreadsheets. When I started digging into this I assumed that Google would provide search capabilities, sort of like they do in the Spreadsheet data API. As far as I can tell this capability is completely missing, so far, from Google Apps Scripts. There’s a feature request for it, but it isn’t there yet.

This means that if you want to find something in a spreadsheet, you have to load the whole sheet (or range of cells) and iterate through the whole thing looking for the bit you’re actually needing. This works, but it is slow. It would be fine if you had a few dozen rows, but it doesn’t take much data to be getting into a thousand or few thousand records, and this isn’t an acceptable way to look up data.  I wasn’t expecting this to be an approach suited for hundreds of thousands of records or anything, but from my quick tests it’s too slow for 1000 rows to be practical.

On top of that, it isn’t really asynchronous as we expect things to be in a modern platform. If I was building the same type of thing with jQuery or something, and parts of it took more time than I really wanted to keep users waiting (which these days isn’t long), I’d slap out the basic HTML and fill in all the pieces as they became available. That would hide the slower data on a panel or tab or whatever that isn’t displayed on start up, allowing a delayed load. You can’t really do that with Google Apps Scripts, since you can’t write custom client side code beyond the relatively few event driven callbacks they provide. From what I can tell, you pretty much have to load all of your data at the start, including data you aren’t going to use until people start clicking. That loses you those seconds to get your data in order that might otherwise be available before the user looks behind the curtain. There isn’t any way to swap out content, other than simple things like text values. You can’t manipulate the DOM. That’s no way to build a web application.

Most of the problems I had using Google Apps Scripts were related to how young it is. The documentation is incomplete at best. If you want to know what most of the classes do, you have to dig into the GWT documentation. Most of Google’s code documentation is decent (I recently had occasion to work through their OpenId documentation as well, but that’s a different post for a different day), and I expect that before too long they’ll fix this documentation as well.

But if this platform is really going to be useful Google is going to have to take significant steps to embrace javascript much more than they are here. Obviously they can. They have other things based on javascript that are awesome. Unfortunately, this isn’t one of them. Yet.