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.