Creating Custom Functions with Google Apps Script

While Google Sheets offers a vast library of built-in functions, there will be times when you need to perform a calculation or process data in a way that a standard function simply can't handle. This is where custom functions, created with Google Apps Script, become incredibly powerful. They allow you to extend the functionality of Google Sheets and tailor it to your specific needs, automating complex or repetitive tasks directly within your spreadsheets.

Think of custom functions as adding your own unique formulas to Google Sheets. You can write them in JavaScript using the Google Apps Script editor, and then use them in your spreadsheet just like any other function (e.g., SUM, AVERAGE). This section will guide you through the process of creating and using your first custom functions.

To get started, you'll need to access the Google Apps Script editor. Here's how:

1. Open your Google Sheet.
2. Go to the 'Extensions' menu.
3. Select 'Apps Script'.

This will open a new browser tab with the script editor. You'll see a default script file named 'Code.gs'. This is where you'll write your custom functions.

Let's create a simple custom function that calculates the area of a rectangle. In the Code.gs file, delete any existing code and paste the following:

javascript function CALCULATE_RECTANGLE_AREA(length, width) { return length * width; }

In this code:

• function CALCULATE_RECTANGLE_AREA(length, width): This line defines a new function named CALCULATE_RECTANGLE_AREA. The names of custom functions used in Sheets are typically written in all caps to distinguish them from built-in JavaScript functions.
• (length, width): These are the arguments or inputs your function will accept. When you use this function in Sheets, you'll provide values for length and width.
• return length * width;: This is the core logic of your function. It multiplies the length and width arguments and returns the result.

After writing your function, you need to save the script. Click the floppy disk icon (Save project) in the script editor, and give your project a name if prompted (e.g., 'My Custom Functions').

Now, switch back to your Google Sheet. In any cell, you can now use your custom function as follows:

=CALCULATE_RECTANGLE_AREA(10, 5)

This formula will return 50.

You can also reference cells for your arguments:

=CALCULATE_RECTANGLE_AREA(A1, B1)

If cell A1 contains 10 and cell B1 contains 5, this will also return 50. This is where the real power of automation begins – your custom function can now dynamically react to changes in your spreadsheet data.

Let's explore another example: a function to convert Celsius to Fahrenheit. Add the following function to your Code.gs file, below the previous one:

javascript function CELSIUS_TO_FAHRENHEIT(celsius) { return (celsius * 9/5) + 32; }

Save your script again. Now you can use =CELSIUS_TO_FAHRENHEIT(25) in your sheet, which will output 77.

Google Apps Script custom functions can accept multiple arguments, including ranges of cells. For example, let's create a function that sums only the positive numbers in a given range.

javascript function SUM_POSITIVE_NUMBERS(inputRange) { let sum = 0; for (let i = 0; i < inputRange.length; i++) { for (let j = 0; j < inputRange[i].length; j++) { let cellValue = inputRange[i][j]; if (typeof cellValue === 'number' && cellValue > 0) { sum += cellValue; } } } return sum; }

When using this in your sheet, you would select a range of cells, like =SUM_POSITIVE_NUMBERS(A1:C10). The inputRange variable will be a 2D array representing the values in the specified range.

It's important to note a few limitations and best practices for custom functions:

• Execution Time: Custom functions have a maximum execution time of 30 seconds. For longer processes, you might need to explore other Apps Script features.
• Dependencies: Custom functions cannot directly call or interact with other Google services (like Gmail, Calendar) or external URLs. They are designed for calculations within Sheets.
• Permissions: Custom functions generally do not require explicit authorization like other Apps Script projects, as they run with the user's permission for that specific sheet.
• Documentation: Use JSDoc comments to document your custom functions. This will provide helpful tooltips when users type your function in the sheet.

Example of JSDoc documentation:

javascript /**

• Calculates the area of a rectangle.
• @param {number} length The length of the rectangle.
• @param {number} width The width of the rectangle.
• @return {number} The area of the rectangle.
• @customfunction */ function CALCULATE_RECTANGLE_AREA(length, width) { return length * width; }

The @customfunction tag is essential to make your function visible and usable within Google Sheets.

graph TD;
    A[Start: Open Google Sheet]
    B[Extensions > Apps Script]
    C[Write Custom Function in Editor]
    D[Save Script]
    E[Return to Sheet]
    F[Use Function in Cell (e.g., =MY_FUNCTION(A1))]
    G[Function Executes]
    H[Result Displayed in Cell]
    A --> B
    B --> C
    C --> D
    D --> E
    E --> F
    F --> G
    G --> H

By mastering custom functions, you unlock a new level of automation and data manipulation within Google Sheets, allowing you to build highly specialized tools and streamline your workflows significantly.