Hacking Google Sheets with JavaScript

I knew Google Sheets were extensible.. but I didn't know that you can actually hack the menus, add custom functions, and gain programmatic access to loads of Google functionality. And all with a little JavaScript!

In this post we'll learn how to create custom Google Sheet functions, which are the basis for a lot of this functionality.

Prefer watching a video? Check out my Egghead lesson on this topic!

Built-in Google Sheet Functions

For those newer to spreadsheets, the concept of functions already exists in the form of built-in functions that you can call.

There are mathematical functions like SUM and COUNTUNIQUE, date functions like TODAY (to get today's date) and DAYS (to get the number of days between two dates). And there are functions that allow you to access Google APIs, like GOOGLEFINANCE, which allows you to include auto-refreshing stock prices in your spreadsheets.

And Google Sheets doesn't really care whether a function is built-in or custom - they're called in the same manner. To call a function, just type = and then the function name. For instance:

Our First Custom Function: rateMyDay()

Now let's create our first custom function, which takes a numeric rating of our day and returns a one-word description of it (e.g. - 4/10 could return meh.)

We'll start by going to the Tools menu and clicking Script Editor, where we'll create a new project for our scripts.

New projects begin with this sample function:

function myFunction() {

}

So let's add our function, which takes a decimal value and returns some text:

function rateMyDay(decimal) {
    return decimal > 0.5 ? 'Pretty pretty good' : 'Eh.';
}

And now to call our function, well go back to our Google Sheet, and call it with a value from our day:

Input values

Notice that we've supplied a cell reference here, rather than an actual value.

Google functions accept two types of arguments:

1. A value (like 1 or 2 or purple).

2. A cell reference, or a range of cells (e.g. A2:B3, which refers to all cells between those two coordinates).

   How does this look in real life?

In all cases, Google Sheets will supply an actual value to the function. So how does that look when we're referring to a range of cells?

2-dimensional array syntax

An important thing to know when writing Google Sheets functions is that ranges are converted into a two-dimensional array syntax that refers to individual rows of cells:

[[1,2,3], [a,b,c]]    // 2 rows, where the first has numbers and the second has letters

This applies to both function arguments and return values. That means that if we accept a range as an argument, Google Sheets will convert that range to the two-dimensional array, and we'll need to iterate over it in order to do whatever we want to do with the values.

And if we want to return a range of cells - meaning we want Google Sheets to place our results in a range of cells, not just one - we signify this range using the same two-dimensional syntax.

Note that Google Sheets will override whichever cells currently occupy the ones that you want to fill. So if you return a range of values, multiple cells will be overridden.

Returning a range of cells

So let's say we wanted to print some dummy data for 5 days. We can have our function create a five-member array (i.e. five rows), and put our decimal rating and the ratingInWords on each row:

function rateMyDay(decimal) {
  const ratingInWords = decimal > 0.5 ? 'Pretty pretty good' : 'Eh.';

  return Array(5).fill([decimal, ratingInWords]);    // [[0.5, 'Eh'], ...]
}

Conclusion

This is just a fraction of what custom functions can do, but it's enough to do quite a lot of damage.

You can also trigger functions from buttons and drawings, add custom menus, and access a range of functionality from the entire Google suite. As a caveat, note that built-in spreadsheet functions are not directly accessible from custom functions. So you can't, for instance, access the GOOGLEFINANCE function from within your custom function without some sneaky workarounds.