Google Apps Script: Custom Functions, Google Sheets
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 wants 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.
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.
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.
Plugging in a length value and then selecting the type of random string you want will result in the following examples:
The Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
/** * Creates a string of random letters at a set length. * * @param {number} len The total number of random letters in the string. * @param {number} num What type of random number 0. Alphabet with Upper and Lower. 1.Alphanumeric 2. Alphanumeric + characters * @return an array of random letters * @customfunction */ function RANDALPHA(len, num) { var text = ""; //Check if numbers if(typeof len !== 'number' || typeof num !== 'number'){throw new Error("Not a Number");}; //Check if there are two arguments if(arguments.length !== 2){throw new Error("Must have 2 arguments")}; var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;"; var charStringRange switch (num){ case 0: //Alphabet with upper and lower case charStringRange = charString.substr(0,52); break; case 1: //Alphanumeric charStringRange = charString.substr(0,62); break; case 2: //Alphanumeric + characters charStringRange = charString; break; default: //error reporting throw new Error("Choose 0,1 or 2 for random type.") } //The formula to choose a random character from the charStringRange var at the desired len for (var i = 0; i < len; i++) text += charStringRange.charAt(Math.floor(Math.random() * charStringRange.length)); return text; } |
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.
|
21 22 23 24 |
case 0: //Alphabet with upper and lower case charStringRange = charString.substr(0,52); break; |
var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;";
Case 1 (lines 25-28) sets the charStringRange to include the numbers.
|
25 26 27 28 |
case 1: //Alphanumeric charStringRange = charString.substr(0,62); break; |
var charString = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>-=_+:;";
Case 2 (lines 29-32) use the full length of the charString in the charStringRange.
|
29 30 31 32 |
case 2: //Alphanumeric + characters charStringRange = charString; break; |
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.
|
33 34 35 |
default: //error reporting throw new Error("Choose 0,1 or 2 for random type.") |
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
|
38 39 40 41 42 |
//The formula to choose a random character from the charStringRange var at the desired len for (var i = 0; i < len; i++) text += charStringRange.charAt(Math.floor(Math.random() * charStringRange.length)); return text; |
Error Handling
Error handling for custom functions 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 workaround is to simply create the Error message as a returned value – I opted for this in the original version 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.
|
13 14 |
//Check if numbers if(typeof len !== 'number' || typeof num !== 'number'){throw new Error("Not a Number");}; |
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.
|
15 16 |
//Check if there are two arguments if(arguments.length !== 2){throw new Error("Must have 2 arguments")}; |
3. The num choice is greater than 2. In the 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:
- Alphabetic
- Alphanumeric
- Alphanumeric + characters
|
33 34 35 |
default: //error reporting throw new Error("Choose 0,1 or 2 for random type.") |
Results on Google Sheet will look a little like this:
Decorators – JsDoc
So how do 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 section. Google Apps Script, like Javascript, uses decorators for API documentation called JSDoc.
|
1 2 3 4 5 6 7 8 |
/** * Creates a string of random letters at a set length. * * @param {number} len The total number of random letters in the string. * @param {number} num What type of random number 0. Alphabet with Upper and Lower. 1.Alphanumeric 2. Alphanumeric + characters * @return an array of random letters * @customfunction */ |
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:
Summary
The summary of the custom function you see when you hover over the function while typing it in or when you have selected the function under the Summary heading comes from the first comment you make (Line 2).
|
1 2 3 4 5 6 7 8 |
/** * Creates a string of random letters at a set length. * * @param {number} len The total number of random letters in the string. * @param {number} num What type of random number 0. Alphabet with Upper and Lower. 1.Alphanumeric 2. Alphanumeric + characters * @return an array of random letters * @customfunction */ |
Parameter Descriptions @param
The description of the two parameters comes from the two @param tags.
|
1 2 3 4 5 6 7 8 |
/** * Creates a string of random letters at a set length. * * @param {number} len The total number of random letters in the string. * @param {number} num What type of random number 0. Alphabet with Upper and Lower. 1.Alphanumeric 2. Alphanumeric + characters * @return an array of random letters * @customfunction */ |
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
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!
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
good work :), i will try to post my custom function here when i
m done with it hopefully soon ?m from Egypt btwalso like the pharaohs images, i
thank you very much! very clear and will be useful 100%.
can you please add how you would specify an optional argument in @param like it is shown in =SUM() function
Hi Valentin,
You can add optional arguments, when the code is run and the user does not use the argument it will return “undefined”. You can use and “if” statement or “switch” to indicate what you want to happen if the argument is left empty.
Here is an example:
<
~Yagi
Yagi, thank you so much for the quick response! Your time and sharing are highly appreciated!
To be honest I knew how to write optional condition in the script, I just did not know how to specify it in @param comment before the function. Now i know I have to add : (optional) after argument name.
Thank you for making that clear!
Great to hear. Happy coding!