Home > development, javascript > Google Apps Scripts

Google Apps Scripts

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.

Categories: development, javascript
  1. John B. Mull
    July 6, 2011 at 12:59 pm

    After six months, have you had the opportunity to see if the GAS engine has improved?

    • David
      July 6, 2011 at 1:17 pm

      I haven’t, actually. My original look at it was in response to a particular need, and I haven’t had a need to revisit it. However, Google’s API’s are almost always being improved, and what I found here might not be true any more.

      Just glancing at the API Documentation, it looks like they have added Search capabilities within spreadsheet data since I looked last. That is a big move forward, and I suspect the pieces I found to be not ready when I reviewed this might be further along now.

  2. January 20, 2012 at 4:14 pm

    It’s getting better. Take a look at Google Visualization API. They’ve also just added databases capabilities via Fustion tables (but it currently just entered beta).

    Even with that, it’s still a pain to use GAS due to lack of library support. They added a public script library but no way to import scripts from a private library file. Google supposedly has been working on this feature for over a year but still no noticeable progress has been made.

    I stumbled on this site because I’m looking for resources to assist in building a workaround solution (using plaintext files, doc.getContentAsString() and eval) to import a library object literal from an external file. I have a basic plaintext editor I built using a spreadsheet with some creative scripting but I can’t get the eval to work properly.

    Also, no jQuery or CoffeeScript support so coding is still like writing JS 10 years ago.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: