Introduction:
Google Apps Script allows you to extend the functionality of Google Sheets by creating custom formulas and functions. These custom functions behave just like built-in spreadsheet functions and can be used directly within cells. By leveraging custom functions, you can perform complex calculations, manipulate data, and integrate external services directly from your spreadsheet. This chapter explores how to create, deploy, and use custom formulas and functions in Google Apps Script, along with best practices for error handling, optimization, and user interaction.
1. Understanding Custom Functions
1.1. What is a Custom Function?
- Definition:
- A custom function is a function you write in Apps Script that can be called from within a Google Sheets cell, similar to built-in functions like SUM() or VLOOKUP().
- Characteristics:
- Custom functions are defined using JavaScript.
- They must be declared as global functions.
- The function name becomes the formula name in the spreadsheet.
1.2. Basic Syntax of a Custom Function
Function Declaration:
function FUNCTION_NAME(input1, input2, …) {
// Function logic
return output;
}
Example:
function DOUBLE(number) {
return number * 2;
}
- Usage in Spreadsheet:
- In a cell: =DOUBLE(5) would return 10.
2. Creating Custom Functions
2.1. Writing Your First Custom Function
Step-by-Step Example:
Function to Convert Celsius to Fahrenheit:
function CELSIUS_TO_FAHRENHEIT(celsius) {
return (celsius * 9/5) + 32;
}
- Using the Function:
- In a cell: =CELSIUS_TO_FAHRENHEIT(25) returns 77.
2.2. Handling Multiple Inputs
Example: Summing Multiple Numbers
function SUM_NUMBERS(a, b, c) {
return a + b + c;
}
- Using the Function:
- In a cell: =SUM_NUMBERS(1, 2, 3) returns 6.
2.3. Working with Ranges
Processing Ranges as Inputs:
function SUM_RANGE(range) {
var total = 0;
for (var i = 0; i < range.length; i++) {
for (var j = 0; j < range[i].length; j++) {
total += range[i][j];
}
}
return total;
}
- Using the Function:
- In a cell: =SUM_RANGE(A1:B5) sums all values in the range A1:B5.
3. Custom Function Requirements and Limitations
3.1. Requirements
- Function Declaration:
- Must be a global function (not nested within another function).
- The function name should be unique and alphanumeric.
- Return Value:
- Must return a value or an array of values.
- Cannot return JavaScript objects or undefined.
3.2. Limitations
- No UI Interaction:
- Custom functions cannot display prompts, dialogs, or interact with the UI.
- No Permanent Changes:
- Cannot modify the spreadsheet (e.g., cannot set values or formats).
- Execution Time:
- Subject to a 30-second execution time limit.
- Authorization:
- Cannot perform actions that require authorization (e.g., sending emails).
4. Advanced Custom Functions
4.1. Returning Arrays
Creating Functions that Return Multiple Values:
Example: Generating a Sequence
function GENERATE_SEQUENCE(start, end) {
var result = [];
for (var i = start; i <= end; i++) {
result.push([i]); // Wrap in array to return as a column
}
return result;
}
- Using the Function:
- In a cell: =GENERATE_SEQUENCE(1, 5)
- Result: A column with values from 1 to 5.
4.2. Handling Optional Parameters
Using Default Values:
function GREET(name, greeting) {
greeting = greeting || ‘Hello’;
return greeting + ‘, ‘ + name + ‘!’;
}
Using the Function:
- =GREET(“Alice”) returns “Hello, Alice!”.
- =GREET(“Alice”, “Hi”) returns “Hi, Alice!”.
4.3. Error Handling in Custom Functions
Throwing Errors:
function DIVIDE(a, b) {
if (b === 0) {
throw new Error(“Division by zero is not allowed.”);
}
return a / b;
}
Using the Function:
- =DIVIDE(10, 0) results in an error message in the cell.
5. Integrating External Data
5.1. Fetching Data from URLs
Example: Fetching JSON Data
function GET_JSON_VALUE(url, key) {
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
return data[key];
}
- Using the Function:
- =GET_JSON_VALUE(“https://api.example.com/data”, “price”)
- Note: Functions that access external data may require authorization and cannot be used without enabling permissions.
5.2. Caching Results
Using Cache Service to Improve Performance:
function GET_STOCK_PRICE(symbol) {
var cache = CacheService.getScriptCache();
var cached = cache.get(symbol);
if (cached !== null) {
return parseFloat(cached);
} else {
var url = ‘https://api.example.com/stock/’ + symbol;
var response = UrlFetchApp.fetch(url);
var price = parseFloat(response.getContentText());
cache.put(symbol, price, 600); // Cache for 10 minutes
return price;
}
}
- Using the Function:
- =GET_STOCK_PRICE(“GOOG”)
6. Localization and Language Support
6.1. Handling Locale Differences
- Decimal Separators:
- Be aware of different decimal separators (e.g., . vs ,).
Example:
function LOCALIZED_SUM(a, b) {
var locale = Session.getActiveUserLocale();
var decimalSeparator = (locale === ‘de_DE’) ? ‘,’ : ‘.’;
// Implement logic based on decimalSeparator
return a + b;
}
6.2. Translating Function Names
- Custom Functions are Not Translated:
- Function names remain the same across locales.
- Best Practice:
- Use English function names and handle localization within the function logic if necessary.
7. Best Practices for Custom Functions
7.1. Naming Conventions
- Use Clear and Descriptive Names:
- Function names should reflect their purpose.
- Avoid Conflicts:
- Ensure function names do not conflict with built-in functions.
7.2. Documentation and Help Text
Adding Function Descriptions:
/**
* Calculates the area of a circle.
*
* @param {number} radius The radius of the circle.
* @return {number} The area of the circle.
* @customfunction
*/
function CIRCLE_AREA(radius) {
return Math.PI * Math.pow(radius, 2);
}
- Benefits:
- Descriptions appear in the function help within Google Sheets.
7.3. Optimizing Performance
- Minimize Execution Time:
- Avoid unnecessary computations.
- Use caching for repeated external data requests.
- Handle Errors Gracefully:
- Provide informative error messages.
- Avoid Side Effects:
- Do not attempt to modify the spreadsheet or use functions that require authorization.
8. Examples of Useful Custom Functions
8.1. Text Manipulation
Function to Reverse Text:
function REVERSE_TEXT(text) {
return text.split(”).reverse().join(”);
}
- Usage:
- =REVERSE_TEXT(“Hello”) returns “olleH”.
8.2. Date Calculations
Function to Calculate Age:
function CALCULATE_AGE(birthdate) {
var today = new Date();
var age = today.getFullYear() – birthdate.getFullYear();
var m = today.getMonth() – birthdate.getMonth();
if (m < 0 || (m === 0 && today.getDate() < birthdate.getDate())) {
age–;
}
return age;
}
- Usage:
- =CALCULATE_AGE(DATE(1990, 1, 1))
8.3. Data Validation
Function to Check Email Format:
function IS_VALID_EMAIL(email) {
var regex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
return regex.test(email);
}
- Usage:
- =IS_VALID_EMAIL(“user@example.com”) returns TRUE.
8.4. Custom Lookup Function
Function to Perform Case-Insensitive Lookup:
function CASE_INSENSITIVE_VLOOKUP(searchKey, range, index) {
searchKey = searchKey.toString().toLowerCase();
for (var i = 0; i < range.length; i++) {
if (range[i][0].toString().toLowerCase() === searchKey) {
return range[i][index – 1];
}
}
return ‘Not Found’;
}
- Usage:
- =CASE_INSENSITIVE_VLOOKUP(“apple”, A1:B10, 2)
9. Testing and Debugging Custom Functions
9.1. Using the Logger
Log Values for Debugging:
function DEBUG_EXAMPLE(input) {
Logger.log(‘Input value: ‘ + input);
return input;
}
- Note: Logs can be viewed in the Apps Script editor under “View” > “Logs”.
9.2. Testing in the Apps Script Editor
Create Test Functions:
function testCustomFunction() {
var result = CUSTOM_FUNCTION_NAME(testInput);
Logger.log(‘Result: ‘ + result);
}
- Run Tests:
- Execute the test function from the editor to verify behavior.
9.3. Handling Errors
Catch Exceptions:
function SAFE_DIVIDE(a, b) {
try {
if (b === 0) {
throw new Error(“Cannot divide by zero.”);
}
return a / b;
} catch (error) {
return error.message;
}
}
10. Deploying and Sharing Custom Functions
10.1. Bound Scripts vs. Standalone Scripts
- Bound Scripts:
- Tied to a specific spreadsheet.
- Custom functions are only available in that spreadsheet.
- Standalone Scripts:
- Can be used across multiple spreadsheets.
- Require deployment as an add-on or library for broader use.
10.2. Sharing Custom Functions
- Copying the Spreadsheet:
- Users can copy the spreadsheet containing the custom functions.
- Publishing as an Add-on:
- Allows distribution through the Google Workspace Marketplace.
- Requires compliance with Google’s policies and guidelines.
10.3. Using Libraries
- Creating a Library:
- Publish the script project as a library.
- Adding the Library to Other Projects:
- Include the library in other scripts to reuse functions.
11. Practical Exercises
11.1. Short Answer Questions
a) What are the requirements for a function to be used as a custom function in Google Sheets?
b) Why can’t custom functions modify the spreadsheet or display dialogs?
c) How can you handle errors within a custom function to provide informative messages to the user?
d) Explain how to create a custom function that accepts a range of cells as input and returns the maximum value.
e) What are the benefits of adding JSDoc comments to your custom functions?
11.2. Practical Exercises
a) Write a custom function named CONCAT_RANGE that concatenates all text values in a given range into a single string, separated by commas.
b) Create a custom function IS_PRIME that checks if a given number is a prime number and returns TRUE or FALSE.
c) Develop a custom function WEATHER_INFO that fetches the current temperature for a given city using a public weather API. Handle cases where the city is not found.
d) Write a custom function RGB_TO_HEX that converts RGB values (provided as three separate inputs) to a hexadecimal color code.
11.3. Code Analysis
Given the following custom function:
function ADD_DAYS(date, days) {
var newDate = new Date(date);
newDate.setDate(newDate.getDate() + days);
return newDate;
}
a) Explain how this function works and how you would use it in a spreadsheet.
b) Identify any potential issues with this function when used in different locales or with invalid inputs.
12. Multiple Choice Questions
- Which of the following is a requirement for a function to be used as a custom function in Google Sheets?
a) It must be a nested function.
b) It must return a value.
c) It can display a dialog to the user.
d) It must modify the spreadsheet. - Custom functions cannot perform actions that require authorization because:
a) They run with limited permissions for security reasons.
b) They don’t have access to the user’s session.
c) They are only meant for simple calculations.
d) They can’t access external services. - How can you make a custom function’s description appear in Google Sheets’ auto-complete and help?
a) By adding a @description tag in the function’s comments.
b) By using JSDoc-style comments with @customfunction.
c) By naming the function in uppercase letters.
d) By publishing the function as an add-on. - What will happen if a custom function throws an exception using throw new Error(“Message”)?
a) The cell will display the error message provided.
b) The function will return undefined.
c) The spreadsheet will crash.
d) Nothing; custom functions can’t throw exceptions. - Which of the following methods can be used inside a custom function?
a) SpreadsheetApp.getActiveSpreadsheet()
b) Browser.msgBox(“Hello”)
c) Utilities.formatDate(date, timeZone, format)
d) SpreadsheetApp.flush() - To return multiple values from a custom function and display them across multiple cells, you should:
a) Return a string with values separated by commas.
b) Return an array of arrays representing rows and columns.
c) Use setValues() to write directly to the cells.
d) Return a JavaScript object. - Custom functions in Google Sheets run:
a) On the server side, within the Apps Script environment.
b) On the client side, within the user’s browser.
c) As a macro recorded in the spreadsheet.
d) As an add-on installed from the marketplace. - If you need to use a custom function across multiple spreadsheets, which approach is NOT recommended?
a) Copying and pasting the code into each spreadsheet’s script editor.
b) Publishing the function as an add-on.
c) Creating a library and including it in each project.
d) Using import statements to include the script. - What is the maximum execution time for a custom function in Google Sheets?
a) 5 seconds
b) 30 seconds
c) 60 seconds
d) 90 seconds - Which statement about custom functions is TRUE?
a) They can create and modify menus and UI elements.
b) They can use the CacheService to store data.
c) They can send emails using MailApp.sendEmail().
d) They can write data to other cells using setValue().
13. Answers to Exercises
11.1. Short Answer Questions
a) Requirements for a custom function:
- Must be a global function (not nested).
- Must be declared without any access modifiers (e.g., no private or public).
- Must return a value.
- Function name should consist of letters, numbers, and underscores, and not conflict with built-in functions.
b) Why custom functions can’t modify the spreadsheet or display dialogs:
- Custom functions run in a restricted context for security and performance reasons.
- They are intended to be pure functions without side effects.
- Interacting with the spreadsheet or UI could lead to unpredictable behavior and security risks.
c) Handling errors within a custom function:
- Use throw new Error(“Error message”) to display an error in the cell.
- This provides the user with a clear indication of what went wrong.
Example:
function SAFE_DIVIDE(a, b) {
if (b === 0) {
throw new Error(“Cannot divide by zero.”);
}
return a / b;
}
d) Creating a custom function to find the maximum value in a range:
- Define the function to accept a range input.
- Iterate over the range to find the maximum value.
Return the maximum value.
function MAX_IN_RANGE(range) {
var max = null;
for (var i = 0; i < range.length; i++) {
for (var j = 0; j < range[i].length; j++) {
var value = range[i][j];
if (typeof value === ‘number’) {
if (max === null || value > max) {
max = value;
}
}
}
}
return max;
}
e) Benefits of adding JSDoc comments:
- Provides descriptions and usage information in the spreadsheet’s function help.
- Helps users understand what the function does and how to use it.
- Improves code readability and maintainability.
11.2. Practical Exercises
a) Custom function CONCAT_RANGE:
/**
* Concatenates all text values in a given range, separated by commas.
*
* @param {Range} range The range containing text values to concatenate.
* @return {string} The concatenated string.
* @customfunction
*/
function CONCAT_RANGE(range) {
var result = [];
for (var i = 0; i < range.length; i++) {
for (var j = 0; j < range[i].length; j++) {
var value = range[i][j];
if (value !== ”) {
result.push(value);
}
}
}
return result.join(‘, ‘);
}
b) Custom function IS_PRIME:
/**
* Checks if a number is prime.
*
* @param {number} number The number to check.
* @return {boolean} TRUE if the number is prime, FALSE otherwise.
* @customfunction
*/
function IS_PRIME(number) {
if (number <= 1 || number !== Math.floor(number)) {
return false;
}
for (var i = 2; i <= Math.sqrt(number); i++) {
if (number % i === 0) {
return false;
}
}
return true;
}
c) Custom function WEATHER_INFO:
/**
* Fetches the current temperature for a given city.
*
* @param {string} city The name of the city.
* @return {number} The current temperature in Celsius.
* @customfunction
*/
function WEATHER_INFO(city) {
var apiKey = ‘your_api_key_here’; // Replace with your API key
var url = ‘https://api.openweathermap.org/data/2.5/weather?q=’ + encodeURIComponent(city) + ‘&units=metric&appid=’ + apiKey;
try {
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
if (data.main && data.main.temp) {
return data.main.temp;
} else {
throw new Error(‘City not found.’);
}
} catch (error) {
return ‘Error: ‘ + error.message;
}
}
Note: Replace ‘your_api_key_here’ with a valid API key from OpenWeatherMap or another weather service.
d) Custom function RGB_TO_HEX:
/**
* Converts RGB values to a hexadecimal color code.
*
* @param {number} red The red component (0-255).
* @param {number} green The green component (0-255).
* @param {number} blue The blue component (0-255).
* @return {string} The hexadecimal color code.
* @customfunction
*/
function RGB_TO_HEX(red, green, blue) {
function componentToHex(c) {
var hex = Math.round(c).toString(16);
return hex.length === 1 ? ‘0’ + hex : hex;
}
if ([red, green, blue].some(function(c) { return c < 0 || c > 255; })) {
throw new Error(‘RGB values must be between 0 and 255.’);
}
return ‘#’ + componentToHex(red) + componentToHex(green) + componentToHex(blue);
}
11.3. Code Analysis
a) Explanation of ADD_DAYS function:
- Functionality:
- Accepts a date and a number of days.
- Creates a new Date object from the input date.
- Adds the specified number of days to the date.
- Returns the new date.
- Usage in Spreadsheet:
- =ADD_DAYS(DATE(2021,1,1), 5) returns January 6, 2021.
b) Potential issues:
- Locale Differences:
- Date parsing may vary based on locale settings.
- If date is not a valid Date object or serial number, it may not work correctly.
- Invalid Inputs:
- If date is not provided or is invalid, the function may return an error.
- If days is not a number, it may not perform the addition correctly.
- Improvement:
- Validate inputs and handle errors gracefully.
function ADD_DAYS(date, days) {
if (!(date instanceof Date) || isNaN(days)) {
throw new Error(‘Invalid input.’);
}
var newDate = new Date(date);
newDate.setDate(newDate.getDate() + days);
return newDate;
}
14. Answers to Multiple Choice Questions
- Answer: b) It must return a value.
- Answer: a) They run with limited permissions for security reasons.
- Answer: b) By using JSDoc-style comments with @customfunction.
- Answer: a) The cell will display the error message provided.
- Answer: c) Utilities.formatDate(date, timeZone, format)
- Answer: b) Return an array of arrays representing rows and columns.
- Answer: a) On the server side, within the Apps Script environment.
- Answer: d) Using import statements to include the script.
- Answer: b) 30 seconds
- Answer: b) They can use the CacheService to store data.
By learning how to implement custom formulas and functions in Google Apps Script, you can extend the capabilities of Google Sheets to meet specific needs and automate complex calculations. Custom functions empower you to create tailored solutions, integrate external data sources, and enhance the interactivity of your spreadsheets, all while maintaining the familiar interface of Google Sheets.