Page Summary

outlined_flag

• 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 inGoogle Sheets just like a built-in function.

The following quickstart sample creates a custom function that calculates thesale 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 mightrequire administrator approval).
• A web browser with access to the internet.

Set up the script

1. Create anewspreadsheet.
2. From within your new spreadsheet, select the menu itemExtensions>Apps Script.

3. Delete any code in the script editor and paste in the code below. Thenclick Save.

   /***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

1. Switch back to your spreadsheet.
2. In a cell, enter=salePrice(100,.2). The first parameter represents theoriginal 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 thescript you created in the previous section. The function results in a saleprice of$80.00.

Next steps

To continue learning about how to extend Sheets withApps Script, take alook at the following resources:

• Spreadsheet custom functions
• Custom menus in Google Workspace
• Extend Google Sheets