A Mail Merge in 100 lines of Clojurescript

I simplify a lot of my tasks at work with Google Apps Script, the scripting language for Google Apps. Google Apps Script is ES5 with access to objects defined by Google mapping to Google Products (Docs, Sheets, Gmail…)

Out of the box, the developer experience with Google Apps Script is pretty poor, you edit code in a Web editor and test that it works by running your code against real documents. I wanted to develop complex apps script, of more than a few hundred lines of code while being able to iterate quickly and safely. To do so, I set up clojurescript to compile to ES5 and built a fake version of dozens of functions in the Apps Script API to emulate the Google Services and test changes locally and quickly before deploying my code. This article presents a small subset of my approach, illustrating how to build a simple mail-merge, a tool to send email from a list of rows in a spreadsheet. To build this project, I learned a lot from Arne Brasseur's article. What's novel about my article is the testing approach and building fake version of Google APIs.

The repository with the code for this article is at https://github.com/charignon/mailmerge/tree/master/test/appscriptlib.

We want to be able to define a list of emails, subjects and messages in a spreadsheet and click a button to send emails with a script:

The script writes the result in a second sheet:

It is a very simple mail-merge that does not support any form of schedule or templating.

First, I thought of structuring the project in three layers:

• entry_point is the public API, it is the top level function
• functions contains the business logic and does all the hard work
• real_api a very thin wrapper around the apps script JS APIs

To make the code testable, I decided to extract the interface for Google APIs behind a clojure protocol (similar to a java interface). This make it possible to write a fake version of the Google APIs conforming to that interface and swap it with the real one:

We can also leverage the fake API to write integration tests for the business logic:

Let's peel off the layer and look at this bottom to top

Our mail merge needs to access a few functions from the Google API, we can get by with just 7 functions in 4 classes. Looking at the doc, we can get their name and build records and protocols (interface) to represent them. The protocols are trivial to write (so trivial that they can be programmatically generated from the API definition):

(ns appscriptlib.protocols)

(defprotocol GmailApp
  (sendEmail [_ recipient subject body] "Send an email"))

(defprotocol SpreadsheetDocument
  (getSheetByName [_ n] "Get a sheet by name"))

(defprotocol Sheet
  (appendRow [_ row] "Append a row [a b c ...] to this sheet")
  (clearSheet [_] "Remove all the elements of this sheet")
  (getName [_] "Return the name of the sheet")
  (getSheetValues [_ x1 x2 y1 y2] "Get the value of a square identified by coordinates"))

(defprotocol DocumentApp
  (openByUrl [_ url] "Returns a document matching <url> or nil"))

In the following implementation we make heavy use of the clojurescript dot notation for creating records and calling JS functions:

• (GoogleSheet. x) calls the constructor of GoogleSheet record or the GoogleSheet Javascript object
• (.openByUrl a b) calls the javascript method openByUrl on the javascript object a passing the argument b

Every record (concrete implementation) wraps an object returned by the Google APIs. For example GoogleSheet, wraps a Sheet object. When calling a method on a record, we call the underlying javascript method with the same name and wrap the result (if applicable) into a record:

(ns appscriptlib.real-google-apis
  (:require [appscriptlib.protocols :refer
             [Sheet SpreadsheetDocument appendRow GmailApp clearSheet
              getName getSheetValues DocumentApp openByUrl]]))

(defrecord GoogleSheet [k]
  Sheet
  (appendRow [{:keys [k]} row] (.appendRow k row))
  (getName [{:keys [k]}] (.getName k))
  (clearSheet [{:keys [k]}] (.clear k))
  (getSheetValues [{:keys [k]} x1 y1 x2 y2] (.getSheetValues k x1 y1 x2 y2)))

(defrecord GoogleSpreadsheetDocument [k]
  SpreadsheetDocument
  (getSheetByName [{:keys [k]} name] (GoogleSheet. (.getSheetByName k name))))

(defrecord GoogleGmailApp [k]
  GmailApp
  (sendEmail [{:keys [k]} dest subject message] (.sendEmail k dest subject message)))

(defrecord GoogleSheetApp [k]
  DocumentApp
  (openByUrl [{:keys [k]} url] (GoogleSpreadsheetDocument. (.openByUrl k url))))

(defn google-sheet-app [k]
  (GoogleSheetApp. k))

(defn google-mail-app [k]
  (GoogleGmailApp. k))

The records abstract away the javascript interop, once the record objects are created, no interop is exposed to the user. Here is an example of how to use this code:

;; Will return a GoogleSpreadsheetDocument
(openByUrl 
   (google-sheet-app js/SpreadsheetApp) 
   "https://sheets.google.com/...")

As you can see the only time the interop is visible is when when initialize the record. This code is pretty repetitive and for more complex projects, you can automatically generate the records and protocol from the API's definition. Unfortunately the fake version of the APIs cannot be automatically generated! Let's look at how to implement it in the next section.

To eventually write integration tests we need to build a fake version of all the Google APIs that we defined. The fake objects should mimic real objects and offer out of band access to their content for testing purposes. In other words, we want to make the fake object behave like its real counterpart but provide introspection into their state.

For example the gmail app is easy to fake, it can keep a queue of the email sent and provide a helper to inspect the emails in the queue:

(defrecord FakeGmailApp [sent]
  GmailApp
  (sendEmail [{:keys [sent] :as this} & args]
    (do (swap! sent #(conj % args))
        this)))

(defn fake-gmail [] (FakeGmailApp. (atom [])))
(defn emails-sent [gmail] @(:sent gmail))

We are using clojurescript's atoms to manage mutable state and the @ syntax to dereference their values. Note: The api says that it should return itself, hence the this at the end of the method.

I omitted here all the mocking of google sheets, feel free to check out the full code on github.

Let's leverage what we built and implement the mail merge:

(ns appscriptlib.functions
  (:require [clojure.string :as str]
            [appscriptlib.protocols :refer [openByUrl getSheetValues getSheetByName sendEmail clearSheet appendRow]]))

(defn mail-merge [{:keys [gmail sheet url date transformer]}]
  (let [mail-sheet      (openByUrl sheet url)
        log-sheet       (getSheetByName mail-sheet "Log")
        mail-list-sheet (getSheetByName mail-sheet "Queue")
        orders          (getSheetValues mail-list-sheet 2 1 100 100)
        num-emails      (count (for [[email subject message] orders
                                     :when (not (str/blank? email))]
                                 (sendEmail gmail email subject message)))
        message         (str "Processed " num-emails " email")]
    (appendRow log-sheet (transformer [date message]))
    (clearSheet mail-list-sheet)
    (appendRow mail-list-sheet (transformer ["Dest" "Subject" "Message"]))))

We can test it end to end:

(ns appscriptlib.functions-test
  (:require
   [appscriptlib.functions :as f]
   [appscriptlib.protocols :refer :all]
   [appscriptlib.fake-google-apis :refer :all]
   [clojure.test :refer :all]))

(deftest mail-merge-basics
  (testing "Can send email"
    (let [log-sheet   (fake-sheet [["Date" "Message"]] "Log")
          queue-sheet (fake-sheet
                       [["Dest" "Subject" "Message"]
                        ["[email protected]" "test" "msg"]
                        ["[email protected]" "test2" "msg2"]] "Queue")
          url         "mailmerge"
          shdoc       (fake-spreadsheet-document url [queue-sheet log-sheet])
          date        "2018-12-21"
          allfiles    (atom {url shdoc})
          gmail       (fake-gmail)
          sheetapp    (fake-document-app-backed-by-store allfiles)]
      (f/mail-merge {:gmail gmail
                     :sheet sheetapp
                     :transformer identity
                     :url   url
                     :date  date})
      (is (= [[date "Processed 2 email"]]
             (getSheetValues log-sheet 2 1 20 20) ))
      (is (= 2 (count (emails-sent gmail)))))))

The last step is to expose our function and inject the real google apps script API:

(ns appscriptlib.core
  (:require [appscriptlib.entry-points]
            [appscriptlib.real-google-apis :refer [google-sheet-app google-mail-app]]
            [appscriptlib.functions :as f]))

(defn ^:export mail_merge [url]
  (f/mail-merge {:sheet (google-sheet-app js/SpreadsheetApp)
                 :gmail (google-mail-app js/GmailApp)
                 :url url
                 :transformer clj->js
                 :date (.toString (js/Date.))}))

And provide a way to call it from google apps script:

function mail_merge() {
    appscriptlib.core.mail_merge(
        "REPLACE_ME_WITH_A_SPREADSHEET_URL"
    );
}

You can compile and copy the code with:

lein cljsbuild main once
cat export/Code.gs | pbcopy # paste compilation result into clipboard

Arne Brasseur covered the build tool configuration to compile to google apps script. Now we can paste the script to the script window associated with a spreadsheet and run it! If you are interested in more content about clojure, check out Extracting Chrome Cookies with Clojure.