Add Custom Function in Google Sheets

I’m sure that at some point in your life you had to use spreadsheet software like Microsoft Excel or Google Sheets.

They are relatively simple and straight forward to be used but also very powerful and easily extendable.

Today, we will look into Google Sheets and its ability to add code and custom functions so we can extend it.

A function is a piece of code that manipulates data from the spreadsheet to calculate a new value automatically for us. A very common example of such a function is SUM, that calculates the sum of a column or group of cells.

All of the spreadsheet software supports a lot of such functions that are pre-built into them but they also support the ability to extend them and write our own.

To write a custom function in Google Sheets we use a feature of it called Apps Script which is a rapid application development platform where we can write code in JavaScript directly in the browser that will then be executed in our spreadsheet.

To start writing we can go to Tools > Script editor in the top menu and that will bring up the online code editor.

In it, upon first open, we will have one file called Code.gs along with a blank starting function, named myFunction.

As a starting example, we will rename this function to DOUBLE and add an input parameter in its declaration. Inside the body of the function, we need to return a value and for this example, we will just multiply the input value with 2.

We can now save the script and if we go back to the spreadsheet and add some data to it, we can now reference this function in any cell and send in the data cell reference as the input for the value.

When executing this function, Google Sheets will shortly display a Loading message in the cell, but it will then display the returned value from the function.

These functions can do whatever we want but there are some limitations that we need to follow like:

Names must be unique and different than the ones used by the built-in functions The name must not end with an _, and Function names are typically written with uppercase letters, although this is not required.

Each function can return a single value as in our example but it can also return an array of values. This array will then be expanded into adjacent cells as long as they are empty. If they are not an error will be shown.

The function that we wrote is usable but for anyone else that might come to edit the document it will be unknown and the user will need to know it exists to use it. We can fix this by adding the function to the autocomplete list, the same as all of the built-in functions are.

To do this, we need to add a JsDoc @customfunction tag in front of the function as a comment where in this comment we can write a short explanation of what our function does.

Now with the comment added, when we start to write the function name, the function will be offered by the autocomplete, along with the function description.

The great power that these functions have, comes from the ability to call and interact with other tools and services from Google like Translate, Maps, connect to an external database, work with XML and others. By far, the most powerful feature for me is the ability to make an external HTTP request to any API or webpage and get data from it by using the UrlFetch service.

To demonstrate this, I’ll paste in a function that will convert US dollars to Swiss franc but it will not assume the currency rate but instead, it will retrieve it from an external API.

The function also uses the built-in cache service where it will not call the API for all of the calculations but it will call it once for the first calculation and then it will store that value in the cache.

Every other calculation will then be made with the cached value so the performance of them will be greatly improved and we won’t hit the server that often as rates do not change that quickly.

Since the API returns JSON, once we get the response from the server, we need to parse the JSON into an object and we can then get the rate, multiply it with the input value and return the new, calculated value to the cell.

If you find this interesting and you want to learn more, I’ll leave links below to additional resources.

https://developers.google.com/apps-script/guides/s...

https://developers.google.com/apps-script

If you liked the Instructable, then be sure to subscribe to my YouTube channel if you haven’t already and check out some of my other Instructables.

Cheers and thanks for reading.