Custom functions in Google Sheets - Step-by-Step Guide

Introduction

Custom functions let you create your own formulas in Google Sheets. They help you do special calculations or tasks that built-in formulas can't do. This is useful when you want to automate or simplify repeated work.

When you want to calculate a special value that Google Sheets does not have a formula for.

When you need to reuse a complex calculation many times without typing it over and over.

When you want to connect your sheet to external data or services using simple formulas.

When you want to create a formula that takes inputs and returns a custom result.

When you want to make your spreadsheet easier to understand by naming your own formulas.

Steps

Step 1: Open

- Google Sheets document

Your spreadsheet is ready to use and edit

Step 2: Click

- Extensions menu, then select Apps Script

The Apps Script editor opens in a new tab

Step 3: Delete any existing code and type

- Apps Script editor code window

You have a blank script to write your custom function

💡 Start your function with the word function and give it a name without spaces

Step 4: Write a function like: function MYFUNCTION(input) { return input * 2; }

- Apps Script editor code window

Your custom function doubles the input number

Step 5: Click

- Save icon or File > Save

Your script is saved and ready to use

Step 6: Go back to your Google Sheet and type

- Any cell

The custom function runs and shows the result

💡 Use =MYFUNCTION(5) to get 10 as output

Before vs After

Before

Sheet has no formula to double a number easily

After

Typing =MYFUNCTION(5) in a cell shows 10 as the result

Settings Reference

Script editor

📍 Extensions > Apps Script

Create and manage your custom functions

Default: Blank script

Function name

📍 In the Apps Script code

Identify your custom function to use in the sheet

Default: None

Return value

📍 Inside your custom function code

Send the result back to the cell where the function is used

Default: None

Common Mistakes

Naming the function with spaces or special characters

Google Sheets requires function names without spaces or special symbols

Use simple names like MYFUNCTION or DoubleValue

Not returning a value from the function

Without a return statement, the function shows no result in the sheet

Always use return to send the result back, e.g., return input * 2;

Trying to use custom functions that call services requiring authorization

Custom functions cannot run services that need user permission

Keep custom functions simple and avoid calls to restricted services

Summary

Custom functions let you create your own formulas in Google Sheets using Apps Script.

You write the function in the Extensions > Apps Script editor and save it.

Use the function in your sheet by typing =FunctionName(arguments).

Function names must be simple and the function must return a value.

Custom functions cannot use services that need special permissions.

Practice

1. What is a custom function in Google Sheets?

easy

A. A chart type you add to your sheet

B. A built-in formula like SUM or AVERAGE

C. A filter to sort data automatically

D. A formula you create using JavaScript in Google Apps Script

Solution

1. Step 1: Understand what custom functions are

   Custom functions are formulas you write yourself using JavaScript in Google Apps Script.

2. Step 2: Differentiate from built-in features

   Built-in formulas like SUM are pre-made, while custom functions are user-created.

3. Final Answer:

   A formula you create using JavaScript in Google Apps Script -> Option D

4. Quick Check:

   Custom function = user-made JavaScript formula [OK]

Hint: Custom functions are your own formulas written in JavaScript [OK]

Common Mistakes:

• Confusing custom functions with built-in formulas
• Thinking custom functions are chart types
• Believing custom functions are filters

2. Which of the following is the correct way to start defining a custom function in Google Apps Script?

easy

A. function MYFUNCTION() { return 'Hello'; }

B. def MYFUNCTION() { return 'Hello'; }

C. func MYFUNCTION() { return 'Hello'; }

D. function: MYFUNCTION() { return 'Hello'; }

Solution

1. Step 1: Recall JavaScript function syntax

   In Google Apps Script, which uses JavaScript, functions start with the keyword 'function' followed by the name and parentheses.

2. Step 2: Check each option's syntax

   function MYFUNCTION() { return 'Hello'; } uses correct JavaScript syntax. Options B and C use Python or other language syntax. function: MYFUNCTION() { return 'Hello'; } has incorrect punctuation.

3. Final Answer:

   function MYFUNCTION() { return 'Hello'; } -> Option A

4. Quick Check:

   JavaScript function syntax = function MYFUNCTION() { return 'Hello'; } [OK]

Hint: JavaScript functions start with 'function' keyword [OK]

Common Mistakes:

• Using Python or other language syntax
• Adding colons after function name
• Missing parentheses after function name

3. Given this custom function code in Google Apps Script:

function DOUBLE(input) {
  return input * 2;
}

What will be the result of =DOUBLE(5) in a sheet cell?

medium

A. 5

B. DOUBLE(5)

C. 10

D. Error

Solution

1. Step 1: Understand the function logic

   The function takes an input and returns input multiplied by 2.

2. Step 2: Calculate the output for input 5

   5 multiplied by 2 equals 10.

3. Final Answer:

   10 -> Option C

4. Quick Check:

   5 * 2 = 10 [OK]

Hint: Multiply input by 2 to get output [OK]

Common Mistakes:

• Expecting the function name as output
• Thinking it returns input unchanged
• Assuming syntax error without checking code

4. You wrote this custom function:

function ADDTEN(value) {
  return value + 10
}

But when you use =ADDTEN(5) in the sheet, it shows an error. What is the likely problem?

medium

A. Function name must be lowercase

B. Missing closing brace '}'

C. Function lacks parentheses

D. Missing semicolon after return statement

Solution

1. Step 1: Check the function syntax carefully

   The function code is missing the closing brace '}' at the end.

2. Step 2: Understand impact of missing brace

   Without the closing brace, the script is incomplete and causes an error when called.

3. Final Answer:

   Missing closing brace '}' -> Option B

4. Quick Check:

   All functions need matching braces [OK]

Hint: Always close function with a brace '}' [OK]

Common Mistakes:

• Thinking semicolon is mandatory in Apps Script
• Assuming function name case matters
• Overlooking missing braces

5. You want a custom function that takes a range of numbers and returns the sum of only the positive numbers. Which of these custom functions will work correctly?

hard

A. function SUMPOS(range) {
let total = 0;
for (let i = 0; i < range.length; i++) {
if (range[i][0] > 0) total += range[i][0];
}
return total;
}

B. function SUMPOS(range) {
return range.reduce((a,b) => a + b);
}

C. function SUMPOS(range) {
let total = 0;
for (let i in range) {
if (range[i] < 0) total += range[i];
}
return total;
}

D. function SUMPOS(range) {
return Math.sum(range);
}

Solution

1. Step 1: Analyze each function's logic

   function SUMPOS(range) { let total = 0; for (let i = 0; i < range.length; i++) { if (range[i][0] > 0) total += range[i][0]; } return total; } loops through the range and adds only positive numbers. function SUMPOS(range) { return range.reduce((a,b) => a + b); } sums all numbers without filtering. function SUMPOS(range) { let total = 0; for (let i in range) { if (range[i] < 0) total += range[i]; } return total; } adds negative numbers only. function SUMPOS(range) { return Math.sum(range); } uses a non-existent Math.sum function.

2. Step 2: Identify correct filtering and summing

   Only function SUMPOS(range) { let total = 0; for (let i = 0; i < range.length; i++) { if (range[i][0] > 0) total += range[i][0]; } return total; } correctly filters positive numbers and sums them.

3. Final Answer:

   The function that sums only positive numbers -> Option A

4. Quick Check:

   Filter positive numbers, then sum [OK]

Hint: Loop and add only if number > 0 [OK]

Common Mistakes:

• Summing all numbers without filtering
• Adding negative numbers by mistake
• Using non-existent functions like Math.sum