In this article:

Google Sheets Custom Function (The Ultimate Guide in 2024)

What is Google Sheets Custom Function?

Google Sheets custom functions are user-defined functions created using Google Apps Script, a JavaScript-based language. These functions allow you to extend the functionality of Google Sheets beyond its standard set of built-in functions (like SUM, AVERAGE, etc.).

How to Create a Custom Function in Google Sheets

Follow the steps below to create a custom function in Google Sheets. We will specifically focus on a function that adds two numbers together and we will apply this function to a simple dataset to illustrate its practical use.

1. Prepare Your Dataset in Google Sheets

Open your Google Sheets document. Create two columns labeled "Number 1" and "Number 2". Fill these columns with numbers. For example, in rows 1 to 5, put numbers 1 to 5 in "Number 1" and 6 to 10 in "Number 2".

google sheets custom function

2. Open Script Editor and Name Your Project

Click on "Extensions" in the menu bar and select "Apps Script".

custom function google sheets

Name your project in the Apps Script editor, like "AddNumbersFunction".

create custom function in google sheets

3. Write and Save Your Custom Function

In the script editor, write the function:

function addNumbers(number1, number2) {

  return number1 + number2;

}

Save your script by clicking the disk icon or pressing Ctrl + S (Cmd + S on Mac).

4. Use the Custom Function with Your Dataset

Go back to your Google Sheets tab. In a new column labeled "Sum", use the custom function to add numbers from "Number 1" and "Number 2". In the first row of "Sum", type =addNumbers(A2, B2) assuming A2 and B2 are the first cells of your number columns.

5. Autofill the Function for Other Rows

After entering the function in the first row of the "Sum" column, drag the fill handle down to fill the rest of the cells in the column. This applies the function to the rest of the rows.

6. Review the Results

The "Sum" column will now show the results of your custom function for each row. It displays the sum of the numbers from "Number 1" and "Number 2".

Get Custom Functions from Google Workspace Marketplace

Custom functions from the Google Workspace Marketplace offer specialized functionalities tailored to diverse needs. Follow the steps below to simplify complex tasks and enhance your efficiency.

1. Navigate to the Google Workspace Marketplace via Google Sheets

In your Google Sheets document, click on "Extensions" in the Google Sheets menu bar. Select "Add-ons" and then "Get add-ons" to open the Google Workspace Marketplace.

2. Search for Specific Custom Functions in the Marketplace

Use the search bar in the Google Workspace Marketplace. Type keywords related to the custom function you are looking for.

3. Evaluate and Choose an Add-on from the Search Results

Review the search results for a suitable add-on. Check its description and user reviews for suitability and reliability.

4. Install Your Chosen Add-on to Google Sheets

Click on the add-on you have chosen. In the pop-up window, click the "Install" button and follow the instructions to add it to your Google Sheets.

5. Approve the Required Permissions for the Add-on

During the installation process, the add-on will request permissions to access parts of your Google account. Review and accept these permissions to continue with the installation.

6. Locate and Access the Installed Custom Functions in Google Sheets

After installation, find the add-on's custom functions available in Google Sheets.

Access them through the "Extensions" menu or directly in your spreadsheet.

7. Implement the New Custom Functions in Your Spreadsheet Tasks

You can follow the steps in the previous section to utilize the installed custom functions in your spreadsheet. Enter them into cells to perform specific calculations or data management tasks. 

We hope that you now have a better understanding of what a Google Sheets custom function is and how to create a custom function in Google Sheets. If you enjoyed this article, you might also like our article on how to set up the FORECAST function in Google Sheets.

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!