# Using ARRAYFORMULA with Custom Functions

In this tutorial, I will show you how to combine `ARRAYFORMULA`

with custom functions in Google Sheets to process entire columns efficiently. This technique is particularly useful for applying calculations to large datasets.

By using `ARRAYFORMULA`

and custom functions, you can significantly improve your spreadsheet's performance and scalability. This approach reduces the number of individual function calls and speeds up recalculations, making it ideal for spreadsheets with lots of data.

We will walk through creating a custom function that applies a discount to prices, then use `ARRAYFORMULA`

to apply this function to an entire column simultaneously. You'll also learn how to handle both single-cell and array inputs in your custom functions.

## Prerequisites

This tutorial assumes the following prerequisites:

Familiarity with Google Sheets and Apps Script

Understanding of custom functions in Google Sheets

Familiarity with Array methods, especially the map() method

Knowledge of ARRAYFORMULA in Google Sheets

## 5 steps to supercharge your spreadsheets with ARRAYFORMULA and custom functions

## Step 1 â€” Create a sample dataset

Let's start by creating a sample dataset that we'll use throughout this tutorial. We'll create a simple spreadsheet with a list of products and their prices.

Open a new Google Sheets spreadsheet.

In cell

`A1`

, type "Product".In cell

`B1`

, type "Price (USD)".Fill in some sample data in columns

`A`

and`B`

, starting from row 2.In cell

`F2`

, type "Discount".In cell

`G2`

, enter the discount percentage. For this example, let's use 20%, so enter 20%.

## Step 2 â€” Write a custom function

Now, let's create a custom function that will apply a dynamic discount to our product prices. We'll write a function that takes a price and a discount percentage as input and returns the discounted price.

Click on "Extensions" in the top menu, then select "Apps Script".

In the Apps Script editor, replace the default code with the following:

```
/**
* Applies a discount to the given price.
* @customfunction
*/
function APPLY_DISCOUNT(price, discount) {
if (Array.isArray(price)) {
return price.map(function(row) {
return row.map(function(price) {
return typeof price === "number" ? price * (1 - discount) : null;
});
});
} else {
return typeof price === "number" ? price * (1 - discount) : null;
}
}
```

Click on "Save" and give your project a name, such as "Dynamic Discount Calculator".

This custom function, `APPLY_DISCOUNT`

, is designed to handle both single values and arrays of prices. Let's break down how it works (I cover this in detail later on in this post):

The function takes two parameters:

`price`

(which can be a single number or an array of numbers) and`discount`

(a number representing the discount).If

`price`

is an array (which happens when used with`ARRAYFORMULA`

), it uses nested`map`

functions to process each element.The discount is applied by multiplying the price by

`(1 - discount)`

.If

`price`

is a single value, it simply applies the discount if it's a number, or returns`null`

if it's not.

## Step 3 â€” Apply the custom function using ARRAYFORMULA

Now let's use `ARRAYFORMULA`

to apply our custom function to the entire "Price" column at once, using the dynamic discount percentage.

Go back to your Google Sheets spreadsheet.

In cell

`C1`

, type "Discounted Price (USD)".In cell

`C2`

, enter the following formula:

`=ARRAYFORMULA(APPLY_DISCOUNT(B2:B,$G$2))`

This formula does the following:

`ARRAYFORMULA()`

applies the formula to the entire range at once.`APPLY_DISCOUNT(B2:B,$G$2)`

calls our custom function, passing the entire`B2:B`

range as the price input and the discount percentage from cell`G2`

.

## Step 4 â€” Test and verify the results

After entering the formula, you should see the discounted prices appear instantly in column `C`

for all your products.

## Step 5 â€” Compare performance

To illustrate the performance benefits of using `ARRAYFORMULA`

, let's compare two approaches:

Using

`ARRAYFORMULA`

Using regular formula without

`ARRAYFORMULA`

Create two sheets in your spreadsheet, each containing 500 rows of product data. In the first sheet, use the `ARRAYFORMULA`

approach we just implemented. In the second, apply the custom function to each cell individually without `ARRAYFORMULA`

.

You'll notice that using `ARRAYFORMULA`

makes the calculations much faster.

To see the difference in action, change the discount percentage in cell G2. Observe that the sheet without `ARRAYFORMULA`

takes noticeably longer to update all 500 cells. Notice how the first sheet with `ARRAYFORMULA`

updates almost instantly. This performance difference becomes even more pronounced with larger datasets or more complex calculations.

Here is a screencast of the sheet without `ARRAYFORMULA`

. Observe that several cells are still "Loading…" several seconds after I update the discount percentage.

Now, when you use `ARRAYFORMULA`

, the calculations are much faster and the entire column is updated in a single operation almost immediately.

### Why ARRAYFORMULA is Much Faster

**Batched Computations:**`ARRAYFORMULA`

allows Google Sheets to process the entire range of data in one batch. This means our custom function is called only once with a large array of inputs, rather than being called 500 times individually.**Efficient Sheet Updates:**When using`ARRAYFORMULA`

, Google Sheets can update the entire column of results in one operation. Without`ARRAYFORMULA`

, the sheet must update 500 individual cells, each triggering its own update event.**Reduced Recalculation Overhead:**When a change occurs (e.g., updating the discount percentage),`ARRAYFORMULA`

requires only one recalculation for the entire column. The regular formula approach would trigger 500 separate recalculations.

## How this code works

The `APPLY_DISCOUNT`

function is designed to handle both single values and arrays of prices, making it versatile for different use cases in Google Sheets. Let's break down the function and explore its functionality:

```
/**
* Applies a discount to the given price.
* @customfunction
*/
function APPLY_DISCOUNT(price, discount) {
if (Array.isArray(price)) {
return price.map(function(row) {
return row.map(function(price) {
return typeof price === "number" ? price * (1 - discount) : null;
});
});
} else {
return typeof price === "number" ? price * (1 - discount) : null;
}
}
```

### Input Types and Function Behavior

#### Function Declaration

`function APPLY_DISCOUNT(price, discount) {`

The function takes two parameters: `price`

(which can be a single number or an array of numbers) and `discount`

(a decimal representing the discount percentage).

#### Array Check

`if (Array.isArray(price)) {`

This condition checks if the `price`

parameter is an array. This will be true when the function is called via `ARRAYFORMULA`

.

#### Handling Array Input

```
return price.map(function(row) {
return row.map(function(price) {
return typeof price === "number" ? price * (1 - discount) : null;
});
});
```

If `price`

is an array (`ARRAYFORMULA`

case):

The outer

`map`

function iterates over each row of the 2D array.The inner

`map`

function processes each price in that row.For each price, it checks if it's a number using

`typeof price === "number"`

.If it's a number, it applies the discount:

`price * (1 - discount)`

.If it's not a number, it returns

`null`

to handle empty or non-numeric cells.

#### Handling Single Value Input

```
} else {
return typeof price === "number" ? price * (1 - discount) : null;
}
```

If `price`

is not an array (regular cell formula case):

It checks if the price is a number.

If it is, it applies the discount and returns the result.

If it's not a number, it returns

`null`

.

#### @customfunction Annotation

`* @customfunction`

This JSDoc annotation tells Google Sheets that this function can be used as a custom function in spreadsheet formulas. If this annotation is not specified, the function cannot be used in spreadsheet formulas.

### Handling Empty and Invalid Inputs

A key aspect of this function is how it handles empty or invalid inputs by returning `null`

. This behavior is important for maintaining clean and meaningful outputs in your spreadsheet, especially when working with `ARRAYFORMULA`

.

Why return null?

**Empty Cell Handling:**When the function encounters an empty cell in the input range,`typeof price`

will not be "number". Returning`null`

ensures that the corresponding output cell remains empty, rather than displaying an error or unwanted value.**Preserving Spreadsheet Layout:**This approach helps maintain the visual structure of your spreadsheet by not filling unused cells with zeros or error messages.**Error Prevention:**For non-numeric inputs (like text in a price column), returning`null`

prevents calculation errors from propagating through your spreadsheet.

**Note:** In some situations you might want the function to return an error when it encounters invalid input. Therefore, this behavior is use case-specific.

#### Use Case Example

Consider a scenario where column `A`

has data from rows 2 to 50, and you're using an `ARRAYFORMULA`

that calls this function with the range `A2:A`

:

`=ARRAYFORMULA(APPLY_DISCOUNT(A2:A, $G$2))`

In this case:

For rows 2 to 50, where data exists, the function will calculate the prices normally.

For rows 51 and beyond, where cells in

`A51:A`

are empty, the function will return`null`

for each empty cell.

This behavior ensures that:

The

`ARRAYFORMULA`

doesn't fill the entire column with unnecessary values (such as`0`

).Empty input cells result in empty output cells, preserving the spreadsheet's readability.

If there are any non-numeric values in

`A2:A50`

(like text or errors), these will also result in empty cells in the output.

## Conclusion

In this tutorial, I showed you how to combine the power of `ARRAYFORMULA`

with custom functions to process entire columns efficiently in Google Sheets. This technique can significantly improve the performance of your spreadsheets, especially when dealing with large datasets or complex calculations.

By using `ARRAYFORMULA`

with custom functions, you can:

Process entire columns in one go, improving efficiency.

Handle both single cell inputs and array inputs in your custom functions.

Reduce the number of function calls, leading to faster calculations.

Create more maintainable and scalable spreadsheets.

Thanks for reading!

## Master Google Sheets Automation

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!