Getting started Updated November 2026

Custom functions

Any function in a bound script with a return value can be used as a formula: write function DOUBLE(x) { return x * 2; } and type =DOUBLE(A1) in a cell. A range argument arrives as a 2D array and a returned 2D array spills into neighboring cells. Custom functions cannot write cells.

Syntax

/** @customfunction */ function NAME(arg1, arg2) { return value; }

Part	What it is
`@customfunction`	JSDoc tag that adds the function to formula autocomplete
`single cell argument`	Arrives as a number, string, boolean or Date
`range argument`	Arrives as a 2D array, rows of columns, even for one column

Examples

/**
 * Doubles a number.
 * @param {number} x The value.
 * @return The value times two.
 * @customfunction
 */
function DOUBLE(x) {
  return x * 2;
}

Result: =DOUBLE(21) returns 42

/** @customfunction */
function SUMSQUARES(range) {
  return range.flat().reduce((sum, v) => sum + (Number(v) || 0) ** 2, 0);
}

Result: =SUMSQUARES(A1:A3) with 1, 2, 3 returns 14

/** @customfunction */
function UPPER_ALL(range) {
  return range.map(row => row.map(v => String(v).toUpperCase()));
}

Result: =UPPER_ALL(A1:B2) spills a 2 x 2 block of uppercase text

Returning a 2D array fills the cells to the right and below.

Common mistakes

#NAME? after writing the function: the script was not saved, or the function lives in a standalone project instead of the sheet's bound script.
You do not have permission to call SpreadsheetApp.getUi or GmailApp: custom functions run anonymously and can only compute and read; use a menu item or trigger for writes and email.
The result is #ERROR with Result was not a number: the function returned undefined because a code path has no return, so return a value on every path.
Function works on one cell but breaks on a range: range input is always a 2D array, so use range.flat() or loop rows and columns.

Practice Custom functions

These drills check your answer on live data, free, no account needed.

Total of a listReturn the total of an array of numbers. An empty array totals `0`. Numbers can be negative or have decimals.
Capitalize each wordWords are separated by single spaces. Uppercase the first character of every word and lowercase the rest of it. Keep the spacing exactly as it is, including repeated spaces and spaces at the ends.
Count the vowelsCount the vowels a, e, i, o and u in `s`, in upper or lower case. Every other character is ignored, including y and accented letters such as é.

Questions

Can a custom function write to another cell?

No. Custom functions can only return values. To write elsewhere, run a normal function from a menu, button or trigger and use setValue.

Why does my custom function show Loading forever?

It is slow or errors silently. Keep custom functions light, avoid UrlFetchApp in each cell, and test the function from the editor with a sample argument to see the error.

Why do custom functions recalculate or stay stale?

They recalculate only when an argument changes. A function with no arguments that reads the clock is cached, so pass the cells it depends on as arguments.

Your first Apps ScriptWrite and run your first Google Apps Script in Sheets: open the editor, write a function that reads and writes cells, run it, authorize it and read the log.
map, filter and reduceUse map, filter and reduce on Google Sheets data in Apps Script: transform rows, keep rows that match a condition, sum a column, chain them, write back.
getRange and getValuesRead Google Sheets data with Apps Script: getRange by A1 notation or row and column numbers, then getValue for one cell or getValues for a 2D array of rows.
try, catch and finallyHandle errors in Google Apps Script with try catch finally: catch exceptions from sheets, email and APIs, read err.message and err.stack, log and rethrow.