How to Create Custom Functions in Google Sheets

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:

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. 

Open your Google Sheet and select Tools > 

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:

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.

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.

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.

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:

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.

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:

Save your functions in a blank sheet and use it as a template by using a copy of it for all future sheets.

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.

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.