Skip to content
Skip to chapter

Chapter 10

Reliable automations

11 min read

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.

By the end

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.

Chapter navigation

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:

Source
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

  1. Create a new blank Google Sheet for this exercise. Do not use a live pricing or finance spreadsheet.

  2. Rename its first tab to Prices.

  3. Copy the comma-separated text from sample.csv, shown below, into cell A1, or import the file with File > Import.

sample.csv
Item,Price,Sale price,With tax
Notebook,40,,
Pen set,12.5,,
Desk lamp,64,,
Gift card,,,

Download sample.csv
  1. If you pasted the text, select column A, choose Data > Split text to columns, and set the separator to Comma.

  2. Check that Prices!A1:D5 has the headers Item, Price, Sale price, and With tax, that C2:D5 is empty, and that B5 (the gift card price) is empty.

  3. Choose Extensions > Apps Script. Replace the default code in Code.gs with the complete Code.gs example file.

  4. In Project Settings, select Show "appsscript.json" manifest file in editor, then replace its contents with the included appsscript.json file. Save both files.

Code.gs
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;
}

Download Code.gs
appsscript.json
{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

Download appsscript.json

The manifest declares no OAuth scopes. The custom functions use only JavaScript, and testPriceFunctions calls only Logger.log().

Use the formulas

  1. Return to the spreadsheet. In cell C2, enter =DISCOUNTED_PRICE(B2:B5, 10%).

  2. In cell D2, enter =PRICE_WITH_TAX(C2:C5, 8%).

No authorization dialog appears. After Loading..., the two columns fill with the calculated values:

ItemPriceSale priceWith tax
Notebook403638.88
Pen set12.511.2512.15
Desk lamp6457.662.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% becomes 0.1). For that reason, requireRate_ accepts numbers from 0 to 1 and rejects 10.

  • Dates, times, and durations arrive as JavaScript Date objects.

  • 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 A1 or R1C1.

  • The name cannot end with _, which marks a function as private to the script.

  • Declare the function at the top level with function NAME(...), not new Function(), a const arrow function, or a function expression.

  • Names are case-insensitive in formulas (=discounted_price(B2, 10%) calls DISCOUNTED_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):

ServiceUse in a custom function
Cache, LockAvailable. CacheService.getScriptCache() can memoize public HTTP responses.
HTMLCan generate HTML but cannot display it.
JDBC, Language, Utilities, XMLAvailable.
MapsCan calculate directions but cannot display maps.
PropertiesRead-only. getScriptProperties() and getDocumentProperties() read shared properties, and getUserProperties() reads only the spreadsheet owner's properties. All set*() and delete*() calls fail.
SpreadsheetRead-only on the current spreadsheet. Most get*() methods work, while set*() methods, openById(), and openByUrl() do not.
URL FetchCan 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.

note

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_FUNCTION sandbox: Because formulas evaluate automatically when anyone opens or edits the sheet without an OAuth prompt, Apps Script blocks personal-data and mutating APIs including GmailApp, DriveApp, PropertiesService writes, Range.setValues(), and SpreadsheetApp.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 SpreadsheetApp and getValue() 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() or RAND() as arguments.

  • Saving a code change in Code.gs does not recalculate cells that already show a result. Retype the formula, or edit one of the cells it references, to run the new code.

warning

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(), or RANDBETWEEN() breaks deterministic caching and causes a permanent Loading... state or a You 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/a when a price cell contains non-numeric text (row 2 of B2:B5 is spreadsheet row 3).

  • discountRate must be one number from 0 to 1, such as 10% or 0.1. when the rate is missing, outside 0..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%) across C2:C501 makes 500 Apps Script calls.

  • Entering =DISCOUNTED_PRICE(B2:B501, 10%) in C2 makes one call that fills C2: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.

tip

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.gs is saved, and the function name does not end with _.

  • price must be a number (range row N, column M). Received: ... Row N, column M of 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%). 10 and a cell range are rejected.

  • #REF! with Array result was not expanded because it would overwrite data in ... Clear any non-empty cells in C3:C5 or D3:D5 so 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! with Exceeded 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 as NOW() or RAND().

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:

  1. Add a JSDoc comment with @param lines for both arguments and the @customfunction tag.

  2. 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.

  3. Reuse calculateCell_ for each price, or write a similar private helper for pairs of values. Round each result with roundPrice_ and keep blank rows blank.

  4. Enter =LINE_TOTAL(D2:D5, E2:E5) in F2.

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
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;
}

appsscript.json

Download file
appsscript.json
{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

sample.csv

Download file
sample.csv
Item,Price,Sale price,With tax
Notebook,40,,
Pen set,12.5,,
Desk lamp,64,,
Gift card,,,