Custom function quickstart

Spark icon

AI-generated Key Takeaways

Google Apps Script can be used to create custom functions for Google Sheets.
This sample demonstrates creating a custom function to calculate and format the sale price of discounted items in US dollars.
The process involves setting up the script in the Apps Script editor and then running the function in a spreadsheet cell.

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 Save icon.

/**
*Calculatesthesalepriceofavalueatagivendiscount.
*ThesalepriceisformattedasUSdollars.
*
*@param{number}inputThevaluetodiscount.
*@param{number}discountThediscounttoapply,suchas.5or50%.
*@returnThesalepriceformattedasUSD.
*@customfunction
*/
functionsalePrice(input, discount){
letprice=input-(input*discount);
letdollarUS=Intl.NumberFormat("en-US",{
style:"currency",
currency:"USD",
});
returndollarUS.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:

Custom function quickstart Stay organized with collections Save and categorize content based on your preferences.