Understanding Custom Formulas in Google Apps Script — What They Are and How to Use Them

Have you ever wished Google Sheets could do more than just standard calculations like SUM, AVERAGE, or VLOOKUP? Do you often find yourself repeating the same complex logic in multiple places? If so, it’s time to unlock the power of Custom Formulas in Google Apps Script.

Custom formulas allow you to create your own functions, just like Google Sheets’ built-in functions. With Apps Script, you can write unique, reusable formulas that perform any task you want — from simple calculations to more advanced logic.

In this guide, you’ll learn:

What Google Apps Script custom formulas are
Why you should use them
How to create your first custom function
Real-world examples of custom functions
Tips and best practices for using custom formulas effectively

🤔 What Are Custom Formulas in Google Apps Script?

Custom formulas are user-defined functions that you create using Google Apps Script. They work like any other function in Google Sheets (e.g., =SUM(A1:A5)), but instead, you define what the function does.

Once you create a custom formula, you can use it directly in your spreadsheet, like this:

=MY_CUSTOM_FUNCTION(A1, B1)

These custom formulas can do anything from calculating numbers to performing string manipulations, returning true/false values, or even calling external APIs.

🚀 Why Use Custom Formulas?

Custom formulas have several benefits:

Personalized Functionality: Create custom logic that isn’t available in Google Sheets by default.
Reusability: Write the logic once and reuse it multiple times in your sheet.
Increased Productivity: Avoid repetitive tasks by turning them into reusable formulas.
Complex Logic Made Simple: Turn long, complicated spreadsheet logic into a simple custom formula.
Call External APIs: You can even call external APIs and pull real-time data into your sheet.

🛠️ How to Create a Custom Formula

Here’s a step-by-step guide on how to create your first custom formula.

Step 1: Open Apps Script

Open a Google Sheet.
Click on Extensions → Apps Script.

Step 2: Write Your Custom Function

In the script editor, remove any existing code.
Write your custom function. For example, here’s a simple function that adds two numbers:

function ADD_NUMBERS(a, b) {
  return a + b;
}

Step 3: Save and Close the Script Editor

Click the Save icon (or press Ctrl + S).
Close the Apps Script editor tab.

Step 4: Use the Custom Formula in Your Sheet

In any cell of your spreadsheet, type the following formula:=ADD_NUMBERS(5, 3) This will return 8 (since 5 + 3 = 8).
You can also reference cells, like this:e=ADD_NUMBERS(A1, B1)

📘 5 Real-World Examples of Custom Formulas

1️⃣ Addition of Two Numbers

Code:

function ADD_NUMBERS(a, b) {
  return a + b;
}

How to Use It:

=ADD_NUMBERS(A1, B1)

This formula takes two numbers from A1 and B1 and returns their sum.

2️⃣ Get the Maximum of Two Numbers

Code:

function MAX_OF_TWO(a, b) {
  return Math.max(a, b);
}

How to Use It:

=MAX_OF_TWO(A1, B1)

This function returns the largest of the two numbers.

3️⃣ Custom Function to Calculate the Area of a Circle

Code:

function CIRCLE_AREA(radius) {
  if (radius < 0) return 'Radius cannot be negative';
  return Math.PI * radius * radius;
}

How to Use It:

=CIRCLE_AREA(5)

This function calculates the area of a circle given a radius (5 in this case).

4️⃣ Check if a Number is Prime

Code:

function IS_PRIME(n) {
  if (n <= 1) return false;
  for (let i = 2; i <= Math.sqrt(n); i++) {
    if (n % i === 0) return false;
  }
  return true;
}

How to Use It:

=IS_PRIME(A1)

This function returns TRUE if the value in A1 is a prime number and FALSE otherwise.

5️⃣ Custom Greeting Based on Time of Day

Code:

function GREETING(name) {
  const hour = new Date().getHours();
  if (hour < 12) return `Good Morning, ${name}!`;
  if (hour < 18) return `Good Afternoon, ${name}!`;
  return `Good Evening, ${name}!`;
}

How to Use It:

=GREETING("John")

This function returns a friendly greeting like “Good Morning, John!” depending on the current time of day.

🎓 Best Practices for Custom Formulas

To ensure that your custom formulas are effective, follow these best practices:

Name Functions Clearly
- Use clear, descriptive names like CALCULATE_AREA instead of vague names like MY_FUNC.
Error Handling
- Use if statements to check for invalid inputs (e.g., division by zero, negative numbers) and provide error messages.
Comment Your Code
- Add comments to explain what each function does for easier maintenance.
Reuse Functions
- If your functions can be reused, break them into smaller, simpler parts. For example, you can use the IS_PRIME() function as part of other functions.
Test Before Use
- Test your functions with different inputs and edge cases (e.g., what happens if the input is zero, negative, or empty?).
Use Logging for Debugging
- Add Logger.log() inside your function to debug it in the Apps Script editor.

💡 Frequently Asked Questions (FAQs)

1️⃣ How do I use custom formulas in Google Sheets?
Once you’ve written a custom formula in Apps Script, you can call it like any other Sheets formula:

=MY_CUSTOM_FUNCTION(A1, B1)

2️⃣ How do I edit an existing custom function?
Go to Extensions → Apps Script, edit the code, and click Save.

3️⃣ Can I call APIs inside a custom function?
Yes! You can use UrlFetchApp.fetch() to call APIs. For example, you can call weather APIs and return real-time weather data in Sheets.

4️⃣ Can I reference other cells in my custom function?
Yes, you can pass cell references to your custom functions, and they will act on the cell values.

5️⃣ Can I share a Google Sheet with custom formulas?
Yes, but the person you share it with will need access to the Apps Script project. They may need to authorize scripts the first time they run.

🚀 Custom Formula Cheat Sheet

Function Name	Description	Usage
`=ADD_NUMBERS(A1, B1)`	Adds two numbers	`=ADD_NUMBERS(10, 20)`
`=CIRCLE_AREA(5)`	Area of a circle	`=CIRCLE_AREA(5)`
`=IS_PRIME(A1)`	Check if number is prime	`=IS_PRIME(7)`
`=MAX_OF_TWO(A1, B1)`	Returns max of two nums	`=MAX_OF_TWO(10, 20)`
`=GREETING("John")`	Returns a greeting	`=GREETING("John")`

✨ Final Thoughts

Custom formulas are one of the most powerful ways to extend Google Sheets’ functionality. Whether you’re calculating primes, pulling in data from APIs, or creating personalized greetings, the possibilities are endless.

Once you’ve mastered basic custom formulas, you can expand your skills to work with web APIs, automate workflows, and create tools that help you and your team work smarter.

🛠️ Ready to Create Your First Custom Formula?

Start simple with functions like ADD_NUMBERS, then challenge yourself with more advanced logic like APIs, loops, and error handling. Before you know it, you’ll be an Apps Script pro!

25 simple custom functions using Google Apps Script for Google Sheets

Open in Sheet

https://docs.google.com/spreadsheets/d/1thj3ENJI8Y2_UlUfOgb1r3AGMe72JywRWFDrMwJDC44/edit?gid=0#gid=0

📘 Custom Functions Code

// 1. Add two numbers
function ADD_NUMBERS(a, b) {
  return a + b;
}

// 2. Subtract two numbers
function SUBTRACT_NUMBERS(a, b) {
  return a - b;
}

// 3. Multiply two numbers
function MULTIPLY_NUMBERS(a, b) {
  return a * b;
}

// 4. Divide two numbers
function DIVIDE_NUMBERS(a, b) {
  if (b === 0) return 'Division by zero error';
  return a / b;
}

// 5. Calculate remainder of division
function MODULUS_NUMBERS(a, b) {
  if (b === 0) return 'Division by zero error';
  return a % b;
}

// 6. Calculate the power of a to b
function POWER_NUMBERS(a, b) {
  return Math.pow(a, b);
}

// 7. Return the maximum of two numbers
function MAX_NUMBERS(a, b) {
  return Math.max(a, b);
}

// 8. Return the minimum of two numbers
function MIN_NUMBERS(a, b) {
  return Math.min(a, b);
}

// 9. Calculate the absolute difference
function ABSOLUTE_DIFFERENCE(a, b) {
  return Math.abs(a - b);
}

// 10. Return the average of two numbers
function AVERAGE_NUMBERS(a, b) {
  return (a + b) / 2;
}

// 11. Calculate the square root of the sum of two numbers
function SQRT_SUM(a, b) {
  return Math.sqrt(a + b);
}

// 12. Calculate the natural logarithm of the sum of two numbers
function LOG_SUM(a, b) {
  const sum = a + b;
  if (sum <= 0) return 'Log of non-positive number';
  return Math.log(sum);
}

// 13. Return true if a is greater than b
function IS_GREATER(a, b) {
  return a > b;
}

// 14. Return true if a is less than b
function IS_LESS(a, b) {
  return a < b;
}

// 15. Return the larger of a or b multiplied by 2
function DOUBLE_LARGER(a, b) {
  return Math.max(a, b) * 2;
}

// 16. Return the smaller of a or b divided by 2
function HALF_SMALLER(a, b) {
  return Math.min(a, b) / 2;
}

// 17. Calculate the geometric mean of a and b
function GEOMETRIC_MEAN(a, b) {
  if (a < 0 || b < 0) return 'Cannot compute geometric mean of negative numbers';
  return Math.sqrt(a * b);
}

// 18. Return the hypotenuse for sides a and b
function HYPOTENUSE(a, b) {
  return Math.sqrt(a * a + b * b);
}

// 19. Return the sum of the squares of a and b
function SUM_OF_SQUARES(a, b) {
  return a * a + b * b;
}

// 20. Calculate the ceiling of the sum of two numbers
function CEIL_SUM(a, b) {
  return Math.ceil(a + b);
}

// 21. Calculate the floor of the difference of two numbers
function FLOOR_DIFFERENCE(a, b) {
  return Math.floor(a - b);
}

// 22. Return true if a and b are both even
function BOTH_EVEN(a, b) {
  return a % 2 === 0 && b % 2 === 0;
}

// 23. Return true if a and b are both odd
function BOTH_ODD(a, b) {
  return a % 2 !== 0 && b % 2 !== 0;
}

// 24. Calculate the distance on a 2D plane between points (0,0) and (a,b)
function DISTANCE_FROM_ORIGIN(a, b) {
  return Math.sqrt(a * a + b * b);
}

// 25. Return the result of a raised to the power of (b modulo 3)
function POWER_WITH_MOD(a, b) {
  return Math.pow(a, b % 3);
}

📘 How to Apply Custom Functions in Google Sheets

Once the Apps Script is saved, head back to your Google Sheet.
Use the functions in your cells as =FUNCTION_NAME(A1, B1).
For example, if you want to add numbers in cells A1 and B1, type:=ADD_NUMBERS(A1, B1)

📘 Example Table

A	B	C
5	3	`=ADD_NUMBERS(A2, B2)`
8	4	`=SUBTRACT_NUMBERS(A3, B3)`
12	2	`=MULTIPLY_NUMBERS(A4, B4)`
7	2	`=DIVIDE_NUMBERS(A5, B5)`
9	4	`=MODULUS_NUMBERS(A6, B6)`
3	3	`=POWER_NUMBERS(A7, B7)`
15	25	`=MAX_NUMBERS(A8, B8)`
15	25	`=MIN_NUMBERS(A9, B9)`
10	6	`=ABSOLUTE_DIFFERENCE(A10, B10)`
4	2	`=AVERAGE_NUMBERS(A11, B11)`

📘 Example Table with Functions Applied

A	B	C	D	E
5	3	=ADD_NUMBERS(A2, B2)	=SUBTRACT_NUMBERS(A2, B2)	=MULTIPLY_NUMBERS(A2, B2)
8	4	=ADD_NUMBERS(A3, B3)	=SUBTRACT_NUMBERS(A3, B3)	=MULTIPLY_NUMBERS(A3, B3)
12	2	=ADD_NUMBERS(A4, B4)	=SUBTRACT_NUMBERS(A4, B4)	=MULTIPLY_NUMBERS(A4, B4)
7	2	=ADD_NUMBERS(A5, B5)	=SUBTRACT_NUMBERS(A5, B5)	=MULTIPLY_NUMBERS(A5, B5)

📘 Summary of Custom Functions

Function Name	Description
ADD_NUMBERS	Add two numbers
SUBTRACT_NUMBERS	Subtract two numbers
MULTIPLY_NUMBERS	Multiply two numbers
DIVIDE_NUMBERS	Divide two numbers
MODULUS_NUMBERS	Get the remainder of division
POWER_NUMBERS	Power of a to b
MAX_NUMBERS	Maximum of two numbers
MIN_NUMBERS	Minimum of two numbers
ABSOLUTE_DIFFERENCE	Absolute difference
AVERAGE_NUMBERS	Average of two numbers
SQRT_SUM	Square root of sum
LOG_SUM	Log of sum
IS_GREATER	True if a > b
IS_LESS	True if a < b
DOUBLE_LARGER	Double of larger number
HALF_SMALLER	Half of smaller number
GEOMETRIC_MEAN	Geometric mean of two numbers
HYPOTENUSE	Calculate hypotenuse
SUM_OF_SQUARES	Sum of squares
CEIL_SUM	Ceiling of sum
FLOOR_DIFFERENCE	Floor of difference
BOTH_EVEN	True if both are even
BOTH_ODD	True if both are odd
DISTANCE_FROM_ORIGIN	Distance from origin (0,0)
POWER_WITH_MOD	Power of a to (b % 3)

🤔 What Are Custom Formulas in Google Apps Script?

🚀 Why Use Custom Formulas?

🛠️ How to Create a Custom Formula

Step 1: Open Apps Script

Step 2: Write Your Custom Function

Step 3: Save and Close the Script Editor

Step 4: Use the Custom Formula in Your Sheet

📘 5 Real-World Examples of Custom Formulas

1️⃣ Addition of Two Numbers

2️⃣ Get the Maximum of Two Numbers

3️⃣ Custom Function to Calculate the Area of a Circle

4️⃣ Check if a Number is Prime

5️⃣ Custom Greeting Based on Time of Day

🎓 Best Practices for Custom Formulas

💡 Frequently Asked Questions (FAQs)

🚀 Custom Formula Cheat Sheet

✨ Final Thoughts

🛠️ Ready to Create Your First Custom Formula?

25 simple custom functions using Google Apps Script for Google Sheets

📘 Custom Functions Code

📘 How to Apply Custom Functions in Google Sheets

📘 Example Table

📘 Example Table with Functions Applied

📘 Summary of Custom Functions

Share this:

Related