Write custom functions for Sheets formulas
Write Apps Script custom functions that Sheets formulas call, accept a cell or a range, fill cells with a 2D array, and stay inside their limits.
Write and use =DISCOUNTED_PRICE(...) and =PRICE_WITH_TAX(...) in a sheet, pass one cell or a whole range, read their errors, and decide when a menu or trigger script fits better.
Write two custom functions, DISCOUNTED_PRICE and PRICE_WITH_TAX, and call them from spreadsheet formulas such as =DISCOUNTED_PRICE(B2:B5, 10%). A custom function is an Apps Script function that a formula calls. It receives the values the formula passes and returns a value, or a two-dimensional array, that Sheets places in the cells. The custom function does not call setValues(). You need a new spreadsheet and the array methods from Work with JavaScript arrays.
Write a function a cell can call
Any top-level function declaration in a spreadsheet's bound script can be called from a cell, as long as its name follows the naming rules described later in this chapter:
function HALF(value) {
return value / 2;
} Save that function in the spreadsheet's script project. Entering =HALF(10) in a cell displays Loading... briefly and then 5, and =HALF(A1) returns half of the value in A1 (custom functions guide).
The project must be bound to the spreadsheet. Custom functions "start out bound to the spreadsheet they were created in," and a function written in one spreadsheet is not available in another unless you copy the script, copy the spreadsheet, or publish an Editor add-on (sharing custom functions). Anyone who can edit the spreadsheet can also edit its bound script.
Set up the price sheet
Create a new blank Google Sheet for this exercise. Do not use a live pricing or finance spreadsheet.
Rename its first tab to
Prices.Copy the comma-separated text from
sample.csv, shown below, into cellA1, or import the file with File > Import.
Item,Price,Sale price,With tax
Notebook,40,,
Pen set,12.5,,
Desk lamp,64,,
Gift card,,,
If you pasted the text, select column A, choose Data > Split text to columns, and set the separator to Comma.
Check that
Prices!A1:D5has the headersItem,Price,Sale price, andWith tax, thatC2:D5is empty, and thatB5(the gift card price) is empty.Choose Extensions > Apps Script. Replace the default code in
Code.gswith the completeCode.gsexample file.In Project Settings, select Show "appsscript.json" manifest file in editor, then replace its contents with the included
appsscript.jsonfile. Save both files.
const PRICE_DECIMALS = 2; // Number of decimal places kept in each returned price.
/**
* Returns a price after a discount. Accepts one price or a range of prices.
*
* @param {number|Array<Array<*>>} price A price, or a range of prices such as B2:B5.
* @param {number} discountRate The discount as a rate from 0 to 1, such as 10% or 0.1.
* @return The discounted price, or one discounted price per cell of the range.
* @customfunction
*/
function DISCOUNTED_PRICE(price, discountRate) {
const rate = requireRate_(discountRate, 'discountRate');
return mapValueOrRange_(price, 'price', function (amount) {
return roundPrice_(amount * (1 - rate));
});
}
/**
* Returns a price with sales tax added. Accepts one price or a range of prices.
*
* @param {number|Array<Array<*>>} price A price, or a range of prices such as B2:B5.
* @param {number} taxRate The tax as a rate from 0 to 1, such as 8% or 0.08.
* @return The price including tax, or one taxed price per cell of the range.
* @customfunction
*/
function PRICE_WITH_TAX(price, taxRate) {
const rate = requireRate_(taxRate, 'taxRate');
return mapValueOrRange_(price, 'price', function (amount) {
return roundPrice_(amount * (1 + rate));
});
}
/**
* Logs sample results so you can check the calculations from the editor.
*
* @returns {void}
*/
function testPriceFunctions() {
Logger.log(DISCOUNTED_PRICE(40, 0.1));
Logger.log(JSON.stringify(DISCOUNTED_PRICE([[40], [12.5], ['']], 0.1)));
Logger.log(JSON.stringify(PRICE_WITH_TAX([[36], [11.25]], 0.08)));
}
/**
* Applies a calculation to one value, or to every cell of a two-dimensional range.
* Blank cells stay blank so the result has the same shape as the input.
*
* @param {*} input One cell value, or a two-dimensional array of cell values.
* @param {string} argumentName The argument name used in error messages.
* @param {function(number): number} calculate The calculation for one number.
* @returns {*} One result, or a two-dimensional array of results.
*/
function mapValueOrRange_(input, argumentName, calculate) {
if (!Array.isArray(input)) {
return calculateCell_(input, argumentName, calculate, '');
}
const rows = /** @type {Array<Array<*>>} */ (input);
return rows.map(function (row, rowIndex) {
return row.map(function (cell, columnIndex) {
const position = ' (range row ' + (rowIndex + 1) + ', column ' + (columnIndex + 1) + ')';
return calculateCell_(cell, argumentName, calculate, position);
});
});
}
/**
* Calculates one cell, keeping a blank cell blank and rejecting text or dates.
*
* @param {*} value The cell value.
* @param {string} argumentName The argument name used in error messages.
* @param {function(number): number} calculate The calculation for one number.
* @param {string} position Where the value sits in a range, or an empty string.
* @returns {number|string} The result, or an empty string for a blank cell.
*/
function calculateCell_(value, argumentName, calculate, position) {
if (value === '' || value === null) {
return '';
}
if (typeof value !== 'number' || !isFinite(value)) {
throw new Error(argumentName + ' must be a number' + position + '. Received: ' + String(value));
}
return calculate(value);
}
/**
* Checks that a rate is a single number from 0 to 1.
*
* @param {*} rate The rate argument from the formula.
* @param {string} argumentName The argument name used in error messages.
* @returns {number} The checked rate.
*/
function requireRate_(rate, argumentName) {
if (typeof rate !== 'number' || !isFinite(rate) || rate < 0 || rate > 1) {
throw new Error(argumentName + ' must be one number from 0 to 1, such as 10% or 0.1.');
}
return rate;
}
/**
* Rounds a calculated price to PRICE_DECIMALS decimal places.
*
* @param {number} amount The unrounded price.
* @returns {number} The rounded price.
*/
function roundPrice_(amount) {
const factor = Math.pow(10, PRICE_DECIMALS);
return Math.round(amount * factor) / factor;
}
{
"timeZone": "Etc/UTC",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
The manifest declares no OAuth scopes. The custom functions use only JavaScript, and testPriceFunctions calls only Logger.log().
Use the formulas
Return to the spreadsheet. In cell
C2, enter=DISCOUNTED_PRICE(B2:B5, 10%).In cell
D2, enter=PRICE_WITH_TAX(C2:C5, 8%).
No authorization dialog appears. After Loading..., the two columns fill with the calculated values:
| Item | Price | Sale price | With tax |
|---|---|---|---|
| Notebook | 40 | 36 | 38.88 |
| Pen set | 12.5 | 11.25 | 12.15 |
| Desk lamp | 64 | 57.6 | 62.21 |
| Gift card |
Only C2 and D2 contain formulas. The arrays those two calls returned fill C3:C5 and D3:D5.
Change the notebook price in B2 from 40 to 50. C2 recalculates to 45, and D2 updates to 48.6 because D2 references the range that C2 fills. Change B2 back to 40 before continuing.
To test the calculations from the Apps Script editor, select testPriceFunctions, click Run, and open Execution log. Do not run DISCOUNTED_PRICE from the toolbar. The editor passes undefined for both parameters, and the rate check throws.
Accept one cell or a whole range
A custom function receives cell values, not a Range object (arguments guide). Calling getValue() or getValues() on a passed argument throws TypeError: X is not a function:
A single-cell reference such as
=DISCOUNTED_PRICE(B2, 10%)passes that cell's value.A range reference such as
=DISCOUNTED_PRICE(B2:B5, 10%)passes a two-dimensional array of values (one inner array per row).
mapValueOrRange_ uses Array.isArray() to check whether the first parameter is a single value or a 2D array.
For a single value, it computes one result.
For a range, it calls
map()on the outer array of rows and on each inner row array. The result is a 2D array with the same dimensions.
The rate parameter accepts only a single number. requireRate_ rejects arrays, so =DISCOUNTED_PRICE(B2:B5, E1:E2) throws an error because the function cannot tell which rate applies to each row.
Return a two-dimensional array to fill cells
Google's return values section sets three rules for what a custom function returns:
A single value appears in the formula's cell.
A two-dimensional array overflows into adjacent cells if those cells are empty, or fails with a spill error if it would overwrite existing cell contents.
A custom function cannot modify any cells outside the formula cell and its overflow range.
The overflow needs empty target cells, so typing any value into C4 makes C2 show #REF! with the hover message Array result was not expanded because it would overwrite data in C4. Clearing C4 restores the four-row output. An array with one row ([[36, 38.88]]) spills to the right, and an array with one column ([[36], [11.25]]) spills downward.
Know what arrives in each argument
Sheets converts cell contents to JavaScript values before calling your function (data types guide):
Percentages arrive as decimal numbers (
10%becomes0.1). For that reason,requireRate_accepts numbers from0to1and rejects10.Dates, times, and durations arrive as JavaScript
Dateobjects.If the spreadsheet and script use different time zones, date calculations must account for the offset.
calculateCell_ accepts only finite numbers. Text, booleans, or dates in the price column throw an error naming the argument and its row and column within the passed range. For blank cells ('' or null), calculateCell_ returns '', so the gift card row stays blank instead of showing 0.
Add autocomplete with JSDoc
Each public function starts with a JSDoc comment ending in @customfunction, which registers the function in Sheets formula autocomplete (autocomplete guide).
Sheets shows the first line as the summary, each @param tag as an argument description, and @return as the return description. The formula helper understands less JSDoc than the editor, so keep the descriptions plain. The private helpers omit @customfunction and end their names with _, so they do not appear in autocomplete.
Name functions so Sheets can find them
Google's naming rules set five conditions for custom function declarations:
The name must differ from built-in spreadsheet functions such as
SUM().The name cannot match a cell coordinate in A1 or R1C1 notation, such as
A1orR1C1.The name cannot end with
_, which marks a function as private to the script.Declare the function at the top level with
function NAME(...), notnew Function(), aconstarrow function, or a function expression.Names are case-insensitive in formulas (
=discounted_price(B2, 10%)callsDISCOUNTED_PRICE), although uppercase names match the style of built-in functions.
End every internal helper name with _ (such as requireRate_, calculateCell_, and roundPrice_) so cells cannot call the helper directly.
Run without authorization
Custom functions "never ask users to authorize access to personal data" and can call only services that do not access personal data (using Apps Script services). They run in ScriptApp.AuthMode.CUSTOM_FUNCTION (AuthMode reference):
| Service | Use in a custom function |
|---|---|
| Cache, Lock | Available. CacheService.getScriptCache() can memoize public HTTP responses. |
| HTML | Can generate HTML but cannot display it. |
| JDBC, Language, Utilities, XML | Available. |
| Maps | Can calculate directions but cannot display maps. |
| Properties | Read-only. getScriptProperties() and getDocumentProperties() read shared properties, and getUserProperties() reads only the spreadsheet owner's properties. All set*() and delete*() calls fail. |
| Spreadsheet | Read-only on the current spreadsheet. Most get*() methods work, while set*() methods, openById(), and openByUrl() do not. |
| URL Fetch | Can fetch public web resources without user OAuth credentials. |
Calling any other service, such as Gmail, Drive, Calendar, or a Range.set*() method, fails with You do not have permission to call X service. Move work that needs authorization into a custom menu or trigger function.
Google's authorization guide says a custom function runs as an anonymous user, while its quota use counts against the user at the keyboard.
Custom functions have two strict isolation rules (using Apps Script services):
Spill-only output: A custom function writes cells solely by returning a value or 2D array. If any cell in the overflow range already contains a value or formula, Sheets leaves existing data untouched and displays
#REF!(Array result was not expanded because it would overwrite data in ...).AuthMode.CUSTOM_FUNCTIONsandbox: Because formulas evaluate automatically when anyone opens or edits the sheet without an OAuth prompt, Apps Script blocks personal-data and mutating APIs includingGmailApp,DriveApp,PropertiesServicewrites,Range.setValues(), andSpreadsheetApp.openById().
Stay inside the 30-second limit
A custom function call must return within 30 seconds (return values guide). Past that limit the cell shows #ERROR! with the note Exceeded maximum execution time (line 0). The quotas page lists 30 seconds per custom-function execution, against 6 minutes for an ordinary script execution, and notes that quotas can change without notice.
If a task calls a slow external service or processes more data than fits in 30 seconds, run it from a menu or trigger and write the output with setValues().
Understand when Sheets recalculates
Sheets recalculates a custom function only when its formula or one of its passed argument cells changes (custom function guidelines):
Pass every cell or range the function depends on as a formula argument. Reading cells inside the function with
SpreadsheetAppandgetValue()does not register a dependency, so edits to those cells do not cause a recalculation.Do not pass volatile built-in functions such as
NOW()orRAND()as arguments.Saving a code change in
Code.gsdoes not recalculate cells that already show a result. Retype the formula, or edit one of the cells it references, to run the new code.
Custom functions must behave deterministically for a given set of inputs (custom function guidelines):
Sheets caches a custom function's return value keyed by its exact argument values. Identical calls share the cached result, and Sheets recalculates only when referenced cells change.
Passing volatile built-in functions such as
NOW(),TODAY(),RAND(), orRANDBETWEEN()breaks deterministic caching and causes a permanentLoading...state or aYou are not allowed to call ...error.
Read errors from a custom function
When a custom function throws an error, the cell displays #ERROR! and shows the thrown message on hover:
price must be a number (range row 2, column 1). Received: n/awhen a price cell contains non-numeric text (row 2 ofB2:B5is spreadsheet row 3).discountRate must be one number from 0 to 1, such as 10% or 0.1.when the rate is missing, outside0..1, or a range.
A throw ends the whole call. C2 shows #ERROR! and nothing fills C3:C5. A formula that uses this output, such as PRICE_WITH_TAX in D2, also shows an error. To debug a failure with full logs, call the function with the same inputs from testPriceFunctions in the editor.
Prefer one call per range
Each formula cell makes a separate server call. Google's optimization guide recommends one formula that takes a range and returns a two-dimensional array instead of one formula per row:
Copying
=DISCOUNTED_PRICE(B2, 10%)acrossC2:C501makes 500 Apps Script calls.Entering
=DISCOUNTED_PRICE(B2:B501, 10%)inC2makes one call that fillsC2:C501.
The quotas page notes that Script invoked too many times per second for this Google user account. and There are too many scripts running simultaneously for this Google user account. most often occur when a sheet calls a custom function hundreds of times in separate cells. An open-ended range such as B2:B covers rows added later, and the function returns '' for the blank ones.
Before testing a custom function across a large sheet, call it from a parameterless helper such as testPriceFunctions in the editor with both a scalar (40) and a 2D array ([[40], [12.5], ['']]) so syntax and type errors appear immediately in Execution log. If a custom function fetches public data with UrlFetchApp.fetch(), accept a whole range in one call and cache responses in CacheService.getScriptCache() so sheet recalculations stay within the 30-second timeout and daily UrlFetchApp call quotas (quotas guide).
Decide between a custom function and a menu or trigger
Use a custom function when the calculation depends only on its formula arguments, finishes within 30 seconds, and writes only to the formula's output range.
Use a custom menu or trigger function when the script:
Calls a service that requires OAuth authorization, such as Gmail, Drive, or Calendar.
Writes to existing data columns or other tabs with
setValues().Sends, creates, or changes something outside the sheet, so it must run once when asked and not on every recalculation.
Can run for longer than 30 seconds.
Reads another spreadsheet.
Common failures
#NAME?in the cell. Check that the script is bound to this spreadsheet,Code.gsis saved, and the function name does not end with_.price must be a number (range row N, column M). Received: ...RowN, columnMof the passed price range contains text, a boolean, or a date.discountRate must be one number from 0 to 1, such as 10% or 0.1.Enter a single decimal (0.1) or percentage (10%).10and a cell range are rejected.#REF!withArray result was not expanded because it would overwrite data in ...Clear any non-empty cells inC3:C5orD3:D5so the returned array can spill.Cell still shows an old result after editing
Code.gs. Edit the formula or one of its input cells to force recalculation.You do not have permission to call X service.Move the authorized service call into a menu or trigger function.#ERROR!withExceeded maximum execution time (line 0).Reduce the work per call or move the calculation to a menu or trigger script.Loading...never finishes. Remove volatile arguments such asNOW()orRAND().
Exercise: multiply prices by quantities
Add a Quantity header in E1 and the quantities 2, 5, 1 in E2:E4, leaving E5 empty. Then write a third custom function, LINE_TOTAL(price, quantity), that returns price * quantity for each row:
Add a JSDoc comment with
@paramlines for both arguments and the@customfunctiontag.Accept either two single values or two ranges. When both are arrays, throw an error unless they have the same number of rows and the same number of columns in every row.
Reuse
calculateCell_for each price, or write a similar private helper for pairs of values. Round each result withroundPrice_and keep blank rows blank.Enter
=LINE_TOTAL(D2:D5, E2:E5)inF2.
With the sample data, F2:F5 shows 77.76, 60.75, 62.21, and a blank cell. Type two in E3 and confirm that F2 shows an error message naming the quantity argument and its position, then restore E3 to 5.
Clean up and continue
Delete the disposable spreadsheet when you no longer need it. Deleting the spreadsheet also removes its bound script. Next, read Validate, format, and track Sheets edits.
Project files
Complete source files for this chapter's examples.
Discount and tax price formulas
Code.gs
Download fileconst PRICE_DECIMALS = 2; // Number of decimal places kept in each returned price.
/**
* Returns a price after a discount. Accepts one price or a range of prices.
*
* @param {number|Array<Array<*>>} price A price, or a range of prices such as B2:B5.
* @param {number} discountRate The discount as a rate from 0 to 1, such as 10% or 0.1.
* @return The discounted price, or one discounted price per cell of the range.
* @customfunction
*/
function DISCOUNTED_PRICE(price, discountRate) {
const rate = requireRate_(discountRate, 'discountRate');
return mapValueOrRange_(price, 'price', function (amount) {
return roundPrice_(amount * (1 - rate));
});
}
/**
* Returns a price with sales tax added. Accepts one price or a range of prices.
*
* @param {number|Array<Array<*>>} price A price, or a range of prices such as B2:B5.
* @param {number} taxRate The tax as a rate from 0 to 1, such as 8% or 0.08.
* @return The price including tax, or one taxed price per cell of the range.
* @customfunction
*/
function PRICE_WITH_TAX(price, taxRate) {
const rate = requireRate_(taxRate, 'taxRate');
return mapValueOrRange_(price, 'price', function (amount) {
return roundPrice_(amount * (1 + rate));
});
}
/**
* Logs sample results so you can check the calculations from the editor.
*
* @returns {void}
*/
function testPriceFunctions() {
Logger.log(DISCOUNTED_PRICE(40, 0.1));
Logger.log(JSON.stringify(DISCOUNTED_PRICE([[40], [12.5], ['']], 0.1)));
Logger.log(JSON.stringify(PRICE_WITH_TAX([[36], [11.25]], 0.08)));
}
/**
* Applies a calculation to one value, or to every cell of a two-dimensional range.
* Blank cells stay blank so the result has the same shape as the input.
*
* @param {*} input One cell value, or a two-dimensional array of cell values.
* @param {string} argumentName The argument name used in error messages.
* @param {function(number): number} calculate The calculation for one number.
* @returns {*} One result, or a two-dimensional array of results.
*/
function mapValueOrRange_(input, argumentName, calculate) {
if (!Array.isArray(input)) {
return calculateCell_(input, argumentName, calculate, '');
}
const rows = /** @type {Array<Array<*>>} */ (input);
return rows.map(function (row, rowIndex) {
return row.map(function (cell, columnIndex) {
const position = ' (range row ' + (rowIndex + 1) + ', column ' + (columnIndex + 1) + ')';
return calculateCell_(cell, argumentName, calculate, position);
});
});
}
/**
* Calculates one cell, keeping a blank cell blank and rejecting text or dates.
*
* @param {*} value The cell value.
* @param {string} argumentName The argument name used in error messages.
* @param {function(number): number} calculate The calculation for one number.
* @param {string} position Where the value sits in a range, or an empty string.
* @returns {number|string} The result, or an empty string for a blank cell.
*/
function calculateCell_(value, argumentName, calculate, position) {
if (value === '' || value === null) {
return '';
}
if (typeof value !== 'number' || !isFinite(value)) {
throw new Error(argumentName + ' must be a number' + position + '. Received: ' + String(value));
}
return calculate(value);
}
/**
* Checks that a rate is a single number from 0 to 1.
*
* @param {*} rate The rate argument from the formula.
* @param {string} argumentName The argument name used in error messages.
* @returns {number} The checked rate.
*/
function requireRate_(rate, argumentName) {
if (typeof rate !== 'number' || !isFinite(rate) || rate < 0 || rate > 1) {
throw new Error(argumentName + ' must be one number from 0 to 1, such as 10% or 0.1.');
}
return rate;
}
/**
* Rounds a calculated price to PRICE_DECIMALS decimal places.
*
* @param {number} amount The unrounded price.
* @returns {number} The rounded price.
*/
function roundPrice_(amount) {
const factor = Math.pow(10, PRICE_DECIMALS);
return Math.round(amount * factor) / factor;
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
sample.csv
Download fileItem,Price,Sale price,With tax
Notebook,40,,
Pen set,12.5,,
Desk lamp,64,,
Gift card,,,