Custom functions in Google Sheets

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; it 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.

Normal scripts open an authorization dialog the first time they run so you can review the permissions they need. Custom functions run in a silent authorization sandbox called AuthMode.CUSTOM_FUNCTION, which never opens an authorization prompt.

Because custom functions cannot ask for user consent, Google forbids them from calling 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.

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 modify arbitrary cells across the workbook.

Calling mutating methods 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.

Think of custom functions like built-in formulas such as SUM or VLOOKUP. They read the arguments you pass them, perform math or text manipulation, and return the answer.

A common mistake is assuming that passing a range like A1:B10 hands your function a SpreadsheetApp.Range object. In Google Sheets, formulas pass raw cell values, never Range objects.

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 raw arrays and numbers do not have Range methods. To support both single cells and range arguments, check whether the input is an array.

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 spills the values across and down into adjacent cells.

If adjacent cells already contain data, Sheets displays #REF! with the message "Array result was not expanded because it would overwrite data in...". Clear the blocking cells so the grid can expand.

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

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

First, function names ending with an underscore, such as DOUBLE_(), are treated as private in Apps Script. Google Sheets cannot see private functions, so typing =DOUBLE_(A1) results in #NAME? Unknown function.

Second, do not name a custom function after an existing built-in Sheets formula, such as SUM or INDEX. Built-in formulas take priority and shadow your script. Third, avoid names that match cell coordinates, such as A1 or R1C1, which confuse the formula parser.

A normal Apps Script execution can run for up to six minutes. Custom functions get a 30-second execution timeout per call because Google Sheets may evaluate dozens of cell formulas simultaneously 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 a cached value that stays frozen 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.

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. Always place the @customfunction tag on its own line within the same comment block.