Pinterest Stumbleupon Whatsapp
Ads by Google

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 Perform Amazing Feats With These Useful Google Spreadsheet Functions Perform Amazing Feats With These Useful Google Spreadsheet Functions 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:”

Sheets-BuiltIn-Functions

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 Find Anything in Your Excel Spreadsheet with Lookup Functions Find Anything in Your Excel Spreadsheet with Lookup Functions 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.

Ads by Google

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 Learning Google Script: 5 Best Sites & Tutorials to Bookmark Learning Google Script: 5 Best Sites & Tutorials to Bookmark 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

Sheets-ScriptEditor

2. Create Your Function

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);
}

Sheets-CustomFunction-Code

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.

3. Save Your Function

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

Sheets-CustomFunction-Save2

Use Your Custom Function

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.

Sheets-CustomFunction-Example

You can use AutoFill 5 Microsoft Word & Excel Skills You Must Have to Succeed at the Office 5 Microsoft Word & Excel Skills You Must Have to Succeed at the Office "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:

Sheets-CustomFunction-AutoFill

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.

Sheets-CustomFunction-Code2

The above script yields the result below:

Sheets-CustomFunction-Example2

Reuse Your Functions

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:

Sheets-MakeACopy

  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 24 Google Docs Templates That Will Make Your Life Easier 24 Google Docs Templates That Will 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 !

Document Your Work

Google Script supports the JSDoc format, which allows you to add comments to your formula to document and provide help context.

Sheets-JSDOC-exmaple

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.

Sheets-CustomFunction-Documenting

You can do so many cool things with Google Script. Creating custom functions for Google Sheets is definitely one of them. You can now break out of the box cell and make Google Sheets more functional Excel vs. Google Sheets: Which One Is Better for You? Excel vs. Google Sheets: Which One Is Better for You? Do you really need Excel? Both desktop and online solutions have their advantages. If you have trouble choosing between Excel and Google Sheets for managing your spreadsheets, let us help you decide. Read More for your individual needs. For more information about creating custom functions, Google has a great knowledge article for you to explore.

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. 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

Leave a Reply

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