Google Sheets Custom Formulas: Comprehensive Guide

by

Google Sheets Custom Formulas: Comprehensive Guide

Google Sheets Custom Formulas_ Comprehensive GuideDownload

Custom formulas in Google Sheets are created using Google Apps Script, which allows you to build powerful, reusable functions for advanced spreadsheet operations. This guide explains how to create, use, and troubleshoot custom formulas in Google Sheets, with detailed examples, exercises, and quiz questions.

What are Custom Formulas?

Custom formulas are user-defined functions that extend Google Sheets’ built-in functions. They are written in Google Apps Script and can be used directly in your spreadsheet like built-in formulas such as SUM or AVERAGE.

How to Create a Custom Formula

  1. Open the Script Editor:
    • In Google Sheets, click Extensions > Apps Script.
  2. Write Your Custom Function:
    • Functions must start with function and should return a value.
  3. Save and Test:
    • Save your script and use the custom function in your sheet.

Basic Custom Formula Example

Custom Formula: Add Two Numbers

Code:

function ADD_TWO_NUMBERS(a, b) {

  return a + b;

}

Usage:

  • In your sheet, enter =ADD_TWO_NUMBERS(10, 20) and the result will be 30.

Explanation:

  • a and b are inputs (arguments) provided in the formula.
  • The function returns their sum.

Advanced Custom Formula Examples

Example 1: Generate a Full Name

Code:

function CONCATENATE_NAME(firstName, lastName) {

  return firstName + ” ” + lastName;

}

Usage:

  • Enter =CONCATENATE_NAME(“Alice”, “Smith”) in a cell.
  • Output: Alice Smith

Explanation:

  • Combines two strings (first and last name) with a space.

Example 2: Calculate Percentage

Code:

function PERCENTAGE(part, total) {

  if (total === 0) return “Error: Division by zero”;

  return (part / total) * 100;

}

Usage:

  • Enter =PERCENTAGE(50, 200) in a cell.
  • Output: 25

Explanation:

  • Divides the part by the total and multiplies by 100 to return the percentage.

Example 3: Fetch Current Exchange Rate

Code:

function EXCHANGE_RATE(fromCurrency, toCurrency) {

  const url = `https://api.exchangerate-api.com/v4/latest/${fromCurrency}`;

  const response = UrlFetchApp.fetch(url);

  const data = JSON.parse(response.getContentText());

  return data.rates[toCurrency];

}

Usage:

  • Enter =EXCHANGE_RATE(“USD”, “EUR”) in a cell.
  • Output: Current exchange rate from USD to EUR.

Explanation:

  • Fetches real-time data from an external API using UrlFetchApp.

Example 4: Calculate Workdays Between Two Dates

Code:

function WORKDAYS(startDate, endDate) {

  const start = new Date(startDate);

  const end = new Date(endDate);

  let count = 0;

  for (let d = start; d <= end; d.setDate(d.getDate() + 1)) {

    const day = d.getDay();

    if (day !== 0 && day !== 6) {

      count++;

    }

  }

  return count;

}

Usage:

  • Enter =WORKDAYS(“2024-11-01”, “2024-11-15”) in a cell.
  • Output: Number of workdays between the two dates.

Example 5: Highlight Duplicate Values in a Column

Code:

function HIGHLIGHT_DUPLICATES(range) {

  const values = range.flat();

  const duplicates = values.filter((value, index, self) => value && self.indexOf(value) !== index);

  return values.map(value => (duplicates.includes(value) ? “Duplicate” : “Unique”));

}

Usage:

  • Select a range of cells and enter =HIGHLIGHT_DUPLICATES(A1:A10).
  • Output: Marks duplicates as “Duplicate”.

Exercises

Exercise 1: Custom Formula to Calculate BMI

  • Write a custom function BMI that calculates BMI using the formula: BMI = weight (kg) / height^2 (m^2).

Solution:

function BMI(weight, height) {

  if (height <= 0) return “Error: Invalid height”;

  return weight / (height * height);

}

Test:

  • Enter =BMI(70, 1.75) in a cell.
  • Output: 22.86

Exercise 2: Calculate Compound Interest

  • Write a custom function COMPOUND_INTEREST to calculate compound interest: A = P(1 + r/n)^(nt)

Solution:

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

  return principal * Math.pow(1 + rate / timesCompounded, timesCompounded * years);

}

Test:

  • Enter =COMPOUND_INTEREST(1000, 0.05, 4, 2) in a cell.
  • Output: 1104.94

Exercise 3: Fetch Weather Data

  • Write a custom formula WEATHER to fetch the current weather for a city using an API like OpenWeatherMap.

Solution:

function WEATHER(city) {

  const apiKey = “YOUR_API_KEY”;

  const url = `https://api.openweathermap.org/data/2.5/weather?q=${city}&appid=${apiKey}&units=metric`;

  const response = UrlFetchApp.fetch(url);

  const data = JSON.parse(response.getContentText());

  return `Temperature: ${data.main.temp}°C, Weather: ${data.weather[0].description}`;

}

Test:

  • Enter =WEATHER(“London”) in a cell.
  • Output: Current weather in London.

Troubleshooting Custom Formulas

  1. Authorization Issues:
    • Ensure the script is authorized to access external resources.
  2. Error Messages:
    • Check for invalid inputs or unhandled edge cases.
  3. Performance:
    • Optimize code for large datasets by minimizing loops.

Multiple-Choice Questions

Question 1:

What is the purpose of JSON.parse() in custom formulas?

  1. Convert a JavaScript object to a JSON string.
  2. Convert a JSON string to a JavaScript object.
  3. Parse an array into a JSON object.
  4. Convert a string into a number.

Answer: 2. Convert a JSON string to a JavaScript object.

Question 2:

What does UrlFetchApp.fetch() do in Google Apps Script?

  1. Sends an email.
  2. Fetches data from an external API or URL.
  3. Writes data to a Google Sheet.
  4. Reads a file from Google Drive.

Answer: 2. Fetches data from an external API or URL.

Question 3:

Which function calculates the square of a number in Apps Script?

  1. Math.sqrt()
  2. Math.pow(base, exponent)
  3. Math.square()
  4. Math.abs()

Answer: 2. Math.pow(base, exponent)

Best Practices for Custom Formulas

  1. Keep It Simple: Write reusable and easy-to-read code.
  2. Validate Inputs: Check for invalid or unexpected values.
  3. Optimize for Performance: Avoid unnecessary loops or heavy computations.
  4. Error Handling: Use conditional checks to prevent runtime errors.
  5. Comment Your Code: Explain complex logic for future reference.