Google I/O 2012 Notes: New features in Google Apps Script (NoSQL, HTML/Content publishing and Chrome app publishing)

This is my last post of notes from Google I/O 2012, so I won’t get to mention that Google Docs has offline editing, Google Drive not only can ocr your pdfs to make them searchable but can also recognise what’s in the images, Google Compute Engine or various Chrome improvements. Instead I’m going to focus on the sessions around Google Apps Script (which I occasionally write about anyway ;s).

One of the main questions I get from people interested in using Apps Script is how stable/sustainable the platform is. Given there were three separate sessions on Apps Script at #io12 announcing a number of new features including the launch of script.google.com I'd say for now things are looking very healthy.

There were (by my count) four new features of Apps Script announced outlined below, I’m throwing in one more old announcement, which I’ll share my notes on in this post:

  • Recent announcement Managed Libraries and Versioning – this lets you control a master script which you can maintain and bug fixes, new features can be used by anyone using the library (before if you created a script in a spreadsheet and someone made a copy the script became orphaned so the only way you could receive updates was making a fresh copy or manually replacing the existing code).
  • ContentService – lets you publish content generated by App Script which will be interpreted by other web services/tools as atom, rss, json, text or xml
  • HTMLService – lets you publish content generated by Apps Script which will be interpreted by other web services/tools as html which can include custom CSS and client-side JavaScript (including importing existing libraries/code like jQuery)
  • ScriptDb – a NoSQL style database that lets you store and query data that doesn’t have a fixed schema (doesn’t have a fixed number of values or may have nested data)
  • Publish to Chrome Web Store – you can publish Apps Script based applications directly to the Chrome Web Store so that other people may easily install and use.

Managed Libraries/Versioning

Existing services and ability to write your own custom functions has made it easy to extend the functionality of Google Apps. Using things like UrlFetch you could make calls to 3rd party APIs or maybe there was something you wanted to do with your Gmail account to make it better.

Previously there were a couple of ways you could share your custom scripts. Most often than not I embed them into my Google Spreadsheet templates, or you could add them to a Script Gallery or maybe drop some code you’ve found from elsewhere like GitHub.

The problem with all these methods is once the script is installed it becomes orphaned from the original, the only way to update is for the user to take a fresh copy or overwrite the code. Managed Libraries let you write a script which other people can then import into there project to immediate extend the methods they can use.

There’s no official searchable store of released scripts but here are some Notable Script Libraries to give you an idea of what’s possible. Here is more information on Managing Libraries.

One of my first concerns was because of Versioning if you want to share a scripted template which used a Managed Library the user would be stuck on the release version (this makes sense if you are a coder and want to use the stable version). So if you had an update users could go into their version and select the next stable release. It also looks like to can distribute a template spreadsheet with the library in Developer Mode, which means it’ll always use the latest version of the code.

ContentService

Getting formatted XML from Google SpreadsheetThere have been several times when I’ve wanted to digest/process some data in a Google Spreadsheet and then republish in a different format. A typical example would be import some timestamped data and generate a custom RSS feed. I trick I picked up from the Google Earth Outreach Spreadsheet Mapper is to format the data in a sheet within Google Spreadsheets and publish as text. So here is a spreadsheet of data and here is the data reformatted as kml.

This solution works well but can be fiddly and the output might be interpreted as text and not XML (because of the MIME type). The other issue is you are stuck with one view, so if I wanted to create a different RSS feed using the same data but a different view it needs to be published as a separate sheet. By creating a ContentService means data views can be published that are generated according to values pasted in the querystring (GET) or payload (POST).

Turn Google Sites pages into RSS codeHere’s some example code I’ve written that allows you to generate an RSS feed from Google Sites pages. When this script it published as a service the doGet catches a user defined Google Site url and page stub (checks if it already cached the result), then creates a page with the RSS.

sites2RSS url

I would show you an example but I’d need to publish with anonymous access and then anyone could use my quota. But here is an example of a feed passed through Yahoo Pipes to obscure the service key, which is using ContentService to generate an RSS feed of this page on QMU’s Audio Feedback Site. Most of the time the generate feed seems be alright for Google Reader and ingested back into Spreadsheet using importFeed() but timeouts in services are a problem if you are pulling back a lot of pages.

HTMLService

HTMLService is similar to ContentService but has some extra neat little tricks. Rather than go into detail about what it can do I’ll direct you to the HTMLService documentation and the video of session given by Corey Goldfeder. To get a quick idea of what is possible keisuke oohashi has already ported the Twitter bootstrap template to HTMLService.

Some of my notes on HTMLSerivce:

ScriptDb

Again the documentation for ScriptDb is very good so I’ll let you explore (worth noting the stub it’s in which covers various ways of Storing Data in Apps Script). The basics are:

  • NoSQL style database which lets you store, update and search data as JavaScript Objects (JSON)
  • Each Apps Script project gets it’s own local database. The only way I can find of accessing the data across projects is if the database is part of a Managed Library. Note of caution: My understanding is if you let other people use your managed library which has ScriptDb in it, everyone using the library has access to the data and if they can write to it, it’s using up your quota. More details on ScriptDb and Libraries).
  • The quota is a total of 50MB for regular Google accounts, 200MB for Google Apps for Education per-user (a concern I have is if you start a regular scrape collecting data in ScriptDb in one script and forget about it, it would be a nightmare to find where your quota had gone. Best practice might be to only use your ScriptDb’s through a personal script library. Because ScriptDbs are local instances using in a managed library will give you access to the data across scripts Bruce Mcpherson has already written a library you could use to silo your data.
  • Querying is by example’ – if you use type: ‘employee’ in your query it pulls all of the objects that include this somewhere in their record.

Here is the video from Google I/O session which covers  Storing Data in Google Apps Script.

Publish to Chrome Web Store

So your institution is running Google Apps for Education and you are doing interesting stuff within the Google Apps ecosystem. Maybe, like Loughborough College, you are experimenting with how Apps Script can be used for assessment and feedback. Perhaps you see a way of making your students lives a little easier like providing a filtered view of all the feedback docs they’ve received or a way for them to book a perch at a PC. Well now you could brand and publish the application to the Chrome Web Store.

I haven’t had a huge chance to play with this feature, but here is the Publish to Chrome Web Store documentation. One thing I do know is you need a web store developer account which has a one off fee ($5). Some examples already in the store:

  • Gmail Meter - runs on the first day of every month and sends you an email with all the desired statistics on your own email behavior.
  • Gmail Print All for Chrome - help you quickly export messages right to a Google Doc that you can print, save as a pdf, share with others, or store in a way that works for you
  • DriveEye - lets you keep an eye on your Google Drive. DriveEye will send you email alerts when a collaborator adds files to a shared folder.

[You can actually search for more because the from references where the script is published]

It seems fitting to finish on the I/O session recording Putting Together the Pieces: Building Apps with Google Apps Script (this actually ran first during the event, but pulls in all the parts I’ve mentioned above):

I think that’s enough Google for now. Next to write about new version of NodeXL, Voyant-tools or Datameer … or something completely different instead