Custom functions in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a quick way to calculate the sales tax for any given amount directly in Google Sheets using a custom function.

📊 Data: You have a list of sales amounts in a column. The sales tax rate is fixed at 7.5%.

🎯 Deliverable: Create a custom function named SALES_TAX that takes a sales amount as input and returns the tax amount. Then use this function in the sheet to calculate tax for each sale.

Sample Data

Sale ID	Amount
1	100
2	250
3	75
4	400
5	150

1

Step 1: Open the Google Sheets script editor to write a custom function.

In Google Sheets, go to Extensions > Apps Script.

Expected Result

A new script editor window opens where you can write code.

2

Step 2: Write the custom function named SALES_TAX that calculates 7.5% tax on the input amount.

function SALES_TAX(amount) { return amount * 0.075; }

Expected Result

The function is saved and ready to use in the sheet.

3

Step 3: Use the custom function SALES_TAX in the sheet to calculate tax for each sale amount.

In cell C2, enter =SALES_TAX(B2) and drag down to C6.

Expected Result

Cells C2 to C6 show the tax amounts: 7.5, 18.75, 5.625, 30, 11.25 respectively.

Final Result

Sale ID | Amount | Tax
--------|--------|-------
1       | 100    | 7.5
2       | 250    | 18.75
3       | 75     | 5.625
4       | 400    | 30
5       | 150    | 11.25

✓The custom function SALES_TAX correctly calculates 7.5% tax for any amount.

✓Using custom functions makes repetitive calculations easy and clean.

✓The tax values update automatically if the sale amounts change.

Bonus Challenge

Modify the SALES_TAX function to accept a second optional parameter for tax rate, defaulting to 7.5% if not provided.

Show Hint

Use a default parameter value in the function like function SALES_TAX(amount, rate = 0.075) { return amount * rate; }

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