Book Source Code on GitHub https://github.com/lsvekis/Sheets-Custom-Formulas-Book

Get the Best Selling Book Free for the next 4 days

1 : Mastering Basic Custom Functions

In this opening chapter, we introduce the foundational concepts of Google Apps Script for custom functions in Google Sheets. Begin by learning how to create simple functions that perform calculations such as summing ranges, generating unique codes, and converting data types. Each section includes sample data, a step-by-step guide to implementing the function, and practical exercises to enhance your learning.

Function for Summing Up Specific Ranges

Sample Data: SUM_OVER_THRESHOLD

A	B
10
55
65
30
75
20
40
85
25
95

Objective: Write a custom function that sums the values in a specified range, but only if they are greater than a certain value.

Learning Outcomes:

Understand the syntax for defining custom functions.
Learn to manipulate cell ranges and arrays.
Apply conditional logic to filter out unwanted data.

Code Example:

/**

* Returns the sum of numbers in a given range that are greater than a specified threshold.

* @param {range} data The range to sum over.

* @param {number} threshold The minimum value to include in the sum.

* @return {number} The sum of the numbers greater than the threshold.

* @customfunction

function SUM_OVER_THRESHOLD(data, threshold) {

return data.flat().filter(val => typeof val === ‘number’ && val > threshold).reduce((sum, val) => sum + val, 0);

}

Steps:

Open a Google Sheet and go to Extensions > Apps Script to open the Apps Script editor.
Create a new project or use an existing one, and paste the code above into the script.
Save your changes and return to the Sheet.
Input some numerical data in a range, e.g., A1:A10.
In an empty cell, test the function with the formula: =SUM_OVER_THRESHOLD(A1:A10, 50). Replace 50 with your desired threshold.

Expected Result: The function will return the sum of all numbers in the range that are greater than the threshold.

Function to Concatenate and Format Strings

Sample Data: CONCATENATE_WITH_SEPARATOR

Alice	Smith
Bob	Johnson
Charlie	Brown
Dave	Wilson
Eve	Taylor

Objective: Write a function that concatenates strings from two different columns and formats the output.

Learning Outcomes:

Learn how to manipulate strings.
Understand the concept of joining and formatting data.

Code Example:

/**

* Concatenates values from two columns with a separator.

* @param {range} col1 The first column of strings.

* @param {range} col2 The second column of strings.

* @param {string} separator The separator string.

* @return {array} The concatenated and formatted strings.

* @customfunction

function CONCATENATE_WITH_SEPARATOR(col1, col2, separator) {

const result = [];

for (let i = 0; i < Math.min(col1.length, col2.length); i++) {

result.push([col1[i][0] + separator + col2[i][0]]);

}

return result;

}

Steps:

Open the Apps Script editor from the Google Sheet.
Create a new project or use an existing one, and paste the code above into the script.
Save the changes and go back to the Sheet.
Enter text data in two columns, e.g., A1:A5 and B1:B5.
Use the function with a formula like =CONCATENATE_WITH_SEPARATOR(A1:A5, B1:B5, “, “).

Expected Result: The output will be a new column where the values from both columns are combined and separated by the provided separator.

Function to Calculate Days Until a Future Date

Sample Data: DAYS_TO_FUTURE_DATE

2024-06-01

2024-07-15

2024-12-25

2025-01-01

2025-04-01

Objective: Write a function that calculates the number of days from today to a given date.

Learning Outcomes:

Learn to work with date objects and calculate time intervals.
Understand how to handle and validate input data.

Code Example:

/**

* Calculates the number of days from today to a specified future date.

* @param {date} futureDate The future date to compare against today.

* @return {number} The number of days from today to the specified future date.

* @customfunction

function DAYS_TO_FUTURE_DATE(futureDate) {

const today = new Date();

const date = new Date(futureDate);

const diffTime = date – today;

const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));

return diffDays >= 0 ? diffDays : “Date is in the past!”;

}

Steps:

Open the Apps Script editor from the Google Sheet.
Create a new project or use an existing one, and paste the code above into the script.
Save the changes and return to the Sheet.
Provide a future date in a cell, e.g., A1 with a value like 2024-06-01.
Use the custom function: =DAYS_TO_FUTURE_DATE(A1).

Expected Result: The function will return the number of days between today and the specified future date or a warning message if the date is in the past.

Calculate and Categorize Average

Sample Data: AVERAGE_WITH_CATEGORY

10
20
35
45
55
65
75
85
95
105

Objective: Write a function to calculate the average of a specified range and categorize it as “Low,” “Medium,” or “High” based on user-defined thresholds.

Learning Outcomes:

Learn how to work with statistical functions.
Understand how to categorize numeric values based on ranges.

Code Example:

/**

* Calculates the average of a range and categorizes it based on user thresholds.

* @param {range} data The range of numbers to calculate the average for.

* @param {number} lowThreshold The upper limit of the “Low” category.

* @param {number} highThreshold The lower limit of the “High” category.

* @return {string} The average value and its category.

* @customfunction

function AVERAGE_WITH_CATEGORY(data, lowThreshold, highThreshold) {

const validNumbers = data.flat().filter(val => typeof val === ‘number’);

if (validNumbers.length === 0) return “No numeric data!”;

const avg = validNumbers.reduce((sum, val) => sum + val, 0) / validNumbers.length;

let category;

if (avg < lowThreshold) {

category = “Low”;

} else if (avg >= highThreshold) {

category = “High”;

} else {

category = “Medium”;

}

return `Average: ${avg.toFixed(2)}, Category: ${category}`;

}

Steps:

Open the Apps Script editor and add this code to a new project.
Save changes and return to Google Sheets.
Enter a set of numerical data in a column, e.g., A1:A10.
Use the custom function like =AVERAGE_WITH_CATEGORY(A1:A10, 30, 70).

Expected Result: The function will calculate the average of the data and return the category label based on the thresholds provided.

Generate Unique Codes

Sample Data: GENERATE_UNIQUE_CODES

(No input data needed, this function generates its own data.)

Objective: Create a function to generate unique alphanumeric codes for a specified range of rows.

Learning Outcomes:

Learn to generate random alphanumeric strings.
Understand the concept of looping through ranges and filling rows.

Code Example:

/**

* Generates unique alphanumeric codes for a specified number of rows.

* @param {number} numCodes The number of unique codes to generate.

* @param {number} length The length of each code.

* @return {array} An array of unique alphanumeric codes.

* @customfunction

function GENERATE_UNIQUE_CODES(numCodes, length) {

const result = [];

const characters = ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’;

for (let i = 0; i < numCodes; i++) {

let code = ”;

for (let j = 0; j < length; j++) {

code += characters.charAt(Math.floor(Math.random() * characters.length));

}

result.push([code]);

}

return result;

}

Steps:

Add the provided script to a new Apps Script project.
Save the changes and return to Google Sheets.
Use the custom function with =GENERATE_UNIQUE_CODES(10, 8).

Expected Result: This function will generate 10 unique alphanumeric codes of length 8 and display them in adjacent cells.

Extract and Count Words in a Text

COUNT_WORDS

The quick brown fox jumps over the lazy dog

Objective: Write a custom function that extracts distinct words from a string, counts them, and displays the count of each word.

Learning Outcomes:

Practice string splitting and filtering.
Learn to count occurrences using an object for mapping.

Code Example:

/**

* Extracts distinct words from a string and counts their occurrences.

* @param {string} text The text from which to extract words.

* @return {array} An array of words and their counts.

* @customfunction

function COUNT_WORDS(text) {

const words = text.toLowerCase().match(/\b\w+\b/g);

if (!words) return “No words found!”;

const wordCounts = {};

words.forEach(word => {

wordCounts[word] = (wordCounts[word] || 0) + 1;

});

const result = Object.entries(wordCounts).map(([word, count]) => [word, count]);

return result;

}

Steps:

Add the script above to a new Apps Script project.
Save and return to Google Sheets.
Input a long text string in a cell, e.g., A1.
Use the custom function with =COUNT_WORDS(A1).

Expected Result: The function will return an array where each row contains a word from the text and its count.

Finding the Longest String in a Range

Sample Data: LONGEST_STRING

Short

Moderate

Longer

Longest

Objective: Write a function that finds the longest string in a specified range of cells.

Learning Outcomes:

Understand how to work with strings in arrays.
Learn to identify and compare string lengths.

Code Example:

/**

* Finds the longest string in a specified range.

* @param {range} data The range to search through.

* @return {string} The longest string found in the range.

* @customfunction

function LONGEST_STRING(data) {

let longest = ”;

data.flat().forEach(item => {

if (typeof item === ‘string’ && item.length > longest.length) {

longest = item;

}

});

return longest || “No strings found!”;

}

Steps:

Open the Apps Script editor and add this code to a new project.
Save changes and return to Google Sheets.
Enter a range of strings, e.g., A1:A10.
Use the custom function like =LONGEST_STRING(A1:A10).

Expected Result: This function will return the longest string found in the provided range.

Checking for Duplicate Values

Sample Data: FIND_DUPLICATES

Objective: Create a custom function that checks for duplicate values in a specified range and returns those duplicates.

Learning Outcomes:

Learn to work with JavaScript objects as hash maps.
Understand how to efficiently identify duplicates.

Code Example:

/**

* Checks for duplicate values in a specified range.

* @param {range} data The range of values to check for duplicates.

* @return {array} An array of duplicate values found.

* @customfunction

function FIND_DUPLICATES(data) {

const flatData = data.flat();

const occurrences = {};

const duplicates = [];

flatData.forEach(item => {

occurrences[item] = (occurrences[item] || 0) + 1;

});

Object.entries(occurrences).forEach(([key, count]) => {

if (count > 1) {

duplicates.push([key]);

}

});

return duplicates.length > 0 ? duplicates : “No duplicates found!”;

}

Steps:

Add the above code to a new Apps Script project.
Save and return to Google Sheets.
Input data with some duplicate values in a range, e.g., A1:A10.
Use the custom function =FIND_DUPLICATES(A1:A10).

Expected Result: This function will return an array listing all values that appear more than once in the provided range.

Calculate the Fibonacci Sequence

Sample Data: GENERATE_FIBONACCI

(No input data needed, this function generates its own data.)

Objective: Write a function to generate a specified number of Fibonacci numbers starting from the first two.

Learning Outcomes:

Practice working with arrays to generate sequential data.
Understand the principles of recursion and iterative algorithms.

Code Example:

/**

* Generates a Fibonacci sequence of the given length.

* @param {number} numTerms The number of terms to generate.

* @return {array} An array of Fibonacci numbers.

* @customfunction

function GENERATE_FIBONACCI(numTerms) {

const fib = [0, 1];

for (let i = 2; i < numTerms; i++) {

fib.push(fib[i – 1] + fib[i – 2]);

}

return fib.slice(0, numTerms).map(num => [num]);

}

Steps:

Paste the code into the Apps Script editor and save your changes.
Return to Google Sheets.
Use the function with =GENERATE_FIBONACCI(10).

Expected Result: The function will return an array containing the first 10 Fibonacci numbers in adjacent cells.

These exercises will help you work more effectively with arrays, hash maps, and sequential data generation in Google Apps Script.

Convert Celsius to Fahrenheit

Sample Data: CELSIUS_TO_FAHRENHEIT

-10

Objective: Create a custom function that converts temperatures from Celsius to Fahrenheit for a range of data.

Learning Outcomes:

Practice working with mathematical formulas.
Understand how to handle numeric input and return processed data.

Code Example:

/**

* Converts temperatures from Celsius to Fahrenheit.

* @param {range} data The range of temperatures in Celsius to convert.

* @return {array} An array of temperatures converted to Fahrenheit.

* @customfunction

function CELSIUS_TO_FAHRENHEIT(data) {

return data.flat().map(celsius => {

if (typeof celsius !== ‘number’) return “Invalid input”;

return [celsius * 9/5 + 32];

});

}

Steps:

Open the Apps Script editor and paste the code into a new project.
Save the changes and return to Google Sheets.
Enter a range of temperatures in Celsius, e.g., A1:A10.
Use the custom function like =CELSIUS_TO_FAHRENHEIT(A1:A10).

Expected Result: This function will return an array with temperatures converted to Fahrenheit.

Remove Vowels from a String

Sample Data: REMOVE_VOWELS

Remove all vowels from this sentence.

Objective: Create a custom function that removes all vowels from a given string.

Learning Outcomes:

Understand how to manipulate strings using regular expressions.
Learn to handle edge cases for invalid or empty inputs.

Code Example:

/**

* Removes all vowels from the provided string.

* @param {string} inputText The input string to process.

* @return {string} The string with vowels removed.

* @customfunction

function REMOVE_VOWELS(inputText) {

if (typeof inputText !== ‘string’) return “Invalid input”;

return inputText.replace(/[aeiouAEIOU]/g, ”);

}

Steps:

Paste the code into a new Apps Script project.
Save and return to Google Sheets.
Enter any string in a cell, e.g., A1.
Use the custom function like =REMOVE_VOWELS(A1).

Expected Result: This function will return the string with all vowels removed.

Sum Only Even Numbers

Sample Data: SUM_EVEN_NUMBERS

Objective: Create a function that calculates the sum of only even numbers in a given range.

Learning Outcomes:

Practice using the filter method to process arrays.
Understand conditional logic to isolate specific values.

Code Example:

/**

* Sums only even numbers from the given range.

* @param {range} data The range of numbers to sum up.

* @return {number} The sum of even numbers in the specified range.

* @customfunction

function SUM_EVEN_NUMBERS(data) {

return data.flat().filter(num => typeof num === ‘number’ && num % 2 === 0).reduce((sum, num) => sum + num, 0);

}

Steps:

Paste the code into a new project in the Apps Script editor.
Save the changes and return to Google Sheets.
Enter a range of numbers in any column, e.g., A1:A10.
Use the function like =SUM_EVEN_NUMBERS(A1:A10).

Expected Result: This function will return the sum of only even numbers in the specified range.

Calculate the Median of a Range

Sample Data: MEDIAN_OF_RANGE

Objective: Create a custom function that calculates the median value from a range of numbers.

Learning Outcomes:

Understand the concept of sorting and median calculation.
Learn how to handle cases where the input data is not purely numeric.

Code Example:

/**

* Calculates the median of a given range of numbers.

* @param {range} data The range of numbers to calculate the median for.

* @return {number|string} The median value or an error message if no valid numbers are found.

* @customfunction

function MEDIAN_OF_RANGE(data) {

// Filter out non-numeric data and flatten the input array

const numbers = data.flat().filter(val => typeof val === ‘number’);

// Check if there are any valid numbers in the range

if (numbers.length === 0) return “No numeric data!”;

// Sort numbers in ascending order

numbers.sort((a, b) => a – b);

// Find the median value

const middle = Math.floor(numbers.length / 2);

return numbers.length % 2 === 0 ? (numbers[middle – 1] + numbers[middle]) / 2 : numbers[middle];

}

Steps:

Open the Apps Script editor and paste this code into a new project.
Save the script and return to Google Sheets.
Enter some numerical data in a range, e.g., A1:A10.
Use the function with =MEDIAN_OF_RANGE(A1:A10).

Expected Result: The function will return the median of the specified range or an error message if no valid numbers are found.

Count Uppercase and Lowercase Letters

Sample Data: COUNT_CASES

This is a Test String with UPPERCASE and lowercase.

Objective: Create a custom function that counts the number of uppercase and lowercase letters in a string.

Learning Outcomes:

Understand regular expression usage for pattern matching.
Practice using JavaScript object mappings for counting.

Code Example:

/**

* Counts the number of uppercase and lowercase letters in a given string.

* @param {string} inputText The text to count uppercase and lowercase letters.

* @return {array} An array with the count of uppercase and lowercase letters.

* @customfunction

function COUNT_CASES(inputText) {

if (typeof inputText !== ‘string’) return “Invalid input”;

// Initialize counters for uppercase and lowercase

const upperCount = (inputText.match(/[A-Z]/g) || []).length;

const lowerCount = (inputText.match(/[a-z]/g) || []).length;

return [[“Uppercase”, upperCount], [“Lowercase”, lowerCount]];

}

Steps:

Add the code to a new Apps Script project.
Save and return to Google Sheets.
Enter a text string in a cell, e.g., A1.
Use the function with =COUNT_CASES(A1).

Expected Result: The function will return the count of uppercase and lowercase letters in the provided string.

Split Text into Sentences

Sample Data: SPLIT_SENTENCES

This is the first sentence. Here is the second one! Is this the third? Yes, it is.

Objective: Create a custom function that splits a text string into sentences.

Learning Outcomes:

Learn to work with regular expressions to identify sentence boundaries.
Practice manipulating strings with arrays.

Code Example:

/**

* Splits the input text into sentences.

* @param {string} inputText The text to split into sentences.

* @return {array} An array of sentences extracted from the input text.

* @customfunction

function SPLIT_SENTENCES(inputText) {

if (typeof inputText !== ‘string’) return “Invalid input”;

// Regular expression to identify sentences

const sentences = inputText.match(/[^.!?]+[.!?]/g) || [];

// Return sentences as an array of single-item arrays for Google Sheets

return sentences.map(sentence => [sentence.trim()]);

}

Steps:

Paste the code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter a long text string in a cell, e.g., A1.
Use the function like =SPLIT_SENTENCES(A1).

Expected Result: The function will return an array of sentences extracted from the provided text.

Identify Prime Numbers in a Range

Sample Data: FIND_PRIMES

2	4	6	8	10	12	14	16	18	20
3	5	7	11	13	17	19	23	29	31
32	33	34	35	36	37	38	39	40	41
42	43	44	45	46	47	48	49	50	51
52	53	54	55	56	57	58	59	60	61

Objective: Create a custom function that identifies which numbers in a given range are prime numbers.

Learning Outcomes:

Practice using conditional logic to identify prime numbers.
Understand the concept of prime checking through loops.

Code Example:

/**

* Identifies prime numbers in a specified range.

* @param {range} data The range of numbers to check.

* @return {array} An array with the prime numbers found.

* @customfunction

function FIND_PRIMES(data) {

function isPrime(num) {

if (num <= 1) return false;

for (let i = 2; i <= Math.sqrt(num); i++) {

if (num % i === 0) return false;

}

return true;

}

const flatData = data.flat();

const primes = flatData.filter(num => typeof num === ‘number’ && isPrime(num));

return primes.length > 0 ? primes.map(prime => [prime]) : “No primes found!”;

}

Steps:

Paste the code into a new project in the Apps Script editor.
Save and return to Google Sheets.
Enter a range of numbers, e.g., A1:A10.
Use the function with =FIND_PRIMES(A1:A10).

Expected Result: This function will return a list of prime numbers found in the provided range.

2 : Analyzing Data with Advanced Functions

Explore more complex functions that help analyze and interpret data. This chapter covers how to track trends, summarize data, and calculate statistical measures like compound interest and standard deviation. Through practical examples, you will learn to write functions that not only perform calculations but also provide insights into data patterns.

Analyze Yearly Expense Trends

Objective: Write a function to analyze the trend in yearly expenses for various categories to determine if they are increasing, decreasing, or remaining stable.

Sample Data:

Year	Utilities	Marketing	Research & Development
2021	5000	7000	10000
2022	5200	6800	9500
2023	5400	7200	12000

Learning Outcomes:

Learn to compare sequential data to identify trends.
Practice handling and analyzing data across multiple categories.

Code Example:

/**

* Analyzes yearly expense trends for multiple categories.

* @param {range} years The range containing years.

* @param {range} categories The range containing expense data for each category across multiple years.

* @return {array|string} An array summarizing the trend for each category, or an error message if data is invalid.

* @customfunction

function ANALYZE_YEARLY_EXPENSE_TRENDS(years, categories) {

const results = [];

const numYears = years.length;

const numCategories = categories[0].length; // Assuming uniform data

for (let j = 0; j < numCategories; j++) {

let trend = ‘Stable’;

let increasing = 0;

let decreasing = 0;

for (let i = 1; i < numYears; i++) {

if (categories[i][j] > categories[i – 1][j]) {

increasing++;

} else if (categories[i][j] < categories[i – 1][j]) {

decreasing++;

}

if (increasing === numYears – 1) {

trend = ‘Increasing’;

} else if (decreasing === numYears – 1) {

trend = ‘Decreasing’;

}

results.push([‘Category ‘ + (j + 1), trend]);

}

return results.length > 0 ? results : “No enough data to determine trends.”;

}

Steps:

Paste the code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter the sample data into a format where each column after the first represents a category and each row a year, from A1:D4.
Use the function like =ANALYZE_YEARLY_EXPENSE_TRENDS(A2:A4, B2:D4).

Expected Result: The function will provide a trend analysis for each category, indicating whether expenses are increasing, decreasing, or remaining stable over the years.

Track Employee Attendance Streaks

Objective: Develop a function to track the longest streak of daily attendance for each employee over a month.

Sample Data:

Employee	Days Attended Consecutively
Alice	23
Bob	15
Carol	30
David	8
Emily	20

Learning Outcomes:

Practice calculating streaks or consecutive occurrences.
Learn to interpret attendance data to assess employee reliability.

Code Example:

/**

* Tracks the longest streak of daily attendance for each employee.

* @param {range} employees The range containing employee names.

* @param {range} streaks The range containing the number of consecutive days each employee attended.

* @return {array|string} An array listing each employee with their longest attendance streak, or an error message if data is invalid.

* @customfunction

function TRACK_ATTENDANCE_STREAKS(employees, streaks) {

const results = [];

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

const employee = employees[i][0];

const streak = streaks[i][0];

results.push([employee, streak + ” days”]);

}

return results.length > 0 ? results : “Invalid or empty data!”;

}

Steps:

Paste the code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter the sample data into columns A1:B6.
Use the function like =TRACK_ATTENDANCE_STREAKS(A2:A6, B2:B6).

Expected Result: The function will list each employee along with their longest consecutive attendance streak, highlighting their consistency.

Summarize Project Budget Utilization

Objective: Create a function to summarize how much of the allocated budget has been utilized by each project.

Sample Data:

Project	Allocated Budget	Spent Amount
Project A	100000	75000
Project B	50000	50000
Project C	75000	55000
Project D	60000	61000
Project E	85000	90000

Learning Outcomes:

Learn to calculate the percentage of budget utilized.
Practice creating financial summaries to aid in budget management.

Code Example:

/**

* Summarizes budget utilization for each project.

* @param {range} projects The range containing project names.

* @param {range} budgets The range containing allocated budgets.

* @param {range} spent The range containing amounts spent.

* @return {array|string} An array listing each project with its budget utilization as a percentage, or an error message if data is invalid.

* @customfunction

function SUMMARIZE_BUDGET_UTILIZATION(projects, budgets, spent) {

const results = [];

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

const project = projects[i][0];

const budget = budgets[i][0];

const expenditure = spent[i][0];

const utilization = (expenditure / budget) * 100;

results.push([project, utilization.toFixed(2) + “%”]);

}

return results.length > 0 ? results : “Invalid or empty data!”;

}

Steps:

Paste the code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter the sample data into columns A1:C6.
Use the function like =SUMMARIZE_BUDGET_UTILIZATION(A2:A6, B2:B6, C2:C6).

Expected Result: The function will provide a summary of budget utilization for each project, indicating the percentage of the allocated budget that has been spent, which is crucial for financial tracking and management.

Determine Staff Availability

Objective: Write a function to determine which days of the week staff members are available based on their schedules.

Sample Data:

Employee	Monday	Tuesday	Wednesday	Thursday	Friday
Alice	Available	Unavailable	Available	Unavailable	Available
Bob	Unavailable	Available	Available	Available	Unavailable
Carol	Available	Available	Unavailable	Available	Available
David	Unavailable	Unavailable	Available	Available	Available
Emily	Available	Available	Available	Unavailable	Unavailable

Learning Outcomes:

Learn to filter and summarize availability data across multiple days.
Practice using logical operations to generate structured availability reports.

Code Example:

/**

* Determines the days of the week each staff member is available.

* @param {range} employees The range containing employee names.

* @param {range} days The range containing availability data for Monday through Friday.

* @return {array|string} An array listing each employee with their available days, or an error message if data is invalid.

* @customfunction

function DETERMINE_STAFF_AVAILABILITY(employees, days) {

const results = [];

const weekdays = [‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’];

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

const availableDays = [];

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

if (days[i][j] === ‘Available’) {

availableDays.push(weekdays[j]);

}

results.push([employees[i][0], availableDays.join(“, “)]);

}

return results.length > 0 ? results : “Invalid or empty data!”;

}

Steps:

Paste the code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter the sample data into a grid format from A1:F6.
Use the function like =DETERMINE_STAFF_AVAILABILITY(A2:A6, B2:F6).

Expected Result: The function will list each employee along with the days they are available to work, helping in scheduling and workforce management.

Generate Random Alphanumeric String

RANDOM_ALPHANUMERIC

(No input data needed, this function generates its own data.)

Objective: Create a custom function that generates a random alphanumeric string of a specified length.

Learning Outcomes:

Understand how to build custom random string generators.
Practice working with arrays and strings for character selection.

Code Example:

/**

* Generates a random alphanumeric string of a specified length.

* @param {number} length The desired length of the random string.

* @return {string} The generated random alphanumeric string.

* @customfunction

function RANDOM_ALPHANUMERIC(length) {

if (typeof length !== ‘number’ || length <= 0) return “Invalid length”;

const characters = ‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789’;

let result = ”;

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

result += characters.charAt(Math.floor(Math.random() * characters.length));

}

return result;

}

Steps:

Add the code to a new Apps Script project.
Save the changes and return to Google Sheets.
Use the function with =RANDOM_ALPHANUMERIC(10).

Expected Result: The function will return a randomly generated alphanumeric string of 10 characters.

Convert List to Sentence

LIST_TO_SENTENCE

Apple

Banana

Cherry

Date

Elderberry

Objective: Create a function that converts a range of individual words into a single grammatically correct sentence.

Learning Outcomes:

Practice string manipulation and array handling to concatenate words.
Learn conditional logic to handle different list lengths.

Code Example:

/**

* Converts a range of words into a grammatically correct sentence.

* @param {range} words The range of words to combine into a sentence.

* @return {string} The formatted sentence.

* @customfunction

function LIST_TO_SENTENCE(words) {

const flatWords = words.flat().filter(word => typeof word === ‘string’ && word.trim() !== ”);

const count = flatWords.length;

if (count === 0) return “No words provided!”;

if (count === 1) return flatWords[0] + ‘.’;

if (count === 2) return flatWords.join(‘ and ‘) + ‘.’;

return flatWords.slice(0, count – 1).join(‘, ‘) + ‘, and ‘ + flatWords[count – 1] + ‘.’;

}

Steps:

Add this code to a new Apps Script project.
Save and return to Google Sheets.
Enter a list of words in a column, e.g., A1:A5.
Use the function like =LIST_TO_SENTENCE(A1:A5).

Expected Result: The function will return a sentence with proper grammatical structure based on the input list length.

Calculate Compound Interest

COMPOUND_INTEREST

Principal	Rate	Times Compounded	Years
1000	5	4	10

Objective: Write a function to calculate compound interest based on principal, rate, and time period.

Learning Outcomes:

Understand the formula for compound interest calculation.
Practice using mathematical functions to apply exponential calculations.

Code Example:

/**

* Calculates the compound interest based on principal, rate, and time period.

* @param {number} principal The initial amount of money.

* @param {number} rate The annual interest rate as a percentage.

* @param {number} timesCompounded The number of times interest is compounded per year.

* @param {number} years The number of years the money is invested or borrowed for.

* @return {number} The total amount accumulated after n years, including interest.

* @customfunction

function COMPOUND_INTEREST(principal, rate, timesCompounded, years) {

const effectiveRate = rate / 100;

const accumulatedAmount = principal * Math.pow(1 + effectiveRate / timesCompounded, timesCompounded * years);

return accumulatedAmount.toFixed(2);

}

Steps:

Paste the code into a new Apps Script project.
Save the script and return to Google Sheets.
Use the custom function with =COMPOUND_INTEREST(1000, 5, 4, 10).

Expected Result: The function will return the accumulated amount after 10 years, assuming an annual interest rate of 5% and quarterly compounding.

Convert Date to Day of the Week

DAY_OF_WEEK

2024-05-22

2024-12-25

2025-01-01

2025-07-04

2026-11-01

Objective: Write a function that converts a date to its corresponding day of the week.

Learning Outcomes:

Learn how to work with date objects in JavaScript.
Practice using methods to extract date components and identify the day.

Code Example:

/**

* Converts a date to the corresponding day of the week.

* @param {date} dateValue The date to convert.

* @return {string} The name of the day of the week.

* @customfunction

function DAY_OF_WEEK(dateValue) {

if (!(dateValue instanceof Date)) return “Invalid date”;

const days = [‘Sunday’, ‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’, ‘Saturday’];

const dayIndex = dateValue.getDay();

return days[dayIndex];

}

Steps:

Add this code to a new Apps Script project.
Save the changes and return to Google Sheets.
Enter a date in a cell, e.g., A1.
Use the function like =DAY_OF_WEEK(A1).

Expected Result: The function will return the name of the day of the week corresponding to the given date.

Calculate Weighted Average

Objective: Write a function that calculates the weighted average of a set of values, given their corresponding weights.

Sample Data:

Value	Weight
80	2
95	1
70	3
85	4

Learning Outcomes:

Understand the concept of calculating weighted averages.
Learn how to use array manipulation and arithmetic operations.

Code Example:

/**

* Calculates the weighted average of a range of values with corresponding weights.

* @param {range} values The range of values.

* @param {range} weights The range of weights.

* @return {number|string} The calculated weighted average or an error message if input data is invalid.

* @customfunction

function WEIGHTED_AVERAGE(values, weights) {

if (values.length !== weights.length || values.length === 0) return “Invalid data!”;

let weightedSum = 0;

let totalWeight = 0;

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

if (typeof values[i][0] === ‘number’ && typeof weights[i][0] === ‘number’) {

weightedSum += values[i][0] * weights[i][0];

totalWeight += weights[i][0];

}

return totalWeight > 0 ? (weightedSum / totalWeight).toFixed(2) : “Invalid weights!”;

}

Steps:

Paste the code into a new Apps Script project.
Save changes and return to Google Sheets.
Enter the sample data above into cells A1:B5.
Use the function with =WEIGHTED_AVERAGE(A2:A5, B2:B5).

Expected Result: The function will return the calculated weighted average of the values based on their weights.

Count Words of Specific Length

Objective: Write a function that counts words of a specified length within a range of text.

Sample Data:

Text

This is a sample sentence

Another example of text

Words of varying length here

Texts with different lengths

Learning Outcomes:

Practice string manipulation and regular expressions.
Learn to count occurrences based on specific criteria.

Code Example:

/**

* Counts words of a specific length in a given range of text.

* @param {range} data The range containing text to search.

* @param {number} wordLength The desired word length to count.

* @return {number|string} The count of words or an error message if input data is invalid.

* @customfunction

function COUNT_WORDS_BY_LENGTH(data, wordLength) {

if (typeof wordLength !== ‘number’ || wordLength <= 0) return “Invalid word length!”;

let wordCount = 0;

data.flat().forEach(text => {

if (typeof text === ‘string’) {

const matches = text.match(new RegExp(`\\b\\w{${wordLength}}\\b`, ‘g’));

wordCount += matches ? matches.length : 0;

}

});

return wordCount;

}

Steps:

Paste the code into a new Apps Script project.
Save changes and return to Google Sheets.
Enter the sample data into column A1:A5.
Use the function like =COUNT_WORDS_BY_LENGTH(A1:A5, 5).

Expected Result: The function will return the count of words with a length of 5 in the given range.

Calculate Percentage Distribution of Categories

Objective: Write a function that calculates the percentage distribution of different categories from a list.

Sample Data:

Fruit

Vegetable

Fruit

Dairy

Fruit

Dairy

Meat

Vegetable

Learning Outcomes:

Learn how to use JavaScript objects for mapping and counting.
Practice arithmetic operations to calculate percentages.

Code Example:

/**

* Calculates the percentage distribution of categories in a given range.

* @param {range} data The range containing the list of categories.

* @return {array|string} An array of categories with their percentage distributions or an error message if input data is invalid.

* @customfunction

function CATEGORY_PERCENTAGES(data) {

const total = data.flat().filter(cat => typeof cat === ‘string’).length;

if (total === 0) return “No categories found!”;

const counts = {};

data.flat().forEach(category => {

if (typeof category === ‘string’) {

counts[category] = (counts[category] || 0) + 1;

}

});

const result = Object.entries(counts).map(([category, count]) => [category, ((count / total) * 100).toFixed(2) + ‘%’]);

return result;

}

Steps:

Paste the code into a new Apps Script project.
Save changes and return to Google Sheets.
Enter the sample data into column A1:A8.
Use the function like =CATEGORY_PERCENTAGES(A1:A8).

Expected Result: The function will return an array with each category and its percentage of the total items.

Find the Mode of a Range

Objective: Create a custom function that finds the mode (most frequently occurring number) in a range of numbers.

Sample Data:

3
7
3
4
7
7
6
4

Learning Outcomes:

Learn to use objects to count occurrences of each value.
Understand how to identify the mode from a dataset.

Code Example:

/**

* Finds the mode (most frequently occurring value) in a range of numbers.

* @param {range} data The range of numbers to analyze.

* @return {number|string} The mode value or an error message if no valid data is found.

* @customfunction

function FIND_MODE(data) {

const numberCounts = {};

const flatData = data.flat().filter(val => typeof val === ‘number’);

if (flatData.length === 0) return “No valid numeric data!”;

flatData.forEach(num => numberCounts[num] = (numberCounts[num] || 0) + 1);

let mode = flatData[0];

let maxCount = numberCounts[mode];

for (let key in numberCounts) {

if (numberCounts[key] > maxCount) {

mode = Number(key);

maxCount = numberCounts[key];

}

return mode;

}

Steps:

Paste this code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter the sample data into column A1:A8.
Use the function like =FIND_MODE(A1:A8).

Expected Result: The function will return the mode of the given range of numbers.

Find the Longest Word in Each Row

Objective: Create a function that finds the longest word in each row from a range of text data.

Sample Data:

This is an interesting exercise

Short sentences count too

Finding the longest word per row

Coding is quite a challenge

Learning Outcomes:

Learn to manipulate text data row-wise.
Understand how to use regular expressions to extract words.

Code Example:

/**

* Finds the longest word in each row of text data.

* @param {range} data The range of text data.

* @return {array|string} An array of longest words or an error message if input data is invalid.

* @customfunction

function LONGEST_WORD_IN_ROWS(data) {

return data.map(row => {

if (typeof row[0] !== ‘string’) return [“Invalid input”];

const words = row[0].match(/\b\w+\b/g) || [];

const longestWord = words.reduce((longest, current) => current.length > longest.length ? current : longest, “”);

return [longestWord || “No words found”];

});

}

Steps:

Paste this code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter the sample data into column A1:A4.
Use the function like =LONGEST_WORD_IN_ROWS(A1:A4).

Expected Result: The function will return an array where each item represents the longest word in its corresponding row.

Check if a Range Contains All Positive Numbers

Objective: Write a function to check if all values in a specified range are positive numbers.

Sample Data:

-7

Learning Outcomes:

Learn conditional logic to validate numeric data.
Understand the concept of positive and negative numbers.

Code Example:

/**

* Checks if all values in the range are positive numbers.

* @param {range} data The range of numbers to check.

* @return {string} “All Positive” or “Contains Non-Positive” depending on the input data.

* @customfunction

function CHECK_ALL_POSITIVE(data) {

const allPositive = data.flat().every(num => typeof num === ‘number’ && num > 0);

return allPositive ? “All Positive” : “Contains Non-Positive”;

}

Steps:

Add this code into a new Apps Script project.
Save and return to Google Sheets.
Enter the sample data into column A1:A6.
Use the function with =CHECK_ALL_POSITIVE(A1:A6).

Expected Result: The function will return “Contains Non-Positive” because there are negative and zero values in the sample data.

Calculate the Standard Deviation of a Range

Objective: Write a function to calculate the standard deviation of a set of numerical data.

Sample Data:

8
6
10
14
9
11
5
13

Learning Outcomes:

Learn to calculate the standard deviation using statistical formulas.
Practice iterating through numerical data arrays and applying arithmetic operations.

Code Example:

/**

* Calculates the standard deviation of a given range of numbers.

* @param {range} data The range of numbers to calculate.

* @return {number|string} The standard deviation or an error message if input data is invalid.

* @customfunction

function STANDARD_DEVIATION(data) {

const numbers = data.flat().filter(val => typeof val === ‘number’);

if (numbers.length === 0) return “No valid numeric data!”;

const mean = numbers.reduce((sum, val) => sum + val, 0) / numbers.length;

const variance = numbers.reduce((sum, val) => sum + Math.pow(val – mean, 2), 0) / numbers.length;

const stdDev = Math.sqrt(variance);

return stdDev.toFixed(2);

}

Steps:

Paste the code into a new Apps Script project.
Save the changes and return to Google Sheets.
Enter the sample data into column A1:A8.
Use the function with =STANDARD_DEVIATION(A1:A8).

Expected Result: The function will return the standard deviation of the values.

First Chapters Book Google Apps Script Sheets Custom Functions 150 Apps Script Code Examples for Sheets

1 : Mastering Basic Custom Functions

Function for Summing Up Specific Ranges

Function to Concatenate and Format Strings

Function to Calculate Days Until a Future Date

Calculate and Categorize Average

Generate Unique Codes

Extract and Count Words in a Text

Finding the Longest String in a Range

Checking for Duplicate Values

Calculate the Fibonacci Sequence

Convert Celsius to Fahrenheit

Remove Vowels from a String

Sum Only Even Numbers

Calculate the Median of a Range

Count Uppercase and Lowercase Letters

Split Text into Sentences

Identify Prime Numbers in a Range

2 : Analyzing Data with Advanced Functions

Analyze Yearly Expense Trends

Track Employee Attendance Streaks

Summarize Project Budget Utilization

Determine Staff Availability

Generate Random Alphanumeric String

Convert List to Sentence

Calculate Compound Interest

Convert Date to Day of the Week

Calculate Weighted Average

Count Words of Specific Length

Calculate Percentage Distribution of Categories

Find the Mode of a Range

Find the Longest Word in Each Row

Check if a Range Contains All Positive Numbers

Calculate the Standard Deviation of a Range

Related

1 : Mastering Basic Custom Functions

Function for Summing Up Specific Ranges

Function to Concatenate and Format Strings

Function to Calculate Days Until a Future Date

Calculate and Categorize Average

Generate Unique Codes

Extract and Count Words in a Text

Finding the Longest String in a Range

Checking for Duplicate Values

Calculate the Fibonacci Sequence

Convert Celsius to Fahrenheit

Remove Vowels from a String

Sum Only Even Numbers

Calculate the Median of a Range

Count Uppercase and Lowercase Letters

Split Text into Sentences

Identify Prime Numbers in a Range

2 : Analyzing Data with Advanced Functions

Analyze Yearly Expense Trends

Track Employee Attendance Streaks

Summarize Project Budget Utilization

Determine Staff Availability

Generate Random Alphanumeric String

Convert List to Sentence

Calculate Compound Interest

Convert Date to Day of the Week

Calculate Weighted Average

Count Words of Specific Length

Calculate Percentage Distribution of Categories

Find the Mode of a Range

Find the Longest Word in Each Row

Check if a Range Contains All Positive Numbers

Calculate the Standard Deviation of a Range

Share this:

Related