How to write a custom function library for Google Sheets
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.
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:
- Versioning of your code
- Control over development or production versions
- Easy distribution of your code
With libraries you are also making a tradeoff between ease of use and maintenance. The main drawbacks of the solutions are:
- Less intuitive function names
- Help information takes is difficult to implement
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.
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:
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.
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
