google-apps-script Create a custom function for Google Sheets Basic Example


To avoid unsightly #DIV/0 errors in a spreadsheet, a custom function can be used.

 * Divides n by d unless d is zero, in which case, it returns
 * the given symbol.
 * @param  {n}  number The numerator
 * @param  {d}  number The divisor
 * @param  {symbol}  string The symbol to display if `d == 0`
 * @return {number or string} The result of division or the given symbol
 * @customfunction
function zeroSafeDivide(n, d, symbol) {  
  if (d == 0)
    return symbol;
    return n / d;

To use the function, it needs to be bound to a spreadsheet using the script editor (Tools -> Script editor...). Once the function is added, it can be used like any other google sheets function by calling the function in a cell's formula.

enter image description here

Note how the function shows up in autocomplete when typed into a formula. This is due to the multi-line comment above the function declaration which is used to describe what the function does similar to JSDoc and Javadoc. To have the formula show up in autocomplete, the @customfunction tag must be specified in the comment.