Custom functions — your own =MYFUNC()

1. Your first custom function

In the Apps Script editor:

/**
 * Adds GST to an amount.
 *
 * @param {number} amount The amount before tax.
 * @param {number} rate GST rate in percent, e.g. 18.
 * @return The amount including GST.
 * @customfunction
 */
function WITHGST(amount, rate) {
  if (amount === '' || amount === null) return '';
  return Number(amount) * (1 + Number(rate) / 100);
}

Save. In the sheet type =WITHGST(1000, 18) → 1180.

The comment block (JSDoc) with @customfunction makes the function appear in autocomplete with your description and argument help.

2. Naming

• Function names are not case-sensitive in the sheet, but CAPITALS make them look like built-in functions.
• Names can't end with an underscore (those are private) and shouldn't clash with built-in functions.

3. Working with ranges

When a range is passed, the function receives a 2D array. Return a 2D array and the results spill down:

/**
 * Adds GST to every value in a range.
 * @param {number[][]} amounts Range of amounts.
 * @param {number} rate GST percent.
 * @customfunction
 */
function WITHGST_RANGE(amounts, rate) {
  if (!Array.isArray(amounts)) return WITHGST(amounts, rate);
  return amounts.map(row => row.map(v => (v === '' ? '' : Number(v) * (1 + Number(rate) / 100))));
}

=WITHGST_RANGE(B2:B100, 18) — one formula fills the whole column. Much faster than 100 separate calls.

4. A practical one: Indian digit grouping (lakh/crore)

/**
 * Formats a number with Indian digit grouping, e.g. 1234567 -> 12,34,567.
 * @param {number} n The number.
 * @customfunction
 */
function INRFORMAT(n) {
  if (n === '' || isNaN(n)) return '';
  return Number(n).toLocaleString('en-IN', { maximumFractionDigits: 2 });
}

=INRFORMAT(1234567) → 12,34,567. (The result is text, for display or labels.)

5. Rules custom functions must follow

• Only return a value. They cannot change other cells, format cells, or send emails.
• No services that need permission — no MailApp, GmailApp, DriveApp. (UrlFetchApp to public URLs is allowed.)
• 30-second limit per call.
• Deterministic: they recalculate only when their arguments change. =MYFUNC() with no arguments won't refresh by itself.
• Many hundreds of calls can show "Loading…" for a while — prefer the range version.

For actions (formatting, emails, writing elsewhere), use a normal function with a menu or trigger (next lesson).

6. Error messages

Throw an error to show it in the cell:

if (Number(rate) < 0) throw new Error('Rate cannot be negative');

The cell shows #ERROR! with your message on hover.

Common mistakes

Forgetting @customfunction (still works, but no autocomplete help). Trying to setValue inside a custom function ("You do not have permission"). Returning a 1D array when you wanted a column — return [[a],[b],[c]].