Custom functions in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of a simple custom function

You created this custom function in Google Sheets:

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

What will be the output in cell A1 if you enter =DOUBLE(5)?

Google Sheets

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

A 5

B 10

C Error: Function not found

D 25

💡 Hint

Think about what doubling 5 means.

The function multiplies the input by 2, so DOUBLE(5) returns 10.

❓ Function Choice

intermediate

Choosing the correct custom function for text manipulation

You want a custom function that takes a text string and returns it in uppercase.
Which of these custom functions will work correctly in Google Sheets?

A function TOUPPER(text) { return text.toUpper(); }

B function TOUPPER(text) { return text.uppercase(); }

C function TOUPPER(text) { return UPPER(text); }

D function TOUPPER(text) { return text.toUpperCase(); }

💡 Hint

JavaScript strings have a method to convert to uppercase.

The correct JavaScript method is toUpperCase(), so option D is correct.

📊 Formula Result

advanced

Output of a custom function with array input

Given this custom function:

function SUMPLUSONE(range) {
  let sum = 0;
  for (let i = 0; i < range.length; i++) {
    sum += range[i][0];
  }
  return sum + 1;
}

What is the output of =SUMPLUSONE(A1:A3) if cells A1=2, A2=3, A3=4?

Google Sheets

function SUMPLUSONE(range) {
  let sum = 0;
  for (let i = 0; i < range.length; i++) {
    sum += range[i][0];
  }
  return sum + 1;
}

A 10

B 9

C Error: Cannot read property '0' of undefined

D 7

💡 Hint

Sum the values 2 + 3 + 4, then add 1.

Sum is 2+3+4=9, plus 1 equals 10.

🎯 Scenario

advanced

Debugging a custom function that returns #NAME!

You wrote this custom function:

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

When you use =CONCATENATE_TWO("Hello", "World") in your sheet, it shows #NAME!.
What is the most likely reason?

Google Sheets

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

A Custom functions must be declared with 'function myFunction()' only.

B The script is missing the <code>function</code> keyword.

C The script file is not saved.

D The function name must be lowercase.

💡 Hint

Think about what happens if the script is not properly saved.

If the script is not saved, the sheet cannot find the function, causing #NAME!.

❓ data_analysis

expert

Analyzing output of a custom function with nested arrays

Consider this custom function:

function FLATTEN_AND_SUM(input) {
  let flat = input.flat();
  return flat.reduce((a, b) => a + b, 0);
}

If you enter =FLATTEN_AND_SUM({1,2;3,4}) in a cell, what is the output?

Google Sheets

function FLATTEN_AND_SUM(input) {
  let flat = input.flat();
  return flat.reduce((a, b) => a + b, 0);
}

A 10

B Error: input.flat is not a function

C 7

D NaN

💡 Hint

The input {1,2;3,4} is [[1,2],[3,4]], flat() gives [1,2,3,4], sum is 10.

input.flat() flattens the 2D array to [1,2,3,4], reduce sums to 10.

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