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
- Open the Apps Script Editor:
- Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
- 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
- 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.
- 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
- Enter Data:
- Enter a mix of even and odd numbers in a range of cells in your Google Sheet.
- Apply the Custom Function:
- Use the SUM_OF_EVENS function on the range containing your data to see the result.
- Verify the Result:
- Verify that the function correctly calculates the sum of all even numbers in the specified range.
Customizing the Function
- 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}`);
}
- 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
- Extend Google Sheets:
- Custom functions allow you to extend the functionality of Google Sheets to meet your specific needs.
- Reusable Code:
- Create reusable functions that can be used across multiple sheets and projects.
- 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.
