Custom functions have been in Google Apps Script since it’s public launch in 2009. These let you extend Google Sheets function list allowing you to create and share your own spreadsheet formula. The official Google Developer docs for Custom Functions are very comprehensive and in this post I just want to highlight one pattern for detecting and handling user inputs.
Similar to built-in functions like FIND, TRIM, and VLOOKUP you will probably need to handle both single values and ranges. For example, even a function like TRIM, whilst using a string or reference to a cell containing a string to be trimmed, when combined with ARRAYFORMULA can be used to use array references in non-array functions e.g. =ARRAYFORMULA(TRIM(A1:A3)).
The documentation highlights using Array.map() as a way that you can detect if the input is an array and if so recursively iterate over that array. Below (also available in this gist) is an example of a custom function that generates a SHA-256 hash of text input:
/**
* Generates a SHA-256 hash of a text input.
*
* @param {String||Array} value to SHA-256 hash.
* @param {String} key to use to generate the hash with.
* @return the SHA-256 hash of the value
* @customfunction
*/
function HASH(value, key) {
if (typeof key !== 'string'){ // test the key is a string
throw "Key needs to be a text value";
}
if (value.map) { // Test whether input is an array.
return value.map(function(v){return HASH(v, key)}); // Recurse over array if so.
} else { // The bit that actual creates the hash
// based on https://stackoverflow.com/a/27933459
return Utilities.computeHmacSha256Signature(value, key).reduce(function(str, chr) {
chr = (chr < 0 ? chr + 256 : chr).toString(16);
return str + (chr.length == 1 ? '0' : '') + chr;
}, '');
}
}
In the above example our function has two variables value and key. The key is used to hash the value. In this scenario let’s assume that the key can only be a string input while value can either be a string or an array. In the above code we detect if key is not a string a throw an error for the user. If the value is mappable we recurse over the array sending the array value v to our function HASH also passing the key. When a string value is sent to the HASH function we compute and return the SHA-256 hash of a text input.
The result of using this formula with different values inputs is shown below:
If you have other custom function tips and tricks feel free to share them in the comments 🙂
Update: Eric Koleda has a helper function you might also want to look at:
Nice article! A while back I created a general purpose helper function called "multimap" that allows you to do this automatically and across multiple parameters:https://t.co/AntVsno18H
— Eric Koleda (@erickoleda) April 26, 2019
