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.
couchdb bulk document transfer
I have a few test and development servers scattered around at various locations, some of which are there just because of convenience for the different people and locations I work with. I needed to copy some databases from one couchdb server to the other, but since the servers weren’t running the same version of couchdb I couldn’t just copy the files. The bulk document functionality almost gets me there, but it has extra junk besides just the bare documents. I also wanted to be able to remove the _rev tags, since in this case they are just an extra headache (I’m deleting the database and loading it fresh from the other site).
My solution was to write a little method in PHP to dump a database and write out the contents to a file. The approach I used was sparked by the specific project I was working on when I needed it, so it is written based on Zend Framework. I’m just using Zend_HTML for my CouchDB interaction, which enables working with couchdb very nicely.
First, we need to get the data from CouchDB.
$client = new Zend_Http_Client( );
$resp = $client->setUri( $this->URI . '/' . $db . '/_all_docs?include_docs=true' )
->request( 'GET' );
$body = $resp->getBody( );
if( $resp->getStatus( ) != 200 ){
echo '<div class="error">Status: ' . $resp->getStatus( ) . "<br>Did you spell the database name right?<br><pre>$body</pre></div>";
exit;
}
We’re using Zend_Http_Client to connect to CouchDB. We set the URI, which we have in our class constructor. I’m getting it from Zend_Config, and piecing together the login credentials if they are in the config, but that’s a different post. The $db is just the database name we’re dumping. If the status returned in the response isn’t 200, that almost always means (in this specific context) that we asked for a database that isn’t there ($db doesn’t exist in CouchDB), so since we can’t continue we show an error and exit.
So now we have a big glob of JSON in $body, but it isn’t formatted the way we need it to load, and we need a file to write this into. One side note here: I’m assuming the size of our database is fairly moderate. My use case was a couple thousand documents, and this approached worked well. You would need to handle it in pieces if you had more data than you could process at once.
So in the next part we convert the JSON to an array so we can iterate through it in PHP, and we drop the parts we don’t care about. We only need the part that’s in “rows.” CouchDB gives us extra information about the number of rows in the data and some other stuff we aren’t going to use here.
$data = Zend_Json::decode( $body );
$total_rows = $data['total_rows'];
$data = $data['rows'];
I use Zend_Json::decode instead of just json_decode here because it converts to a nice array instead of to an object, and its just nicer in this situation to work with.
$resource = fopen( $outpath, 'w' );
$count = 0;
$comma = "{\"docs\": [\n";
foreach( $data as $key => $value ){
if( !$preserve_rev ) unset( $value['doc']['_rev'] ); // we aren't going to use this to update, so we don't need the _rev
$docs = $comma . Zend_Json::encode( $value['doc'] );
fwrite( $resource, $docs );
unset( $data[$key] );
$comma = ",\n";
$count++;
}
fwrite( $resource, "\n]}" );
fclose( $resource );
$filesize = filesize( $outpath );
echo "<div>CouchDB reported $total_rows rows. I wrote $count rows to $outfile, with a resulting file size of $filesize bytes.</div>";
}
The first line here is opening a file so we can write to it. Make sure you check if the file exists or not and that you are safely handling the file name first.
We’re using $count and $total_rows (from that last section) to report on the results at the end.
We add the JSON opening to the front of our file (the first $comma setting does that), and iterate through the array, sticking each document from the data into the file, separating each document with a comma. We unset the documents from the array as we use them, just to tidy up as we go.
After we’ve gone through the whole array we close the JSON array and object, close the file, get the filesize to report, and output a summary of what we did.
This results in a file at $outpath that we can move to the destination server and just load in. Typically for my case it means I copy the file, delete and recreate the database, and push the file in as the new data. So far I’ve done that from the command line with curl, an example of why CouchDB is so easy to work with.
Simple form submit with jQuery
In building ajax driven apps, one of the things that you have to do is capture form submits, send them to the server, and then handle the results. In many applications, the results are data, passed as JSON or XML or some other format that the remote service provides. In other apps, we’re dealing with snippets of HTML that then get placed in the current page somewhere. I want to be able to handle all forms on the pages, without needing to know in advance how many forms there are, what they are called, what’s in them, or what to do with the results. I haven’t found a good succinct description of how to do this, so here’s mine using jQuery. This isn’t rocket science. Its just pulling together some of jQuery’s nice features. There are other good ways to do this.
$(document).ready(function(){
$("form").submit(function(){
var thistarget = this.target;
jQuery.ajax({
data: $(this).serialize(),
url: this.action,
type: this.method,
error: function() {
$(thistarget).html("<span class='error'>Failed to submit form!</span>");
},
success: function(results) {
$(thistarget).html(results);
}
})
return false;
}
);
});
This captures all submits on all forms on the page, and uses jquery’s ajax call to post the form’s fields to the URL in the form’s action attribute, and then places the results in the element identified by the form’s target attribute. You could easily use jQuery.post instead of ajax, and all you would lose is the error message, at least of the options I included above. jQuery.ajax has a bunch of options, which are documented nicely here:
http://docs.jquery.com/Ajax/jQuery.ajax#options
The forms in the page look like normal forms, and don’t need any special attributes other than that we are abusing the target attribute a little to specify the target element for the results.
<form name=’myform’ id=’myform’ action=’targetURL’ target=’target element’>
First Name: <input type=’text’ name=’firstname’ id=’firstname’>
<input type=’submit’ name=’submit’ value=’Submit’>
</form>
<div id=’target element’></div>
If I submit this form, either using the Submit button or by hitting enter in the firstname field, the fields from the form (in this case just firstname) are posted to targetURL, and if the post works the results that I get back are inserted into the ‘target element’ div. If the post fails for some reason (for example, if I get back a 404 error or something) then the “Failed to submit form!” message is inserted into the ‘target element’ div.
Note that if javascript isn’t available, the forms will still work. We’ll just get the results in the named target window instead of having it inserted into the page.
My linux distribution odyssey (so far)
Last year I wrote a post about trying out Ubuntu, but then moving back to OpenSuse. I was very happy with OpenSuse, and wouldn’t hesitate to recommend it to anybody. However, when Ubuntu 8.10 was released I moved to it. Some people just can’t make up their minds, I guess. Then I moved to Ubuntu 9.04, and a couple of weeks ago I upgraded that to 9.10. All of these worked great.
I used to tell people that the linux distribution they chose is a matter of taste, and there were several perfectly good choices, but I’m not sure I really meant it. In my linux life, I’ve used Redhat, Gentoo, Ubuntu, Fedora, Debian, CentOS, Conectiva, and several small specialized distributions. I ran KDE for years, and was clearly superior to all those Gnome users. Now (since the release of KDE4) I run Gnome. I somewhat expect that at some point in the future I’ll find myself back on KDE. My longest chunk of time was on Redhat, before they came up with the whole Fedora thing, and for me that was clearly the winning choice. During that time (and a while after) I wouldn’t touch anything with Debian in its lineage. Then I got a job in which I inherited some Debian servers, and I was pretty miserable for a bit, but I got over it. I have a long history of being a distro snob of one kind or another. I have at some point or other fervently disliked some of the best software out there.
For now, I’m pretty happy with Ubuntu. It works really well, and long gone are my days of hacking at some perl script or trying to figure out why code distributed with the distro would not compile on my box. For some time now, stuff just works. I still occasionally find a use for dusting off my perl skills, but not because I couldn’t find good tools from somewhere else or because basic components of the software I need don’t work. In fact, its pretty rare to not be able to just run a search in Synaptic, install something, and be on my way when I need some new application. My hacking time is spent solving the problem I’m actually trying to solve, instead of on getting to the point that I can start working on it.
Things have really come together in the last couple of years for Linux. Its amazingly good. Things work amazingly well, on every piece of hardware I ever use. My kids and my wife still use Windows. Its fine for games. Ubuntu is easier to install. Its easier to maintain. Its easier to install and manage software on. Its easier to patch. Its even easier to use. If I want to get stuff done, I never choose Windows.
And the message, I think, of my odyssey through all the different linux distributions is that it doesn’t matter. I’ve used several linux distributions. I’ve used KDE, Gnome, and Enlightenment (and probably something before KDE, but I don’t remember what). The key to my computing world is choice. Use what works for you for what you are doing now.
That’s not what life is about, but it is what computers are about. They are tools. Use what works. Use what you like.
If you haven’t tried Ubuntu yet, you should.
Clean Models
The flip side of what I said in my last post about Models in frameworks is that models shouldn’t know anything about the controller or view. The data that the model is providing might eventually end up in JSON, or XML, or in a web page or HTML snippet, or in a CSV file. None of that should matter to the model.
Generally models should not return error messages or “no data” messages that are intended for the eventual user, unless they are generic enough to be meaningful regardless of context. It should always be obvious for the controller whether the call was successful, but the controller and view should handle how to display or send any messages. Typically errors and other similar messages should be communicated through a central error or messaging handler. The messages formatting and even content will vary quite a bit depending on how the results will eventually be delivered.
The data might be destined for a javascript heavy table, or for a CSV or Excel file, or a PDF file, or for a clean display on a light HTML page, or to pre-fill a form on a user’s return visit. All of these will have very different formatting requirements, both for the data and for any messages that might get displayed to the user. The deliveries that download files might actually never download anything if there’s no data, and might display messages in some other way.
Not building clean models, either by requiring that the controller know too much about the model, or by embedding information about the eventual delivery of the information in the model, is a recipe for spending time rewriting later, or for ending up with lots of duplicate code.
David's Tech Blog 