How To Create A Function In Google Sheets

Last Updated on November 1, 2023 by Jake Sheridan

In this tutorial, you will learn how to create a function in google sheets.

Google Sheets has a large number of built-in functions that make all kinds of calculations quick and easy, however you may find sometimes these functions aren’t enough for your needs. When this happens, you can create custom functions in Google Sheets that perform specific calculations, unit conversions, or fetch live data from the internet. These custom functions can then be used in your Google Sheet just like the built-in functions, although they do tend to calculate slightly slower as a general rule.

If you’re ready to get started with creating your own functions in Google Sheets, keep reading and this tutorial will step you through how to set up an example custom function that doubles the input parameter. Note that while this tutorial itself does not require any previous knowledge of JavaScript, in order to write your own functions you will need some familiarity with JavaScript or at least programming languages in general.

Creating a Custom Function

Custom functions can be as easy or as complex as you want them to be, but for this example we’ll keep it simple with a function that takes a single input parameter and doubles it

Step 1

Open the Script editor by opening the Extensions menu and choosing Apps Script

Step 2

In the Script Editor, delete any existing code and then paste the following function:

function DOUBLE(input) {

return input * 2;

}

Step 3

Click the Save button in the toolbar to save the custom function

Using Custom Functions

Once you’ve followed the steps above to create a custom function, you can use it right away in the Google Sheet it’s tied to. Note that if you want to use the custom function in other Google Sheet files, you will have to follow the steps above in each file individually.

To use the function:

Step 1

Click on a cell in the Google Sheet you added the custom function to and type the equals sign, then type the name of custom function you created exactly as it appears in the script editor, followed by open parentheses: “=DOUBLE(”

Step 2

Type any parameters as needed, close parenthesis, and hit enter. You will most likely see a Loading message in the cell for several seconds

Step 3

Then the function result will appear

Autocomplete

You may have noticed while using the custom function that it was more cumbersome than using a built-in Google Sheets function because it didn’t autocomplete. Fortunately, we can add the autocomplete functionality to this function by following these steps:

Step 1

Back in the script editor, add a line above the custom function and paste the following:

/**

* Multiplies the input value by 2.

*

* @param {number} input The value to multiply.

* @return The input multiplied by 2.

* @customfunction

*/

Step 2

The @customfunction tag tells Google Sheets this comment is the autocomplete info for the custom function, while the other lines display the information to display, starting with a short description at the top, and then specifying help text information about the input parameter (@param), and what the function returns (@return).

Step 3

Save the script and return to the spreadsheet. Now when you start typing the name of the custom function, you will get help text just like you would for a built-in function

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to create a function in google sheets. Want more? Check out all the Google Sheets Tutorials.