Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Apps Script

Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or want to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.

Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.

Custom Function Decorators Google Apps Script

Let’s look at the creation process through a recent example I created:

The Example – RANDALPHA

In our example custom function, RANDALPHA, we want to create a random string of any number of characters in a designated cell. We also let the user decide if they just want a purely alphabetic string of random numbers, one that is alphanumeric or one that is alphanumeric with the addition of characters.

Once the  RANDALPHA function is saved to the spreadsheets attached Google Script in Script Editor, it is indexed with all the other functions (formulas) in Google Sheets for your specific sheet.

Custom Functions Are Indelible - Google Sheets

Just like when you add values to a regular function in Google Sheets, you can create a list of descriptors and examples for your Custom Function to help the user.

Custom Function Example Google Sheets

Plugging in a length value and then selecting they type of random string you want will result in the following examples:

RANDALPHA Examples Complete

The Code

The Function

All custom function must return a value. There are some limitations to what the custom function can return and it is important to consult the documentation before you plan to create a custom function.

Let’s jump to line 10, the start of the function. As you can see, the RANDALPHA function takes two parameters (or arguments). The first one is len or the length of the random string you want. The second parameter is num or the number reference for the type of random sequence you want.

 function RANDALPHA(len, num)

We then set an empty string variable on line 11. Our code will be adding to this and returning the desired value or returning an error.

On line 16, the charString variable holds a string of all the letter, numbers and characters we will be using to create our random sequence.

charStringRange (line 17), is an empty variable that will take a specific selection of characters in the charString depending on the selected num case in switch( lines 18-32).

In case 0 of switch, we create a substr starting at the zeroeth character, running 52 characters long until it reaches the end of the uppercase and lowercase letters.

var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;";

Case 1 (lines 25-28) sets the charStringRange to include the numbers.

var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;";

Case 2 (lines 29-32) use the full length of the charString in the charStringRange.

var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;";

I’ve numbered our switch cases starting at zero to match the standard practice in Google Sheets.

The final choice for switch is the default, which will catch any other instance and, in our case issue an error warning.

Lines 38-42 is the core of our custom formula. Which randomly selects a character in the charStringRange (charAt) by multiplying the length of the string by a random number between 1 and 0 (Math.random()) and then rounding down this number (Math.floor).Finally this character is added sequentially to the text variable for the desired length of character (len) before being returned to the cell in the Google Sheet.

Attribution: csharptest.net

Error Handling

Error handling for custom function is not 100% similar to Google Sheets built-in functions. The trailing information of the custom function error leaves a code line reference, which is irritating. The only other work around is to simply create the Error message as a returned value – I opted for this in the original verson of the RANDALPHA function.

Nevertheless, to create an error message for your custom function, simply:

throw New error("Error message")

I have done this for the RANDALPHA custom function in three locations.

1. To check if both our len and num parameters are numbers. On line 14, we run a short if statement to determine if len and num are numbers by using the typeof operator. If the typeof variable in len or num does not equal a number then we return “Not a Number” to the spreadsheet cell.

2. More than 2 arguments. While the “Not a Number” error will throw an error if there are less than two parameters, we also need to throw an error if there are more than two parameters.

3. The num choice is greater than 2. In our default case of our switch expression we catch any other number greater than two and return and error warning the user that there are only three choices of random number starting at the zeroeth choice:

  1. Alphabetic
  2. Alphanumeric
  3. Alphanumeric + characters

Results on Google Sheet will look a little like this:

RANDALPHA Errors - Google Sheets

Decorators –  JsDoc

So how to we get all the at helpful information when we type in our new custom formula in Google Sheets.

If you take a look at the top of our code you will see a detailed comment sections. Google Apps Script, like Javascript, uses decorators for API documentation called JSDoc.

In the comments section you will see certain lines that start with a “@”. These are tags. Google Sheets will look through your attached Google Apps Script and if it finds these tags it will try and use them.

This block of comments is placed directly above every custom function you have in your spreadsheet. These comments will then reference the function below it.

@customfunction

The @customfunction tag tells Google Sheet that the below function is a custom function and should be added to the index of built-in functions when the user looks for a function. Like this:

Custom Functions Are Indelible - Google Sheets

Summary

Custom Function Description - Google Sheets

Custom Function Summary - Google Sheets

The summary of the custom function you see when you hover over the function while typing it in or when you have select the function under the Summary heading comes from the first comment you make (Line 2).

Parameter Descriptions @param

Custom Function Parameter Descriptions - Google Sheets

The description of the two parameters comes from the two @param tags.

The @param tag is a little confusing at first but once you understand it, it will be super easy to apply. You will need to create as many @param tags as you have parameters(arguments) in your function. The @param tag first takes a “type” like number, text, array etc inside the curly braces”{}”. after the curly braces we add the name of the @param (in our case len and num). Make sure these are identical to the parameter arguments in your function. Finally you write a summary of what the parameter is or requires.

@param {type} paramName Summary

@param {number} len The total number of random letters in the string.

The Example

Custom Function Example Text - Google Sheets

The example is generated by referencing the function title and the two @param details in the curly braces “{}”

@Return

The @Return tag describes what value is returned and can be left out as it is ignored by Google Sheets. However it is a useful note to have.

Conclusion

Custom functions are great, but one unfortunate drawback is that they make calls to the server, which means that they will take longer to generate a result as opposed to build-in functions that run client-side.

Creating a Google Sheets custom function in Google Apps Script is a fairly simple process. Just remember to add your JsDoc decorators above each of your custom functions so that they can be indexed in Google Sheets.

Good luck.

P.S. Got a cool custom function you created and want to share? Wack it in the comments below!

Leave a Reply