Scripting Google with Google Apps Scripts: Review

When Google Apps Scripts was first introduced, about a year ago, it was available only to premium members. Then in January, Google made a majority of the features, such as classes for accessing the contact list, classes for accessing the folders, and classes for manipulating the spreadsheets down to the cell level (like what you would find with desktop-based spreadsheet software), available to standard customers. Then in May, Google added several updates such as database integration support via JDBC (Java Database Connectivity), custom user interfaces (still reserved for the paid “Premier” edition of Apps), standalone invocation of scripts (letting you run the scripts from non-Google websites), additional integration into Google properties such as the Documents list and Google Maps, and installable event handlers.

Presently, the scripting system is available primarily from the spreadsheet application, although there’s integration into other Google applications such as the email system and the maps system. Included in the scripting system is a rich API filled with objects that let you automate almost any task you can imagine. As examples of its power,  Google has made available examples such as one that lets you simulate a stock trading strategy in the spreadsheet application, one that generates fractals and one that’s an entire course registration system (OK, that last one seems a bit out of place; I can’t imagine why somebody would use an online spreadsheet program as a course registration system, but it does demonstrate the wide range of things you can do).

Personally, I’m still a bit skeptical. There are certainly some handy features here, but I’m not sure how popular the scripting system will be. The JDBC part is cool, and if you’re a premium member you’ll be able to build some decent applications on top of the spreadsheet system. But look at it this way: That functionality has existed in Microsoft Excel for many years, but how many people actually build applications in Excel compared to the number of people who simply use Excel for its basic spreadsheet features? Perhaps that will change, and indeed the Google Apps Script system has some nice features, but we’ll have to see just how popular it becomes.

JDBC Integration

If you’re familiar with JDBC from the Java world, you’ll be right at home with the JDBC integration. The set of classes is smaller than the official Sun JDBC API that runs under Java (remember, we’re talking JavaScript here, not Java, which is very different). But the base functionality is there through classes supporting connections, statements, prepared statements and result sets.

The API documentation is a bit weak in that it just shows the function prototypes and some quick descriptions, but not really any examples. For example, I wanted to see some actual SQL in the JdbcStatement class, but there was none. (I imagine there will be more documentation in the future, however, as well as more examples from the community.)

Trying It Out

The scripting system is basically a JavaScript wrapper around a set of Java classes on the server. How do I know that? Because during testing, I often received errors that were obviously coming from Java. (For example, one time I tried to pass a JavaScript array into a function and received the error message “Cannot convert NativeArray@78037b60 to java.lang.Object[][].”)  Further, the JavaScript classes that you interact with seem to have a lot of methods that aren’t documented in the API—which is actually quite normal when you consider that JavaScript doesn’t have much of a mechanism for public and private members. (There are ways to hide members, thereby making them private, but the spec itself doesn’t support access levels.) But this means you have a whole slew of functions you’re free to call—although I would be careful if they’re not documented because Google could easily change them at any time. Again, how do I know this? Because the script editor is object-aware and will show you a list of members available to an object when you type the object name followed by a dot.

Of course, JavaScript isn’t strongly typed (you just declare variables with var, and they can hold any type), and this means the code editor is doing some sophisticated work to follow the chain of calls. For example, if you type

var ss = SpreadsheetApp.getActiveSpreadsheet();

the editor knows that the getActiveSpreadsheet method returns a Spreadsheet object (or, more accurately for the JavaScript purists, an object that contains the members that define it as a Spreadsheet object).

Because the documentation was a bit weak, I had to figure out how to get a JDBC connection going. But once I got it figured out, it worked very well. For the programmers reading this, here’s the short code that opens the JDBC connection, executes some SQL and copies the resulting data into the active spreadsheet:

function testJDBC() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var connstr = ‘jdbc:mysql://www.example.com:3306/test’; // subst your own IP/database name.

var conn = Jdbc.getConnection(connstr, ‘username’, ‘password’);

var stmt = conn.createStatement();

stmt.execute(‘select * from names’);

var res = stmt.getResultSet();

var line = 2;

var stuff;

while (res.next()) {

var fname = res.getString(‘fname’);

var lname = res.getString(‘lname’);

sheet.getRange(‘A’ + String(line) +’:B’ + String(line) ).setValues([[fname,lname]]);

line++;

}

conn.close();

}

The code execution was a bit slow; it took about four seconds to connect, and then you can visibly see a row fill, then a very tiny pause, then the next row fill. Compare this to connecting remotely via the mysql command-line interface (from my local computer to the same remote mysql server), which connected instantly, and retrieved the rows a bit more quickly. It’s hard to say exactly what the holdup is on Google’s end, but I would suggest doing some tests to see if the access is fast enough for your uses. If not, you’ll want to do some streamlining (e.g., use stored procedures and views on the database end).

Standalone Invocation of Scripts

According to the official blog, “it’s now possible to call a script from any website, so you’re able to build web pages where users can submit entries that will be collected in a Google spreadsheet.” Unfortunately, that’s about all the information we have at this point. The docs so far say nothing about this feature, and it sounds intriguing. I’m speculating that there will be a JavaScript library that you’ll include on your website that gives you access to the scripts, much the same way you can include Facebook scripts on your own site. We’ll have to wait and see on this one. And the privacy and security issues should be interesting as well.