Productivity

How to Create Custom Functions in Google Sheets

Anthony Grant Updated 03-06-2020

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.

Advertisement

If you need to go beyond the scope of what Google Sheets has built-in, 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

Advertisement

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.

Advertisement

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 The Ultimate JavaScript Cheat Sheet Get a quick refresher on JavaScript elements with this cheat sheet. Read More .

Open the Script Editor

Open your Google Sheet and select Tools > Script Editor

Script Editor for Custom Function for Google Sheets

Advertisement

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.

Advertisement

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 5 Excel Autofill Tricks to Build Your Spreadsheets Faster These spreadsheet autofill tricks will help you complete tasks smarter and faster so you spend less time in Excel. Read More 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. 24 Google Docs Templates That Will Make Your Life Easier These time-saving Google Docs templates will help you finish your documents instead of struggling to put them together. Read More

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 4 Ways to Find the Best Google Sheets Templates Looking for Google Sheets templates? Use these tips to find the right Google Sheets templates and use it for your work. Read More . If you want to dig deeper into scripting with Google Sheets you’re going to want to master JavaScript. Learn what JavaScript is What Is JavaScript and How Does It Work? If you're learning web development, here's what you need to know about JavaScript and how it works with HTML and CSS. Read More and the basics of declaring variables in JavaScript. How to Declare Variables in JavaScript To get started with JavaScript, you need to understand variables. Here are three ways to declare variables in JavaScript. Read More

Related topics: Coding Tutorials, Google Drive, Google Sheets, JavaScript, Scripting, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Paul
    June 3, 2017 at 11:33 am

    I enjoy reading this article as I am starting to use Google Spread sheets and I was wondering what is meant by the script editor, I am now going to try and learn about this Feature and use it my spreadsheet !
    many thanks

  2. alex
    November 28, 2016 at 4:20 pm

    What was the point to wright this , if you do not address the main problem in creating google scripts - input ranges