Quickstart: Custom function

You can use Google Apps Script to write a custom function, then use it in Google Sheets just like a built-in function.

The below quickstart sample creates a custom function that calculates the sale price of discounted items. The sale price is formatted as US dollars.

Before you begin

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 it up

  1. Create a new spreadsheet.
  2. From within your new spreadsheet, select the menu item Extensions > Apps Script.
  3. Delete any code in the script editor and paste in the code below.

    /**
     * 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);
    }
    
  4. At the top, click Save .

Try it out

  1. Switch back to your spreadsheet.
  2. 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 function results in a sale price of $80.00.

To continue learning about how to extend Google Sheets with Apps Script, take a look at the following resources: