You can use Google Apps Script to write a custom function, then use it in
Google Sheets just like a built-in function.
The following quickstart sample creates a custom function that calculates the
sale price of discounted items. The sale price is formatted as US dollars.
Objectives
- Set up the script.
- Run the script.
Prerequisites
To use this sample, you need the following prerequisites:
- A Google Account (Google Workspace accounts might
require administrator approval).
- A web browser with access to the internet.
Set up the script
- Create a
new
spreadsheet
.
- From within your new spreadsheet, select the menu item
Extensions
>
Apps Script
.
Delete any code in the script editor and paste in the code below. Then
click Save
.
/**
* Calculates the sale price of a value at a given discount.
* The sale price is formatted as US dollars.
*
* @param {number} input The value to discount.
* @param {number} discount The discount to apply, such as .5 or 50%.
* @return The sale price formatted as USD.
* @customfunction
*/
function salePrice(input, discount) {
let price = input - (input * discount);
let dollarUS = Intl.NumberFormat("en-US", {
style: "currency",
currency: "USD",
});
return dollarUS.format(price);
}
Run the script
- Switch back to your spreadsheet.
- In a cell, enter
=salePrice(100,.2)
. The first parameter represents the
original price and the second parameter represents the discount percentage.
If you're in a location that uses decimal commas, you might need to enter
=salePrice(100;0,2)
instead.
The formula that you enter in the cell runs the function in the
script you created in the previous section. The function results in a sale
price of
$80.00
.
Next steps
To continue learning about how to extend Sheets with
Apps Script, take a
look at the following resources: