Have you ever wished your spreadsheet was as smart as you? Well, it may never be quite that smart, but with a little help from Google Scripts you can create your own custom functions and get it just a bit closer to that goal.

Google Sheets already has a lot of great features to handle numerical calculations, look-ups, and string manipulation, to name a few. However, you might find yourself needing to build lengthy or complex formulas that are difficult or impossible with the built-in options. If this is you, then you should consider taking the next step and building your own custom functions. This article will demonstrate how to accomplish this using a simple Google Script.

## Built-in Functions

Spreadsheets have pretty powerful functions built into them already and Google Sheets is no exception when it comes to the cool things you can do out of the box I am ashamed to admit that I've only recently decided to start experimenting with Google Spreadsheets, and what I've discovered is that whatever allure or power Excel had over me due to the amazing things... Read More . A simple example of built-in functions would be “Sum” or “Average:”

What if you wanted to perform a dynamic calculation that the current functions just do not handle? Consider a scenario where you want to add the tax amount to a purchase. Since tax rates vary based on location, you would need to build a function with a long list of nested “If” 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. It becomes unmanageable fast. Sure, there are other ways of handling this, like adding the information to another table and performing a vlookup In a giant Excel spreadsheet, CTRL+F will only get you so far. Be clever and let formulas do the hard work. Lookup formulas save time and are easy to apply. Read More , much like you would in Excel, but once you learn how to create your own custom function in Google Script, your mind will be opened to a new world of useful possibilities. So let’s begin.

## Create a Custom Function

If you are new to scripting, fear not! It’s pretty simple to use. The following example will get you started, and if you want to really dig in for a deeper understanding of Google Script, we’ve got you covered Do new and cool things with the Google services you use every day. Extend and automate by learning the cloud based scripting language from Google. Read More .

### 1. Open the Script Editor

From within your Sheet select Tools > Script Editor

You will want to give your function a useful name; verbs work well. Information that you will paste into your function will go inside the parentheses as variables. Typically, this will just be the cell value that you want to work with, but if you have more than one value simply 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);
}
``````

Note: You can see I’ve only included two locations in this example, just to give you the idea. You can add more by just adding additional “case” and “break” lines with the additional locations that you require. This will be good practice.

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

Now the fun begins! Once your function has been created you can start using it the same way you would use a built-in function. In the cell where you want your calculation to display, you enter “=yourFunctionName(inputs)”

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 "Get that report to me, ASAP!" A core set of Microsoft Excel and Word tricks can make this task less stressful and help you whiz up a stylish report in no time. Read More to drag and drop your function to all of your rows, just as you would a built-in function:

After you’ve created your first custom function, you will probably have several more that you’d like to add and you can do that with no problem. Just follow the steps above to create a new function the same way and add it below your existing work.

The above script yields the result below:

Don’t let all that hard work go to waste. You will want to use your custom functions again. Even if you don’t need all of them in future sheets there is no reason not to have them at your disposal.

You can reuse your work in a couple different ways:

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

1. Copy and paste your functions from one sheet to the next. This is tedious, but it will work. Simply open the script editor and copy all of the code out of one sheet and then open the script editor in another sheet and paste the code there.
2. 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, otherwise it will be publicly available. If you haven’t used the template gallery before, it’s worth checking out as there are many other useful templates out there to make your life easier Templates can save you lots of time. We compiled 24 time-saving Google Docs templates for work, health, home, and travel. Get on with your projects, rather than struggling with putting together documents. Read More !

This isn’t required, but it will certainly give your work a professional feel and may save you from a lot of questions if you plan to share your work with others.

Have you ever created custom functions in Google Sheets?  Do you have any that you would like to share in the comments?

Image Credits:stacking up lego by graja via Shutterstock

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