Book Source Code on GitHub https://github.com/lsvekis/Sheets-Custom-Formulas-Book
Get the Best Selling Book Free for the next 4 days
US https://www.amazon.com/dp/B0D59Q4J1T
CAN https://www.amazon.ca/dp/B0D59Q4J1T
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
1 | 2 | 3 | 4 | 5 | 1 | 2 | 3 | 6 | 7 |
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 | 0 | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 |
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
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
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
2 | 4 | 6 | 8 | 10 | 12 | 14 | 16 | 18 | 20 |
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:
12 |
34 |
-7 |
45 |
0 |
67 |
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.