Even More Google Sheets Custom Formulas for Productivity

by

How to Add These Custom Formulas to Google Sheets

To recap, here’s how you add custom formulas to your Google Sheets:

  1. Open Google Sheets and select Extensions > Apps Script.
  2. Copy and paste the function code into the script editor.
  3. Save the script, and start using your new custom formulas directly in your Google Sheet.

1. Find Maximum Value

Formula: =FIND_MAX(value1, value2, ...)

This function allows you to find the maximum value from a list of numbers. It can be handy when you need to quickly identify the highest value in a set of data points.

function FIND_MAX(...values) {
  return Math.max(...values);
}

Usage example: Enter =FIND_MAX(10, 20, 5, 30) and you’ll get 30.

Explanation: The Math.max() function returns the largest value from the list of numbers provided. By using the spread operator (...), this function can accept any number of arguments, making it a versatile tool for your sheets.

2. Generate a Random Number Between Range

Formula: =RANDOM_BETWEEN(min, max)

Want to generate a random number between two values? This function is perfect for that.

function RANDOM_BETWEEN(min, max) {
  return Math.floor(Math.random() * (max - min + 1)) + min;
}

Usage example: Enter =RANDOM_BETWEEN(1, 100) to get a random number between 1 and 100.

Explanation: This function uses Math.random() to generate a number between the specified range. It’s useful for generating random samples, lottery numbers, or test data for your spreadsheets.

3. Count Words in a String

Formula: =COUNT_WORDS(text)

Need to know how many words are in a text string? This custom function can help you with that.

function COUNT_WORDS(str) {
  return str.trim().split(/\s+/).length;
}

Usage example: Enter =COUNT_WORDS("Hello world, how are you?") and you’ll get 5.

Explanation: The function splits the text based on whitespace and returns the number of resulting words. It also uses .trim() to remove any leading or trailing spaces, ensuring accurate word counts.

4. Convert Minutes to Hours and Minutes

Formula: =MINUTES_TO_HOURS(minutes)

This function converts a total number of minutes into hours and minutes. It’s a helpful tool when working with time-based calculations, such as time tracking.

function MINUTES_TO_HOURS(minutes) {
  const hrs = Math.floor(minutes / 60);
  const mins = minutes % 60;
  return `${hrs} hour(s) and ${mins} minute(s)`;
}

Usage example: Enter =MINUTES_TO_HOURS(150) to get 2 hour(s) and 30 minute(s).

Explanation: The function takes the total minutes as input, calculates the number of hours by dividing by 60, and calculates the remaining minutes using the modulus operator (%). The result is a nicely formatted string displaying hours and minutes.

5. Convert String to Lowercase

Formula: =TO_LOWERCASE(text)

This function is a simple but effective way to convert any string to lowercase, ensuring uniformity across your data.

function TO_LOWERCASE(str) {
  return str.toLowerCase();
}

Usage example: Enter =TO_LOWERCASE("HELLO WORLD") and you’ll get hello world.

Explanation: The function simply calls the .toLowerCase() method on the provided string, which converts every character to its lowercase version. This is especially useful for standardizing text data, making it easier to work with.

Wrapping Up

These five custom functions are designed to help you with tasks ranging from word counting to unit conversion. By using these simple yet effective functions, you can make your data handling smoother and save precious time when working in Google Sheets.

If you’ve enjoyed this series so far, I encourage you to try implementing these functions and see how they can enhance your workflow. Custom formulas might seem intimidating at first, but once you get the hang of Apps Script, you’ll realize just how much potential there is to take your spreadsheets to the next level.