Using ClojureScript with Google Apps Script

Game of Life in Google Sheets, written in ClojureScript. Github and demo

Background story

At ClojureBridge Berlin we put a lot of work into managing the sign-up process for attendees. With workshops of this kind it’s common for people to sign up but not actually show up. We’ve figured out a process to make sure we have nearly 100% attendance, but there’s a lot of manual work involved to make it work.

We use Google Forms for the registration. We typically get two to three times more sign-ups than we have space for. About three weeks before the workshop the registration closes, and we invite the first batch of attendees, selected randomly. These people get an email giving them a week to confirm that they’re still able to come. The rest are informed that they’re on the waiting list.

A week later we know how many spots have opened up, either because people cancelled or because they didn’t respond, and we can invite a second batch. We keep repeating this process until days before the workshop.

All of this is done by manually keeping multiple spreadsheets based on email conversations… not a great experience.

The weekly ClojureBridge Berlin project group is working on a web app that will make this a lot smoother, but that won’t be finished in time for the upcoming workshop, so I decided to investigate if we could automate some of the things we are currently doing inside Google Sheets.

Google Apps Script

If like many of us you’re also trapped in Google’s golden cage, you might be interested to know just how much of Docs, Sheets, and other Google services is scriptable. “Google Apps Script” is what Google calls third party JavaScript that runs on their servers and interacts with their services.

There are two ways to get started with GAS. The easiest is to open a spreadsheet, form, or word processing document, and go to Tools > Script editor. This will create a “bound” script, one that’s linked to the document. This has some limitations, but for personal use it’s the easiest option.

The other option is to go to http://script.google.com/ and create a new stand-alone project. You’ll need this is you want to write add-ons that others can add to their documents. You can also create Google Chrome add-ons, or even actual web apps that run on Google’s cloud.

I’m going to focus on sheets. One thing you can do is create custom functions that you can use as formulas in cells, “macros” in spreadsheet lingo. You can also add custom menus, add a sidebar, and display modal or non-modal popups.

As entry point there are time and event based “triggers” you can hook into, like “onOpen”, “onChange”, or “onSubmit”.

So to make this concrete, my plan is to create a sidebar in the “onOpen” trigger, which will contain a small UI for filtering attendees and updating their status.

It’s just JavaScript

While Google calls it “Google Apps Script” and insists you use the “.gs” extension, it’s really just JavaScript, and so we can use ClojureScript just fine. For instance, to insert a row of data you could do this

(.. js/SpreadsheetApp
    getActiveSheet
    (appendRow #js ["hello", "world"]))

Note that you do need to be up to speed with js interop. The great thing is that with a bit of sugar you get nice idiomatic ClojureScript doing what it’s good at, processing data.

(defn current-sheet []
  (->> (.. js/SpreadsheetApp
           getActiveSheet
           getDataRange
           getValues)
       array-seq
       (map array-seq)))

(filter (fn [[name age]]
          (< age 18))
        (current-sheet))

The guides and API docs for GAS are pretty good, so go there if you want to figure out how to do something.

To get it working I created a small project with just a single ClojureScript build.

;; project.clj
(defproject attendomat "0.1.0-SNAPSHOT"
  :license {:name "Mozilla Public License 2.0" :url "https://www.mozilla.org/en-US/MPL/2.0/"}

  :dependencies [[org.clojure/clojure "1.9.0-alpha13"]
                 [org.clojure/clojurescript "1.9.229"]]

  :plugins [[lein-cljsbuild "1.1.4"]]

  :cljsbuild {:builds
              {:main {:source-paths ["src"]
                      :compiler {:main attendomat.core
                                 :optimizations :advanced
                                 :output-to "export/Code.gs"
                                 :output-dir "target"
                                 :pretty-print false
                                 :externs ["resources/gas.ext.js"]
                                 :foreign-libs [{:file "src/entry_points.js"
                                                 :provides ["attendomat.entry-points"]}]}}}})

Now my code goes in src/attendomat/core.cljs, and I compile it with lein cljsbuild once main. The resulting Code.gs I need to manually copy over to Google’s script editor.

Those last three lines of project.clj can use a bit of clarification. I’m using advanced compilation, so I need externs definitions for the GAS API stuff.(If you’re not sure what those externs are for, check out Using JS libraries from ClojureScript) Luckily someone has already gone ahead and scraped the API docs to generate this externs file. You can grab it at https://github.com/tyskdm/gas.ext.js/blob/master/dist/0.7.2/gas.ext.js.

We also need to tell the compiler to leave the top-level functions alone, using the ^:externs metadata annotation.

(ns attendomat.core
  (:require [attendomat.entry-points]))

(defn ^:export create-menu []
  (.. js/SpreadsheetApp
      getUi
      (createMenu "ClojureScript")
      (addItem "Select attendee", "popup_attendee_selector")
      (addToUi)))

Now that function will be available in JavaScript as attendomat.core.create_menu, but Google expects to find simple non-namespaced function names, that’s where the “entry-points” stuff comes in.

Create a simple JavaScript file under src/entry_points.js, for example

function onOpen(e) {
  attendomat.core.create_menu();
}

By adding it under :foreign-libs this code will be prepended unchanged to our final build artifact (see project.clj above).

Importing / exporting Code

You may be wondering if there’s really no better way than to copy-paste that code over after every compilation. One small trick is to use a command line clipboard interface. On linux there’s xclip or xsel, so my build command looks like this.

lein cljsbuild once main ; cat export/Code.gs | xclip -selection clipboard

At least that takes part of half of the copying and pasting.

Now there are tools like this one that runs on Node.js, that will use the Google Drive API to push and pull your project files. Neat! But… it only works for standalone scripts, not for those bound to a document. To get them onto a document you need to jump through some other hoops, so in the end I decided it wasn’t worth it for me. However for developing commercial add-ons this could be a great workflow. Combine Clojure’s productivity with what this platform has to offer and you could have a winner on your hands.

Reflections

I definitely have mixed feeling developing for this platform, it brings back memories of doing Microsoft-only stuff back in the 90’s. Not that I’m not already up to my neck in Google’s muck, but yeah…

One thing that’s really still missing in an interactive workflow. Forget about a REPL, your feedback cycle is now: compile, copy, paste, click, click, click, plus a lot of println debugging (or Logger.log debugging, to be precise). It doesn’t help that the code is basically obfuscated. I tried with :optimizations :whitespace, and the result is too big, my browser wouldn’t let me copy it into the text field. What you can do is enable pretty-printing. When an error occurs all you get is a line number, so having expressions each on their own line really helps then.

Testing locally is not an option unless you mock out all of Google’s API, and what would be the point of that? I don’t immediately see a solution upcoming, given how the code needs to be uploaded and executed on Google’s servers, and given the limits of the import/export API.

Related projects

johnmn3/clgs uses bootstrapped ClojureScript so you can use ClojureScript expressions in cell formulas. There’s a bit more info on this threadon the Clojure Google Group.