# Creating Custom Functions in Google Sheets Using Google Apps Script

Google Apps Script is a powerful tool that allows you to extend the functionality of Google Sheets by creating custom functions. In this blog post, we will walk through an exercise to write a custom function that calculates the sum of all even numbers in a given range. Additionally, we will explore how you can create and use custom functions in Google Sheets using Apps Script.

### Prerequisites

Before we begin, ensure you have:

• A Google account
• Basic knowledge of JavaScript
• Access to Google Sheets

### Step 1: Writing the Custom Function

1. Open the Apps Script Editor:
• Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
2. Create a New Script:
• Replace any code in the script editor with the following code to create a custom function that calculates the sum of all even numbers in a given range:

/**

* Calculates the sum of all even numbers in the given range.

*

* @param {Range} range The range of cells to calculate the sum of even numbers.

* @return The sum of all even numbers in the range.

* @customfunction

*/

function SUM_OF_EVENS(range) {

let sum = 0;

// Loop through the provided range

for (let i = 0; i < range.length; i++) {

for (let j = 0; j < range[i].length; j++) {

let value = range[i][j];

// Check if the value is a number and even

if (typeof value === ‘number’ && value % 2 === 0) {

sum += value;

}

}

}

return sum;

}

### Step 2: Using the Custom Function in Google Sheets

1. Save and Close the Script:
• Save your script by clicking the floppy disk icon or pressing Ctrl+S (Windows) or Cmd+S (Mac).
• Close the Apps Script editor to return to your Google Sheet.
2. Use the Custom Function:
• In your Google Sheet, enter the data you want to analyze.

Use the custom function SUM_OF_EVENS in a cell. For example:
=SUM_OF_EVENS(A1:C10)

• This function will calculate and return the sum of all even numbers within the specified range (A1
).

### Step 3: Testing the Custom Function

1. Enter Data:
• Enter a mix of even and odd numbers in a range of cells in your Google Sheet.
2. Apply the Custom Function:
• Use the SUM_OF_EVENS function on the range containing your data to see the result.
3. Verify the Result:
• Verify that the function correctly calculates the sum of all even numbers in the specified range.

### Customizing the Function

1. Handling Non-Numeric Values:
• Modify the function to handle non-numeric values more gracefully by ignoring them.

if (typeof value === ‘number’ && value % 2 === 0) {

sum += value;

} else {

Logger.log(`Ignored non-numeric value: \${value}`);

}

1. Adding Error Handling:
• Add error handling to manage cases where the input range is invalid or empty.

if (!range || range.length === 0) {

throw new Error(‘Invalid range. Please provide a valid range of cells.’);

}

### Benefits of Custom Functions

1. Extend Google Sheets:
• Custom functions allow you to extend the functionality of Google Sheets to meet your specific needs.
2. Reusable Code:
• Create reusable functions that can be used across multiple sheets and projects.
3. Automation:
• Automate complex calculations and data manipulations, saving time and reducing errors.

### Conclusion

With this exercise, you’ve learned how to create a custom function in Google Sheets using Google Apps Script. The SUM_OF_EVENS function demonstrates how to calculate the sum of all even numbers in a given range. By leveraging custom functions, you can extend the capabilities of Google Sheets to better meet your needs and automate complex tasks.