Custom function quickstart

  • Google Apps Script allows you to create custom functions that can be used directly within Google Sheets, similar to built-in functions.

  • This tutorial demonstrates how to build a custom function that calculates and formats sale prices based on provided input and discount values.

  • To use the custom function, simply paste the provided code into the Apps Script editor linked within your Google Sheet and call it using =salePrice(input, discount) in any 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

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

  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:

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年10月13日 UTC.