Make your Google Spreadsheets Scala-ble

Google Spreadsheets (and softwares alike such as Excel) are a great way to visualise and organise (medium-sized) data. Google spreadsheets are also easily connectible to a whole lot of Google services such as Google analytics. Hence, they are the perfect entry point for your data. They even offer powerful tools for manipulating the data via a fully featured API.

However, the problem is that with this power comes a great responsibility to keep the code inside the cells readable and easy to maintain. Sadly, this task quickly becomes impossible to accomplish, and you soon break any of the “clean code good practices” out there (such as DRY, KISS…). Keeping a clear versioning also is a challenge.

Fortunately, Google allows us to write custom functions that you can then use as any other built in function inside your spreadsheet. This is already a huge leap forward, but you have to write them in JavaScript, which is arguably not the most scalable and easy to maintain language. Wouldn’t it be great, however, if we could write these function in a language that is, aka Scala?

Luckily for us, Scala code can be compiled to JavaScript, and allows to expose functions for JavaScript to use them. That is all we need to achieve what we want!

In the following, we’re going to describe how to use the project available here.

TLDR

In order to use Scala for making custom functions, follow these steps:

• clone the repo.
• write and export your functions to the global scope
• (install and) launch sbt
• execute the `fastCompileCreateFunctions` task
• copy-paste the compiled `google.js` file (in `target/scala-2.13/google.js`) into Google Spreadsheets’ Script Editor

How it works

Scala-js compiles a Scala project into a big JavaScript file that we can then copy-paste into the “Script Editor” associated to the Spreadsheet. But there is a catch. Scala-js puts the exported functions into the global scope, while Google wants custom functions to be exactly defined with the syntax

function foo(args) { … }