Custom functions in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a custom function that adds two numbers in Google Sheets.

Google Sheets

function addNumbers(a, b) {
  return a [1] b;
}

Drag options to blanks, or click blank then click option'

A/

B+

C*

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using minus - instead of plus +.

Using multiplication * or division / by mistake.

The plus sign + adds two numbers together in JavaScript, which is used for Google Sheets custom functions.

2fill in blank

medium

Complete the code to return the uppercase version of a text input in a custom function.

Google Sheets

function toUpperCase(text) {
  return text.[1]();
}

Drag options to blanks, or click blank then click option'

Alowercase

Btrim

Ccapitalize

DtoUpperCase

Attempts:

3 left

💡 Hint

Common Mistakes

Using lowercase() which does the opposite.

Using capitalize() which is not a standard JavaScript method.

The toUpperCase() method converts text to all uppercase letters in JavaScript.

3fill in blank

hard

Fix the error in the custom function that should return the square of a number.

Google Sheets

function squareNumber(num) {
  return num [1] 2;
}

Drag options to blanks, or click blank then click option'

A**

B+

C*

D^

Attempts:

3 left

💡 Hint

Common Mistakes

Using ^ which is a bitwise XOR, not exponent.

Using * which multiplies but does not square.

The ** operator in JavaScript means 'to the power of'. So num ** 2 squares the number.

4fill in blank

hard

Fill both blanks to create a custom function that returns the first letter of a text in uppercase.

Google Sheets

function firstLetterUpper(text) {
  return text.[1](0, 1).[2]();
}

Drag options to blanks, or click blank then click option'

Aslice

BtoUpperCase

Csubstring

Dtrim

Attempts:

3 left

💡 Hint

Common Mistakes

Using trim() which removes spaces, not letters.

Using substring but forgetting to convert to uppercase.

slice(0, 1) extracts the first letter, and toUpperCase() converts it to uppercase.

5fill in blank

hard

Fill all three blanks to create a custom function that returns a dictionary with words as keys and their lengths as values, but only for words longer than 3 letters.

Google Sheets

function wordLengths(words) {
  let result = {};
  for (let word of words.flat()) {
    if (word.[1] > [2]) {
      result[word] = word.[3];
    }
  }
  return result;
}

Drag options to blanks, or click blank then click option'

Alength

B3

Dsize

Attempts:

3 left

💡 Hint

Common Mistakes

Using size which is not a string property in JavaScript.

Comparing length to a string '3' instead of number 3.

word.length gives the length of the word. We check if it's greater than 3, then store the length in the result.

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