Custom functions (=MYFUNCTION)

Your first custom function

In a bound script (Extensions → Apps Script):

/**
 * Adds GST to an amount.
 *
 * @param {number} amount The amount before tax.
 * @param {number} rate The GST rate, e.g. 0.03 for 3%.
 * @return {number} The amount including GST.
 * @customfunction
 */
function WITH_GST(amount, rate) {
  return amount * (1 + rate);
}

Save, go back to the sheet and type =WITH_GST(48500, 0.03) → 49955.

The comment block with @customfunction makes your function appear in formula autocomplete with its description. Function names are usually written in capitals so they look like built-in formulas.

Accepting a range

When you pass a range like A2:A10, your function receives a 2D array. When you pass a single cell, it receives a single value. Handle both:

/**
 * Converts weight in grams to tola (1 tola = 11.6638 g).
 *
 * @param {number|Array<Array<number>>} grams Weight in grams, or a range.
 * @return Weight in tola, rounded to 3 decimals.
 * @customfunction
 */
function TOLA(grams) {
  const convert = g => (typeof g === 'number' ? Math.round((g / 11.6638) * 1000) / 1000 : '');
  if (Array.isArray(grams)) {
    return grams.map(row => row.map(convert));
  }
  return convert(grams);
}

=TOLA(B2) returns one value; =TOLA(B2:B20) fills 19 cells down — just like an array formula.

Returning several values

Return a 2D array to spill results over multiple cells:

/**
 * Splits a full name into first and last name.
 * @param {string} fullName
 * @return First and last name in two cells.
 * @customfunction
 */
function SPLIT_NAME(fullName) {
  const parts = String(fullName).trim().split(/\s+/);
  const first = parts.shift() || '';
  return [[first, parts.join(' ')]];   // one row, two columns
}

Rules and limits

Custom functions run every time the sheet recalculates, so Google keeps them restricted:

• They must finish in about 30 seconds.
• They can only return values — they cannot change other cells, formatting, or create sheets.
• They cannot use services that need your personal authorization: no GmailApp, no DriveApp, no opening other spreadsheets. (UrlFetchApp works.)
• They only recalculate when their inputs change. A function with no inputs, or one that reads new Date(), won't refresh on its own.
• Hundreds of copies of a slow function make the sheet sluggish — prefer one call over a range (=TOLA(B2:B500)) rather than 500 separate =TOLA(B2) cells.

If you need to send emails or change other cells, use a menu item or a trigger instead (next lessons).

Key points

• Any function in a bound script can be called from a cell.
• Ranges arrive as 2D arrays; return 2D arrays to fill many cells.
• Add @customfunction for autocomplete help.
• Custom functions only compute and return — no side effects.