Hacking Google Sheets with JavaScript
Tags: javascript
Published: 2020-08-14 15:05:02

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](https://egghead.io/lessons/javascript-create-custom-javascript-functions-for-google-sheets) 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:

![Google finance](https://i.ibb.co/0cp3d5g/ticker.png)
___

### 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:

```jsx
function myFunction() {
  
}
```

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

```jsx
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:

![Day log](https://i.ibb.co/m6jSMp3/daylog.png)
___


### 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?

![range](https://i.ibb.co/wMF43Zw/range.png)

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:

```jsx
[[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:

```jsx
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.