Part 4: Creating Custom Functions in Google Sheets

Google Sheets comes with a plethora of built-in functions that can help you perform calculations, manipulate data, and much more. However, there are times when you may need a custom function that doesn’t exist in Google Sheets by default. In this blog post, we’ll show you how to create your own custom functions using Google App Scripts, so you can extend the capabilities of your spreadsheets even further.

Getting Started with Custom Functions

Before we dive into creating custom functions, you need to open the script editor in your Google Sheet. To do this, click on “Extensions” in the top menu, then click “Apps Script.” This will open the Google App Script editor where you’ll be writing your custom functions.

To create a custom function, start by defining a new JavaScript function in the script editor. Custom functions follow the same rules as standard JavaScript functions. They can accept input parameters, return values, and use variables and control structures as needed.

Basic Custom Function Example

Here’s a simple example of a custom function that multiplies two numbers:

function multiply(a, b) {
  return a * b;
}

Once you’ve defined your custom function in the script editor, save your script by clicking the floppy disk icon or pressing Ctrl + S (Cmd + S on macOS). Now, you can use your custom function in your Google Sheet just like any other built-in function. In a cell, enter =multiply(2, 3), and the cell will display the result 6.

Custom Function Guidelines

When creating custom functions, there are some guidelines and best practices you should follow:

1. Function names should be descriptive and use camelCase. This means instead of separating words by spaces, you use lowercase for the first word and uppercase for every subsequent word.
2. Functions should return a value. If a function doesn’t return a value, Google Sheets will display an error. The exception to this is if you are using the function only in Google App Script.
3. Custom functions should not have side effects, such as modifying cells or other objects within the spreadsheet. Again, the exception to this is if you are using the function only in Google App Script.
4. Functions should have concise, clear names that describe their purpose.
5. Add comments to your functions (using // to make sure nothing on the line is run) to describe their purpose and parameters, so other users can understand how to use them.

Passing Arguments to Custom Functions

You can pass arguments to custom functions just like you would with built-in functions. The arguments can be values, cell references, or ranges. To pass a cell reference or range, simply enter the cell or range address as the argument.

Here’s an example of a custom function that calculates the sum of two cells:

function addCells(cellA, cellB) {
  let valueA = SpreadsheetApp.getActiveSheet().getRange(cellA).getValue();
  let valueB = SpreadsheetApp.getActiveSheet().getRange(cellB).getValue();

  return valueA + valueB;
}

In your Google Sheet, enter =addCells("A1", "B1") to calculate the sum of the values in cells A1 and B1.

Handling Errors in Custom Functions

When creating custom functions, it’s essential to handle errors gracefully. If your custom function encounters an error, it should return a relevant error message instead of just failing.

For example, consider a custom function that divides two numbers. If the divisor is zero, the function should return an error message instead of causing a division by zero error.

function divide(a, b) {
  if (b === 0) {
    return 'Error: Division by zero';
  }

  return a / b;
}

By returning an error message, your custom function provides useful feedback to the user,

and helps prevent unexpected behavior in your Google Sheet.

Creating Array Formulas with Custom Functions

Google Sheets supports array formulas, which allow you to perform calculations on entire ranges of data with a single formula. You can create custom functions that work as array formulas as well.

To create an array formula with your custom function, you should first make sure your function can handle input ranges (2D arrays of values). Then, use the ARRAYFORMULA() function in your Google Sheet to call your custom function on a range of cells.

Here’s an example of a custom function that calculates the product of each corresponding pair of cells in two ranges:

function multiplyRanges(rangeA, rangeB) {
  if (rangeA.length !== rangeB.length || rangeA[0].length !== rangeB[0].length) {
    return 'Error: Ranges must have the same dimensions';
  }

  let result = [];

  for (let row = 0; row < rangeA.length; row++) {
    let newRow = [];

    for (let col = 0; col < rangeA[row].length; col++) {
      newRow.push(rangeA[row][col] * rangeB[row][col]);
    }

    result.push(newRow);
  }

  return result;
}

To use this custom function as an array formula, enter =ARRAYFORMULA(multiplyRanges(A1:C3, D1:F3)) in your Google Sheet to calculate the product of each corresponding pair of cells in the ranges A1:C3 and D1:F3.

Debugging and Logging in Custom Functions

When working with custom functions, you may encounter errors or unexpected results. To help debug your functions, you can use the Logger.log() (you may also see console.log()) function in Google App Scripts to log messages, variables, and other data.

For example, consider the following custom function that calculates the factorial of a number:

function factorial(n) {
  if (n < 0) {
    return 'Error: Input must be a non-negative integer';
  }

  let result = 1;

  for (let i = 2; i <= n; i++) {
    result *= i;
    Logger.log('i: %s, result: %s', i, result);
  }

  return result;
}

To view the log messages generated by your custom function, run the function in the script editor using the “Select function” dropdown and clicking the “Run” button. Then, click “View” in the menu bar and select “Logs” to open the log viewer.

Conclusion

Creating custom functions in Google Sheets using Google App Scripts can help you automate tasks and enhance the functionality of your spreadsheets. By following best practices and handling errors gracefully, you can create powerful, reusable functions for a wide range of tasks.

With the knowledge you’ve gained in this blog post, you can now create your own custom functions to tackle unique challenges and make your Google Sheets even more powerful. In our next blog post, we’ll cover how to set up triggers in Google Sheets with Google App Scripts to automate your workflow further.