Custom functions in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a custom function in Google Sheets?

A custom function is a special formula you create yourself using Google Apps Script to do tasks that built-in functions can't do.

beginner

How do you start writing a custom function in Google Sheets?

Open the Extensions menu, choose Apps Script, then write a JavaScript function that starts with the keyword function and returns a value.

beginner

What must a custom function always do to work correctly in a cell?

It must return a value. The returned value is what you see in the cell where you use the function.

intermediate

Can custom functions in Google Sheets change other cells or the spreadsheet structure?

No, custom functions can only return values to the cell they are called from. They cannot change other cells or the sheet layout.

beginner

How do you use a custom function once it is created?

Just type its name like a normal formula in a cell, for example =MYFUNCTION(A1), and it will run and show the result.

Where do you write a custom function for Google Sheets?

A Directly in a cell

B In the Extensions > Apps Script editor

C In the Google Docs document

D In the Google Drive settings

Custom functions are written in the Apps Script editor accessed from Extensions > Apps Script.

What must a custom function always do to work properly?

A Return a value

B Change other cells

C Send an email

D Open a popup window

Custom functions must return a value to display in the cell where they are used.

Can a custom function modify other cells in the spreadsheet?

A Yes, always

B Only if you have admin rights

C No, never

D Only on weekends

Custom functions cannot change other cells or the spreadsheet layout.

How do you call a custom function in a sheet cell?

A By typing its name with an equal sign, like =MYFUNC()

B By clicking a button in the toolbar

C By right-clicking the cell

D By opening the script editor

You use custom functions just like built-in ones, starting with = and the function name.

Which language do you use to write custom functions in Google Sheets?

A HTML

B Python

C SQL

D JavaScript

Google Sheets custom functions are written in JavaScript using Google Apps Script.

Explain how to create and use a custom function in Google Sheets.

Think about the steps from writing code to using it in a cell.

What are the limitations of custom functions in Google Sheets?

Focus on what custom functions cannot do compared to scripts.

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