Write custom functions for Sheets formulas

A custom function is an Apps Script function that you call from a spreadsheet cell, the same way you call SUM or VLOOKUP. In this chapter you add two price formulas to a disposable spreadsheet: DISCOUNTED_PRICE takes a price and a discount rate, and PRICE_WITH_TAX takes a price and a tax rate. Both accept one cell or a whole column of prices. You will also see what a custom function is not allowed to do, how Sheets reports its errors, and when a menu or trigger script is the better tool.

The earlier chapters ran functions from the editor and wrote to ranges with setValues(). A custom function works the other way around: Sheets calls your code, passes it the values from the formula's arguments, and puts the returned value in the cell. The function itself never writes to the sheet.

Write a function a cell can call

Any top-level function declaration in a spreadsheet's bound script can be called from a cell of that spreadsheet when its name follows the naming rules later in this chapter. The smallest useful custom function has one parameter and one return statement:

function HALF(value) {
  return value / 2;
}

After you save that function in the spreadsheet's script project, typing =HALF(10) in a cell shows 5. Typing =HALF(A1) shows half of the value in A1. Google's custom functions guide describes the same sequence: type an equals sign, the function name and the inputs, then press Enter. The cell displays Loading... briefly, then the result.

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. See 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 the included sample.csv into cell A1. Select column A, choose Data > Split text to columns, set the separator to Comma, and verify that Prices!A1:D5 has the headers Item, Price, Sale price, and With tax. Cells C2:D5 must be empty, and B5 (the gift card price) is also empty.

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

5. In Project Settings, select Show "appsscript.json" manifest file in editor, then replace its contents with the included appsscript.json file. 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;
}

The manifest declares no OAuth scopes, and the code calls no Google service. The only service call in the project is Logger.log() in the optional test function.

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:

Only C2 and D2 contain formulas. The values in C3:C5 and D3:D5 come from the arrays those two calls returned.

Change the notebook price in B2 from 40 to 50. C2 recalculates to 45, and D2 follows to 48.6, because D2 reads the range that C2 fills. Change B2 back to 40 before you continue.

You can also check the calculations from the editor: select testPriceFunctions, click Run, and open Execution log. The function calls the two custom functions with literal values and logs the results. Running DISCOUNTED_PRICE itself from the editor is less useful, because the Run button passes no arguments and the rate check stops with an error.

Accept one cell or a whole range

A custom function receives values, never a Range object. Google's guide defines two shapes in its arguments section:

• A reference to a single cell, such as =DISCOUNTED_PRICE(B2, 10%), passes the value of that cell.

• A reference to a range, such as =DISCOUNTED_PRICE(B2:B5, 10%), passes a two-dimensional array of the cells' values. The guide's example reads =DOUBLE(A1:B2) as double([[1,3],[2,4]]): one inner array per row.

So the same parameter can hold a number in one call and an array of rows in the next. mapValueOrRange_ handles both. Array.isArray() tells the two shapes apart. For a single value, the helper calculates one result. For a range, it uses map() twice: the outer call visits each row, and the inner call visits each cell in that row. Each map() returns a new array of the same length, so the result has the same number of rows and columns as the input range.

This is the same rectangle rule that setValues() enforced in the batching chapter. The difference is who performs the write. Here Sheets places the returned array starting at the formula's cell.

The rate parameters accept only a single number. requireRate_ rejects an array, so =DISCOUNTED_PRICE(B2:B5, E1:E2) stops with an error instead of guessing which rate applies to which row. If you need a different rate per row, write a function that takes two ranges and checks that their shapes match. The exercise at the end of this chapter does that.

Return a two-dimensional array to fill cells

What you return decides what the cells show. Google's return values section sets three rules:

• A single returned value appears in the cell that holds the formula.

• A returned two-dimensional array overflows into adjacent cells, as long as those cells are empty. If the array would overwrite existing cell contents, the custom function throws an error instead.

• A custom function cannot affect any other cells. It can change only the cell it was called from and the adjacent cells its returned array fills.

The overflow is why the setup step requires C3:D5 to be empty. Type any value in C4 and C2 shows an error, because its four-row result no longer fits. Delete the value in C4 and the column fills again. Sheets checks only the cells the result needs, so C6 and below do not matter for this four-row input.

An array with one row, such as [[36, 38.88]], fills cells to the right. An array with one column, such as [[36], [11.25]], fills cells downward. The example functions keep the input's shape, so a column of prices returns a column of results.

Know what arrives in each argument

Sheets converts cell contents to JavaScript values before your function runs. Google's data types section names the conversions that most often cause confusion:

• A percentage becomes a decimal number. The 10% in the formula arrives as 0.1, which is why requireRate_ accepts rates from 0 to 1. A rate typed as 10 stops with the rate error instead of producing a negative price.

• Dates and times become JavaScript Date objects. Durations also become Date objects, which makes arithmetic on them awkward.

• If the spreadsheet and the script use different time zones, a function that works with dates has to compensate.

calculateCell_ accepts only finite numbers. Text, TRUE, and a date in the price column stop the calculation with a message that names the argument and the position in the range. The function treats an empty cell as blank and returns an empty string for it, so the gift card row stays blank instead of showing 0. The check accepts both '' and null for that empty cell.

Add autocomplete with JSDoc

Each public function in the example starts with a JSDoc comment that ends with @customfunction. That tag is optional. Without it, =DISCOUNTED_PRICE(...) still calculates. With it, the function appears in the list that Sheets shows while you type a formula, alongside the built-in functions. See autocomplete.

Sheets uses the rest of the comment for the help it shows during typing: the first line describes the function, @param lines describe the arguments, and @return describes the result. Google notes that the Sheets formula helper supports a narrower set of JSDoc tags and syntax than the editor does, so keep the description and parameter text plain. The private helpers have JSDoc for the editor but no @customfunction tag, and their names keep them out of the spreadsheet entirely.

Name functions so Sheets can find them

Google's naming rules add four conditions to ordinary JavaScript naming:

• The name must differ from built-in spreadsheet functions such as SUM().

• The name cannot end with an underscore (_). Apps Script treats a trailing underscore as a private function, so =mapValueOrRange_(B2) does not call the helper.

• The function must be declared with function NAME(...). A function created another way, such as with new Function(), is not available to a cell.

• Capitalization does not matter. =discounted_price(B2, 10%) calls DISCOUNTED_PRICE. Uppercase names follow the convention of built-in spreadsheet functions.

The trailing underscore is useful in the opposite direction too. Name every helper that should not be called from a cell with _ at the end, as requireRate_, calculateCell_, and roundPrice_ are here.

Run without authorization

Most scripts show an authorization dialog the first time they need access to a user's data. A custom function never does. Google's services section states that custom functions "never ask users to authorize access to personal data," and so they can call only services that do not have access to personal data. The AuthMode reference lists CUSTOM_FUNCTION as its own authorization mode with access to "a limited subset of services."

The guide lists the services a custom function can use:

Anything else is unavailable. A custom function cannot send email through Gmail, create a Drive file, add a calendar event, or write to another cell. If the code calls a service that needs authorization, the call fails with the message You do not have permission to call X service., where X names the service. The guide's advice for that case is to move the work into a function run from a custom menu, which asks for authorization when it needs it.

The execution identity is also different. Google's authorization guide lists a custom function in a spreadsheet as running as an anonymous user, while quota limits count against the user at the keyboard. Do not write a custom function that depends on who is looking at the spreadsheet.

The example functions call no service at all. They calculate from their arguments and return the result, which keeps them inside every rule above.

Stay inside the 30-second limit

"A custom function call must return within 30 seconds," according to the return values section. If a call takes longer, the cell shows #ERROR! and its note reads Exceeded maximum execution time (line 0). The quotas page lists a custom function runtime of 30 seconds per execution, beside a script runtime of 6 minutes per execution, for both consumer and Google Workspace accounts. Google states that these limits can change without notice.

The arithmetic in this example is small. The limit matters when a custom function calls UrlFetchApp, reads a large sheet, or loops over many rows with slow work in each one. If a calculation cannot reliably finish in 30 seconds, run it from a menu or trigger instead and write the result with setValues().

Understand when Sheets recalculates

Sheets calls the function again when a value the formula passes as an argument changes. Google's arguments section states the rule from the other side: to trigger recalculation, pass the referenced cell or range directly as an argument. Otherwise, the function does not recalculate until you edit the formula or change the value of a referenced cell.

Three consequences follow:

• Pass the data the function uses as arguments. A custom function that reads cells itself with SpreadsheetApp and getValue() does not recalculate when those cells change, because Sheets does not know the function depends on them.

• Do not pass volatile functions. Arguments must be deterministic, so built-in functions that return a different result each time, such as NOW() or RAND(), are not allowed as arguments. A custom function that tries to return a value based on one shows Loading... indefinitely.

• After you change the script, cells that already show a result can keep it. Edit the formula or one of its input cells to make Sheets call the new code.

The third point is the one that most often confuses a first test. If you fix a bug in Code.gs, save, and the cell still shows the old result, retype the formula in that cell.

Read errors from a custom function

When a custom function throws an error, the formula cell shows #ERROR!. Hold the pointer over the cell, or select it, to see the error message. This is the only place the message appears to the person using the spreadsheet, so write it for that person.

The example throws two kinds of error:

• price must be a number (range row 2, column 1). Received: n/a when a price cell contains text. The position is counted inside the range argument, so 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, out of range, or a range of cells.

Throwing stops the whole call, so one bad cell replaces the entire returned column with a single error in C2. An alternative is to return an error text such as 'Invalid price' in that one position and keep the other results. That keeps the column visible, but the text then flows into any formula that reads the column. In this example a single clear error is the better choice, because PRICE_WITH_TAX would otherwise receive a text value as a price.

The person using the spreadsheet sees only that message. To debug the calculation, call the function from a test function such as testPriceFunctions with the same values and read the execution log.

Prefer one call per range

Each cell that calls a custom function makes a separate call to the Apps Script server. Google's optimization section warns that dozens, hundreds, or thousands of calls can be slow, and recommends accepting a range as a two-dimensional array and returning a two-dimensional array instead.

Compare two ways to fill column C for 500 rows:

• =DISCOUNTED_PRICE(B2, 10%) in C2, filled down to C501, makes 500 calls.

• =DISCOUNTED_PRICE(B2:B501, 10%) in C2 makes one call, and the result fills C2:C501.

With the second form, changing the rate means editing one formula instead of 500. The quotas page lists two errors, 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., and says both most commonly occur with custom functions called repeatedly in a single spreadsheet. Its recommended fix is the same: code custom functions so they need only one call per range of data.

One call per range has a cost. Inserting a row inside B2:B501 is handled by the range reference, but adding a price below row 501 needs a longer range in the formula. A reference such as B2:B covers the whole column; it also returns blank rows, which the example turns into blank results.

Choose a custom function or a menu or trigger script

A custom function fits a calculation that depends only on its inputs and returns values to the cells next to the formula. Prices, unit conversions, text cleanup, and scores computed from a row are good candidates.

Use a function run from a custom menu, or a trigger from a later chapter, when the work:

• needs a service that asks for authorization, such as Gmail, Drive, or Calendar;

• writes to cells other than the formula's own result, such as a status column or another tab;

• sends, creates, or deletes something, which should happen once when someone chooses to run it, and not each time Sheets recalculates;

• can take longer than 30 seconds;

• must read data the formula cannot pass as an argument, such as another spreadsheet.

The batching chapter's status update belongs in that second group. It writes a status column that other people edit, and a formula cannot do that.

Common failures

• #NAME? in the cell. Sheets does not know the function. Check that the script is bound to this spreadsheet, the file is saved, and the name in the formula matches the function name without a trailing underscore.

• price must be a number (range row N, column M). Received: ... A cell in the price range contains text, a date, or TRUE. Find row N of the range argument and correct the value.

• discountRate must be one number from 0 to 1, such as 10% or 0.1. The rate is missing, is a whole number such as 10, or is a range. Enter 10% or 0.1.

• An error about overwriting data, with the column empty. A cell in the area the result needs is not empty. Clear C3:C5 or D3:D5.

• The cell still shows the old result after you changed the script. Retype the formula, or change one of its input cells, so Sheets calls the new code.

• You do not have permission to call X service. The custom function calls a service that needs authorization. Move that work into a menu or trigger function.

• #ERROR! with Exceeded maximum execution time (line 0). The call took more than 30 seconds. Reduce the work in the function or move it to a menu or trigger script.

• Loading... never finishes. An argument uses a volatile function such as NOW() or RAND(). Pass a fixed value or a cell that holds one.

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. Then type two in E3 and confirm that F2 shows an error message naming the quantity argument and its position. Change E3 back to 5.

The exercise reads only the values in the formula's arguments and writes only to F2:F5.

Clean up and continue

No script-created resource needs cleanup. Delete the disposable spreadsheet when you no longer need it, which also removes its bound script. Next, validate, highlight, and react to Sheets edits with a script that changes a sheet when someone edits it.