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.).
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.
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".
Click on "Extensions" in the menu bar and select "Apps Script".
Name your project in the Apps Script editor, like "AddNumbersFunction".
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).
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.
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.
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".
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.
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.
Use the search bar in the Google Workspace Marketplace. Type keywords related to the custom function you are looking for.
Review the search results for a suitable add-on. Check its description and user reviews for suitability and reliability.
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.
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.
After installation, find the add-on's custom functions available in Google Sheets.
Access them through the "Extensions" menu or directly in your spreadsheet.
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.
