Custom functions for Google Sheets using Apps Script

If you're familiar with Google Sheets, you've probably used functions such as SUM, AVERAGE, COUNT, etc. in formulas.

Using Apps Script, you can also write your own functions. These are called custom functions. Once you create these functions, you can use them in Sheets just like any other function.

What is a function?

A function is a piece of code that has a name. You can run (or execute) this piece of code elsewhere in your program by using its name. This enables reuse of code. Instead of typing the same code in multiple places, you can define a function in one place and then run it from other places in your program.

Functions in Apps Script have the following structure.

function functionName (inputValues) {
 
   return outputValue;

}

The words function and return are called keywords.

The keyword function tells Apps Script that you are about to define a function. A function can accept input values and it can also return an output value. The return keyword tells Apps Script that you're about to return a value from the function. A function can only return a maximum of one value but you can have functions that don't return any value.

If the function accepts multiple input values, use a comma to separate them.

function functionName (inputValue1, inputValue2, inputValue3) {
   //The input values are processed to generate the output
   return outputValue;

}

Here is a diagram that visualizes what the function is doing.

function functionName (inputValue1, inputValue2, inputValue3) {

}

return outputValue;

There are functions that don't need input values. Similarly, you can have functions that don't return any value. It all depends on what the function does.

A function that does not accept any input value but returns an outputValue.

function functionName () {

   return outputValue;

}

A function that neither accepts input values nor returns any value.

function functionName () {

}

Naming your function

You must follow certain rules while naming your function.

• Function names can only contain letters, numbers, the dollar sign ($) and underscores.

• Function names cannot be a keyword.

• Function names cannot start with a number.

Remember that function names in Apps Script are case-sensitive. So, MYFUNCTION is not the same as myFunction.

Here are some best practices for naming your function.

• Use a descriptive name to help future readers of your code understand what the function does.

• If the name is made up of several words, either use camel case or use underscores to separate the words. If you choose to separate the words using underscores, ensure that the words are in lower case.

Writing your first custom function

You're going to write a function that returns a number that you believe will bring you luck. Let's start by documenting a few details.

• What the function should do: It will return a number.

• Function name: Pick a descriptive name (eg. myLuckyNumber).

• Input values: This function does not need any input values.

• Output value: A number (your lucky number).

I think the number 3 brings me luck so here is a function that returns my lucky number.

function MYLUCKYNUMBER () {
   return 3;
}

Here is a screenshot of the above function in the Apps Script editor.

You can now use this custom function in a Google Sheets formula just like any other function.

You should see your lucky number displayed in the cell where you entered the formula.

Learn more

You can learn more about custom functions in Google Sheets by reading the following tutorials:

• Returning values from custom functions in Google Sheets

• Enable autocomplete for custom functions in Google Sheets using JSDoc

• How to pass cell coordinates to a custom function?

• How to return an error from a custom function?

The following tutorials are examples of custom functions that you can create:

• Star rating system in Google Sheets ★★★★★

• How to convert strings to Proper case in Apps Script?