Skip to content

Basics

Custom functions in Google Sheets

A custom function runs from a cell formula. Each call has 30 seconds, receives cell values instead of ranges, and cannot use your personal data or edit other cells.

1 of 8

How custom functions differ from normal scripts

A normal Apps Script function runs when you click Run in the editor, click a custom menu, or set up a trigger. A custom function is typed directly into a spreadsheet cell as a formula, such as =DOUBLE(A2), and recalculates on its own whenever referenced cells change.

Google Sheets can call any top-level function declaration whose name does not end in an underscore. Arrow functions and functions declared inside objects or classes are not available. The @customfunction tag in the comment block immediately above the function adds it to the formula autocomplete list. The tag is not required for the formula to work.

Because custom functions run inside a formula, Google Sheets treats them as pure calculations. They compute a result from their inputs and return it to the spreadsheet.

A minimal custom function with JSDoc metadata 
/**
 * Multiplies a number by two.
 *
 * @param {number} input The number to double.
 * @return {number} The input multiplied by two.
 * @customfunction
 */
function DOUBLE(input) {
  return Number(input) * 2;
}

2 of 8

Custom functions cannot ask for permission

Normal scripts open an authorization dialog the first time they run so you can review the permissions they need. Custom functions run in the AuthMode.CUSTOM_FUNCTION mode, which never shows an authorization dialog.

Because custom functions cannot ask for your consent, they cannot call services that access personal data. Calling GmailApp, DriveApp, CalendarApp, or ContactsApp fails immediately with "You do not have permission to call...".

Services that do not require personal authorization are allowed, including UrlFetchApp to query public websites. If a calculation needs private user data, write a normal script that runs from a custom menu or a time-driven trigger and writes the values into the sheet in advance.

3 of 8

Custom functions cannot edit other cells or open dialogs

A custom function can only return a value to the cell that called it and to the adjacent cells below and to the right if the result is an array. It cannot change other cells in the spreadsheet.

Calling methods that change the sheet, such as range.setValue(), range.clear(), sheet.appendRow(), or sheet.deleteRow() throws an exception at runtime. Methods that use the user interface, such as SpreadsheetApp.getUi() or Browser.msgBox(), also fail because formula recalculation has no interactive user session.

4 of 8

Range arguments arrive as values

When a formula passes a range such as A1:B10, your function receives the cell values in that range. It never receives a SpreadsheetApp.Range object.

A single cell argument arrives as a primitive value: a number, a string, a boolean, or a Date object. An empty cell arrives as an empty string. A multi-cell range arrives as a two-dimensional JavaScript array of rows, where each row is an array of cell values.

Writing input.getValue() throws "TypeError: input.getValue is not a function" because arrays and numbers do not have Range methods. To support both single cells and range arguments, check whether the input is an array.

Handle both single cells and multi-cell ranges in one function 
/**
 * Converts temperatures from Celsius to Fahrenheit.
 *
 * @param {number|string|boolean|Date|null|undefined|Array<Array<number|string|boolean|Date|null|undefined>>} input A single cell or a rectangular range.
 * @return {number|string|Array<Array<number|string>>} The converted temperature or grid of temperatures.
 * @customfunction
 */
function CELSIUS_TO_FAHRENHEIT(input) {
  if (Array.isArray(input)) {
    return input.map(function(row) {
      return row.map(function(cell) {
        return convertOneCell(cell);
      });
    });
  }
  return convertOneCell(input);
}

/**
 * @param {number|string|boolean|Date|null|undefined} cell
 * @return {number|string}
 */
function convertOneCell(cell) {
  if (cell === '' || cell === null || cell === undefined) {
    return '';
  }
  const celsius = Number(cell);
  if (isNaN(celsius)) {
    throw new Error('CELSIUS_TO_FAHRENHEIT needs a number. Received: ' + cell);
  }
  return Math.round((celsius * 9 / 5 + 32) * 10) / 10;
}

5 of 8

Return a value or a table of values

A custom function can return a primitive value (a number, string, boolean, or Date), a one-dimensional array, or a two-dimensional array. When you return a two-dimensional array, Google Sheets fills the cells below and to the right of the formula with the values.

If those cells already contain data, Sheets displays #REF! with the message "Array result was not expanded because it would overwrite data in...". Clear those cells so the result has room.

Custom functions cannot return plain JavaScript objects like { total: 100 } or Promises from async functions. Returning a plain object displays [object Object] or #VALUE!. An async function returns a Promise, which Sheets cannot display.

6 of 8

Naming rules, private underscores, and collisions

By convention, custom function names are written in capital letters so they look like standard Google Sheets formulas. These naming mistakes cause confusing errors:

  • A function name that ends with an underscore, such as DOUBLE_(), is private in Apps Script. Google Sheets cannot see private functions, so typing =DOUBLE_(A1) results in #NAME? Unknown function.
  • A custom function named after a built-in Sheets formula, such as SUM or INDEX, never runs. Sheets uses its built-in function instead.
  • A name that matches a cell reference, such as A1 or R1C1, confuses the formula parser.

7 of 8

Formula caching and the 30-second timeout

A normal Apps Script execution can run for up to six minutes. A custom function must finish each call within 30 seconds, because Google Sheets may calculate many formulas at the same time when a spreadsheet opens.

Google Sheets caches custom function results based on the formula arguments. If the cell arguments do not change, Sheets does not re-run your function, even when you reload the browser tab.

Calling non-deterministic functions like Math.random() or Date.now() returns the cached value until you edit an argument. If your calculation needs to refresh with the current time, pass a timestamp cell or a trigger-updated cell as an explicit formula argument.

8 of 8

JSDoc comments control formula autocomplete and help text

Google Sheets reads the JSDoc comment block above your custom function to build the formula help card that appears as users type in a cell. The first lines of the comment become the function summary.

Each @param tag describes one argument. The parameter name in the @param tag must match the argument name in the function declaration. If the names do not match, Sheets cannot connect your description to the argument, leaving the help box empty.

The @return tag describes the output shown at the bottom of the formula card. Put the @customfunction tag on its own line in the same comment block.

Remember

The short version

  • Check whether an argument is an array before you use it, because a range argument arrives as a 2D array of values.
  • If a calculation needs Gmail, Drive, or other personal data, run a normal script from a custom menu or an installable trigger and write the values into the sheet.
  • To make a result refresh, pass a cell that changes, such as a timestamp, as a formula argument.
  • A function whose name ends with an underscore is private, so a formula that calls it shows #NAME?.
  • Each call must finish within 30 seconds, and Sheets reuses the cached result until an argument changes.
SpreadsheetApp.Range

Keep going

Put it to work

All basics