Custom function quickstart (original) (raw)

Custom function quickstart

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

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

1. Create a new spreadsheet.
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. 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

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 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:

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

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-05-07 UTC.