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.

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

About the author

Arne divides his time between making Clojure tutorial videos for Lambda Island, and working on open-source projects like Chestnut. He is also available for Clojure and ClojureScript training and mentoring. You can support Arne through his Patreon page.

More blog posts

Simple and Happy; is Clojure dying, and what has Ruby got to do with it?

The past week or so a lot of discussion and introspection has been happening in the Clojure community. Eric Normand responded to my one year Lambda Island post with some reflections on the size and growth of the community.

And then Zack Maril lamented on Twitter: “I’m calling it, clojure’s dying more than it is growing”. This sparked a mega-thread, which was still raging four days later. A parallel discussion thread formed on Reddit. Someone asked if their were any Clojure failure stories. They were pointed at this talk from RubyConf 2016, which seemed to hit a lot of people right in the feels, and sparked a subthread with a life of its own.

Meanwhile Ray, one of the hosts of the defn podcast reacted to the original tweet: “I’m calling it: Clojure is alive and well with excellent defaults for productive and sustainable software development.” This sparked another big thread.

Loading Clojure Libraries Directly From Github

Did you ever fix a bug in an open source library, and then had to wait until the maintainer released an updated version?

It’s happened to me many times, the latest one being Toucan. I had run into a limitation, and found out that there was already an open ticket. It wasn’t a big change so I decided to dive in and address it. Just a little yak shave so I could get on with my life.

Now this pull request needs to be reviewed, and merged, and eventually be released to Clojars, but ain’t nobody got time for that stuff. No sir-ee.

Lambda Island Turns One, The Story of a Rocky Ride

One year ago to date I launched Lambda Island, a service that offers high quality video tutorials on web development with Clojure and ClojureScript. It’s been quite a ride. In this post I want to look back at the past year, provide some insight into how this experience has been for me, and give you a glimpse of what the future has in store.

This story really starts in December 2015. After three years of doing contract work for Ticketsolve I decided it was time for a change. I have been self-employed for many years, but I knew that sooner or later I wanted to try my hand at selling a product, rather than selling my time.

In January and February I took some time for soul-searching, and recharging. I went to speak at RubyConf Australia, and got to hang out with some old friends around Australia and New Zealand. Once back in Berlin I got busy creating Lambda Island.

Writing Node.js scripts with ClojureScript

In the two most recent  Lambda Island episodes I covered in-depth how to create command line utilities based on Lumo, how to combine them with third party libraries, and how to deploy them to npmjs.com.

However there’s a different way to create tools with ClojureScript and distribute them through NPM, without relying on Lumo. In this blog post I want to quickly demostrate how to do just that.

To recap, Lumo is a ClojureScript environment based on Node.js, using bootstrapped (self-hosted) ClojureScript. This means the ClojureScript compiler, which is written in Clojure and runs on the JVM, is used to compile itself to JavaScript. This way the JVM is no longer needed, all you need is a JavaScript runtime to compile and run ClojureScript code, which in this case is provided by Node.js. On top of that Lumo uses nexe, so Lumo can be distributed as a single compact and fast executable binary.

Announcing lambdaisland/uri 1.0.0

I just released lambdaisland/uri, a pure Clojure/ClojureScript URI library. It is available on Github and Clojars.

This is a small piece of the code base that powers lambdaisland.com. It’s inspired by Ruby’s Addressable::URI, the most solid URI implementation I’ve seen to date, although it only offers a small part of the functionality that library offers.

It’s written in pure Clojure/ClojureScript, with only minimal use of .cljc reader conditionals to smooth over differences in regular expression syntax, and differences in core protocols. It does not rely on any URI functionality offered by the host, such as java.net.URL, so it’s usable across all current and future Clojure implementations (Clojure, ClojureScript, ClojureCLR).

re-frame Subscriptions Got Even Better

Up until recently, to use re-frame subscriptions in Reagent views, you had to use a form-2 component.

A form-2 component is a function that returns another function, which does the actual rendering of the component to hiccup. In contrast, a form-1 component renders the hiccup directly.

;; form-1
(defn todo-item [todo]
  [:div.view
   [todo-checkbox (:id todo) (:completed todo)]
   [:label {:unselectable "on"} title]
   [:button.destroy {:on-click #(dispatch [:todos/remove (:id todo)])}]])

;; form-2
(defn todo-item [todo]
  (fn [todo]
    [:div.view
     [todo-checkbox (:id todo) (:completed todo)]
     [:label {:unselectable "on"} title]
     [:button.destroy {:on-click #(dispatch [:todos/remove (:id todo)])}]]))

Game Development with Clojure/ClojureScript

This weekend it’s Ludum Dare again, the world’s longest running game jam. The idea is that, alone or with a team, you build a game in a weekend based on a certain theme.

We got a little team together here in Berlin, and so I’ve been reviewing what options there are for someone wanting to build a game in Clojure or Clojurescript.

The good news is there are plenty of options, as you’ll see from the list below. You can do desktop games, browser based games with canvas or webgl, and you can even create Unity 3D games, all from your comfortable Clojure parentheses.

Union Types with Clojure.Spec

Elm and other statically typed languages have a great feature called Union Types (also called Sum Types or Algebraic Data Types).

Here’s an example taken from Elm. Suppose your system used to represent users as integers, maybe just an auto-incrementing primary key, but then switched to UUIDs represented as strings.

To correctly model this situation, you need a way to create a type that can be either an integer or a string, that’s what union types give you.