Chapter 3 Case study: tidying the monthly sales sheet ・ MISSION 13
Mission 13: Create a function you can use in cells (custom function)
Task
Column C contains =WITH_TAX(B2), but the function doesn't exist yet, so it shows #NAME?. Write and save a function that adds 10% tax to the pre-tax amount, rounds down to a whole number and returns it.
For practice, the table goes back to its starting state every time before the code runs (a real spreadsheet doesn't go back. Script changes can sometimes be undone with Ctrl+Z, but not all at once).
File ＋
Code.gs
Chapter 3 ・ MISSION 13
Create a function you can use in cells (custom function)
Column C contains =WITH_TAX(B2), but the function doesn't exist yet, so it shows #NAME?. Write and save a function that adds 10% tax to the pre-tax amount, rounds down to a whole number and returns it.
Reading the code
- @customfunction
- Marks a function for use in cells. It also appears in the suggestions as you type.
- function WITH_TAX(price) {
- When a cell contains =WITH_TAX(B2), the value of B2 goes into price.
- return Math.floor(price * 1.1);
- Multiplies by 1.1, rounds down to a whole number and returns the value to the cell.
Once you write and save the function, cells can use it as =WITH_TAX(B2). The cell's value goes into the argument (price), and the returned value appears in the cell. Even company-specific calculation rules take just one formula once they are a function.
How to get started with GAS and common code are covered in an article.What is GAS? Getting started and common code
The code you write runs in an isolated place inside this page. It can't reach outside the page or other sites.
* This is a simulation of how Google Sheets and Apps Script behave and may differ in some ways from the real thing.
* Google Sheets is a trademark of Google LLC. This site is not affiliated with Google.