How to write a custom function library for Google Sheets

Jasper Duizendstra
Dec 9, 2020 · 3 min read

In this article I will showcase one of the many ways you can customize your Google Workspace environment. We will implement two custom functions in Google Sheets. With these functions as an example I will demonstrate how to use Google Apps Script Libraries to organise and distribute your solution. There are multiple ways to distribute your Apps Script code within the Google Workspace, and this article will help you decide if this library method is right for you.

Image for post
Image for post
Photo by Gabriel Sollmann on Unsplash

Custom functions are a great way to address specific use cases in Google Sheets. In this case I will focus on how to maintain the functions, not on the functions themselves, therefore I based my example functions on the examples in the Quickstart: Menus and Custom Functions | Apps Script.

Container bound scripts

Google Sheets use Container-bound Scripts | Apps Script. This means that the script comes with the sheet and if you make a copy of the sheet you will automatically get a copy of the script. This makes it difficult to maintain the custom functions in the script.

Moving the functions into a library solves the maintenance problem. However it introduces a new problem. Once the functions are in the library they are not visible for the Google Sheet. To solve this we need to bring the functions back into the container bound script of the Google Sheet.

Why use a library to distribute your functions

With a library you will get:

With libraries you are also making a tradeoff between ease of use and maintenance. The main drawbacks of the solutions are:

Libraries | Apps Script describes how to create a library. The example code below uses the construction described in this article Structure and simplify your Google Apps Script (with) V8.

Image for post
Image for post

In the example we moved the two custom functions into the customFunctions object. These functions are placed into the allFunctions object.

The getFunctions function returns a function based on the name and passes the parameters to the custom function.

The next step is to add the library. In the example below the name of the library is sheetFunctions. Once the library is available we can make the functions available.

In the example above we implemented the functionality in two ways. Row one to seven enables this usage of the custom functions:

Image for post
Image for post

Row nine to eighteen will make the help information available. This eliminates the drawback of using a library, however it makes it difficult to introduce new functions or change the parameters of the function. The implementation of the function remains in the library.

Image for post
Image for post

In this article I have shown how to move custom functions to a library.

There are other ways to maintain custom functions in a Google Sheet, implementing an add-on (Extending Google Workspace with Add-ons | Google Workspace Add-ons) is also a great way to maintain and distribute your custom functions. Using a library can help when you don’t want to use an add-on and still want to be able to maintain your code after distribution.

Read the guides Custom Functions in Google Sheets | Apps Script and Libraries | Apps Script if you would like to dive deeper into the functions and libraries.

Resources:

Custom Functions in Google Sheets | Apps Script

Quickstart: Menus and Custom Functions | Apps Script

Structure and simplify your Google Apps Script (with) V8

Google Cloud - Community

Google Cloud community articles and blogs

Jasper Duizendstra

Written by

Freelance Google Cloud Platform Architect, Google Workspace GDE

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Jasper Duizendstra

Written by

Freelance Google Cloud Platform Architect, Google Workspace GDE

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store