Custom function quickstart

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:

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