Google Sheets has some useful features to handle numerical calculations, look-ups, and string manipulation. If your sheets are more advanced, you might find yourself needing to build complex formulas to get the job done.

If you need to go beyond the scope of what Google Sheets has built-in (like sorting columns in Google Sheets), creating a custom function is the solution. Custom functions are pieces of code that perform actions on your sheet. Once you write them you can give them a name and call them again and again, saving you time.

Let's look at how to make a custom function in Google Sheets, using Google scripts.

Google Sheets Functions

Google Sheets has pretty powerful functions already built-in. An example of built-in functions you may have already used would be Sum or Average:

Custom Function for Google Sheets
Custom Function for Google Sheets

What if you wanted to perform a calculation that isn't included in standard functions? Consider a scenario where you want to add sales tax to the price of an item. Since tax rates vary by location, you would need to build a function with a long list of nested logic. It would look something like this:

        '=if(A2="PA",B2*0.06,if(A2="CA",B2*0.0625,B2*0))'
    

Now imagine if you had to add a dozen or more conditions to this statement for each state. It would get out of control!

A Google Sheets custom function can handle this task. You can put all the complicated code into a script, give it a name, and call the function. No bulky code in your Google Sheet, just a simple function like Sum.

Learning how to create custom functions opens up a brand new world of possibilities. So let's begin.

Create a Google Sheets Custom Function

If you are new to scripting, fear not! It's easy to use. This example will get you started and before long you'll be writing your own scripts.

Custom functions for Google Sheets are written with JavaScript code. If you're an expert in JavaScript you'll feel right at home. If not, it's a simple language that you can learn with a JavaScript cheat sheet.

Open the Script Editor

Open your Google Sheet and select Tools > Script Editor

Script Editor for Custom Function for Google Sheets

Create Your Function

You will want to give your function a useful name. Something simple yet very clear indicating what the function will do.

The inputs you want to use go inside the parentheses as variables. This will be the cell value that you want to work with. If you have more than one cell value you can separate them with a comma.

To use this tax example, you can copy and paste this code into the script editor:

         
function tax(input, location) {
    var rate = 0 ;
    switch (location) {
        case 'PA':
            rate = 0.06;
            break;
        case 'CA':
            rate = 0.0625;
            break;
        default:
            rate = 0;
    }
    return (input * rate);
}

This is a function called tax that will calculate the tax rate on a price based on the location you input in the function. These are hypothetical tax percentages.

Script Editor for Custom Function for Google Sheets

The script will take two cells. One assigned to input the other to location. It will run code to determine which state you would like to calculate for and return the tax amount.

I've only included two locations in this example to give you the idea. You can add more by adding additional lines with locations that you need. That would be good practice to add on once you're finished.

Save Your Function

Select File > Save, give your project a name and click OK.

Save a Custom Function for Google Sheets

Use Your Custom Function

Once you create your function you can use it the same way you would use a built-in function. In the cell where you want your calculation to display, enter an equal sign followed by the name of your function.

For our tax example we are using two inputs. The location which will determine the tax rate and the price of the product that needs tax applied to it:

=tax(B2, A2) where B2 is the price of the product, and A2 is the tax location.

Using a Custom Function for Google Sheets

You can use AutoFill just like Excel to drag and drop your function to all your rows, just as you would a built-in function:

Auto Filling Custom Function for Google Sheets

After you've created your first custom function, you might have several more that you'd like to add. It's easy to add more code to your script. Follow these steps to create a new function the same way and add them underneath your existing code.

Adding to Custom Function for Google Sheets

Here's the result of the new script:

Result of Google Script Custom Function

Reuse Your Functions

Once you put in the effort to create a custom function you can reuse it later. If you create a script to solve a common problem you can get some pretty significant time savings.

Even if you don't need them all in future sheets you should know how to save them just in case you run into a similar problem down the road.

There are a couple of ways to reuse your functions:

  1. Save your functions in a blank sheet and use it as a template by using a copy of it for all future sheets.
  2. Copy your functions from one sheet to the next. This is tedious, but it will work. Open the script editor and copy all the code from one sheet, open the script editor in another sheet, and paste the code there.
  3. Save your sheet to the Google template gallery. Keep in mind this will make your document accessible by others. You will be able to limit this to members of your domain if you have a Google Apps for Work subscription. If you haven't used the template gallery before, it's worth checking out. There are a number of useful Google templates out there to make your life easier.

Document Your Google Script

Google Script supports the JSDoc format, which allows you to add comments to your formula to provide some helpful context.

You've seen these comments in standard functions. When you hover over a function as you write it, it tells you a little bit about what each piece does.

Sheets-JSDOC-exmaple

This isn't required but it's recommended.

Sheets-CustomFunction-Documenting

You can do so many cool things with custom functions in Google Sheets. In fact, creating custom functions is one of the ways to use Google Scripts to make Google Sheets more powerful.

If you want to go down the road learning more about Google Sheets you should check out ways to find great Google Sheets templates. If you want to dig deeper into scripting with Google Sheets you're going to want to master JavaScript. Learn what JavaScript is and the basics of declaring variables in JavaScript.