In any company, there’s a lot of data sitting around in spreadsheets. We manipulate that data using built-in functions to create formulas that allow us to extract meaningful information. If your company is using Google Suite you’re able to extend the default functionality available in a spreadsheet by creating custom functions using a flavour of ECMAScript called “Apps Script”.
For someone familiar with other popular flavours of ECMAScript, Apps Script feels closer to NodeJS rather than Javascript given the absence of the web APIs and the DOM. Apps Script provides instead access to Google Suite API using high level classes like SpreadSheetApp or DocumentApp to name a few.
There are two types of Apps Script projects, bound and standalone. Bound scripts are the ones who are permanently attached to a particular document, a spreadsheet for example, while a standalone script is not. This difference will affect the types of APIs our scripts are going to have access and the permissions that they require from the user to be executed.
The cloud editor
To create a bound script we need to open (or create) a spreadsheet. Once there we can open the editor by going to “Tools” => “Script Editor”.
A default file named Code.gs has already been created with a default function named myFunction. The name of the file is not important but the extension is. We can rename our file to index.gs and it will work the same. Let’s remove this default function and create our own named MYSTERY_MULTIPLY that will multiply the number we pass by a “mysterious” number.
/**
* Multiplies input by a mysterious value
*
* @param {number} input Number to multiply
*
* @customFunction
*/
function MYSTERY_MULTIPLY(input) {
return input * 10;
}
We can use JsDoc to provide auto complete and inline documentation the same way built-in functions have. For this to work, we need to add the @customFunction annotation. The choice of using upper case for the function name is just so it looks similar to the rest of built-in functions.
Our custom function is now working.
Apps Script limitations
Even though you can use modern ECMAScript syntax like const, let, arrow functions and the spread operator, App Script has still major limitations:
- Lack of support for ES6 modules
- Lack of support for testing
- Cumbersome 3rd party library mechanism
- Lack of support for Typescript
ES6 modules
When I started working with Apps Script it really surprised me that although they made an effort to support modern ECMAScript syntax by using V8 as a run time, there was no support for any kind of module system.
// this doesn't work
import * as myLib from './my-lib';
// this doesn't work either
const myLib = require('./my-lib')
By default every single function, even if they live in a different script, is global and that’s a huge problem when trying to go past the most simple functions. Here’s an example of what happens when you define two functions with the same name in different files:
// index.gs
function MYSTERY_MULTIPLY(input) {
return MULTIPLY(input, 10);
}
// lib1.gs
function MULTIPLY(a, b) {
return a * b; // multiplication (good)
}
// lib2.gs
function MULTIPLY(a, b) {
return a + b; // addition (wrong)
}
// output when used
=MYSTERY_MULTIPLY(5) => 15
As you can see, the function defined last takes precedence but we shouldn’t rely on this fact.
Lack of support for testing
Any serious developer will not write code without writing at least unit tests. Sure, you can find workarounds and create extra functions that will help you with debugging inside the cloud IDE or even use a wrapper around QUnit to be able to test inside that IDE, but those are very limited options. Even the QUnit wrapper comes with lots of limitations. Trust me, I tried.
Cumbersome 3rd party library mechanism
The cloud IDE has the notion of libraries and 3rd party software but they are not related to npm packages. This means that special wrappers need to be created and maintained for popular npm packages and needles to say, no one is doing that. In short, the built in library system is useless.
Lack of support for Typescript
These days I only use Typescript for any front end or back end project. Who wouldn’t? It’s an awesome tool. Needles to say Typescript is not supported as an alternative for the cloud editor.
Luckily we can overcome all those limitations by moving the development environment to our local machines using our regular tools like VSCode, Jest, Prettier and Git. All these can be done thanks to the Command Line Apps Script Projects or clasp for short. If you are curious of how this can be done, head over to my next blog post.
Felicitaciones.