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

• Step 2 — Write a custom function

• Step 3 — Apply the custom function using ARRAYFORMULA

• Step 4 — Test and verify the results

• Step 5 — Compare performance

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.

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!