Google Sheets Custom Functions

Google Sheets Custom Function

Introduction

Google Sheets, like other spreadsheet applications, has built-in functions that the user can use to interpret or manipulate data. Functions like SUM and AVERAGE come in handy to accomplish common scenarios. However, people may encounter challenging tasks which leave the built-in functions inadequate. Modern spreadsheet applications each have their own implementation of providing users a way to create their own functions. For Google Sheets, this feature is called App Script. Fortunately, we don’t have to learn a proprietary scripting language for this because App Script is in JavaScript — a programming language commonly used by front-end web developers (and full-stack web developers like me).

In this tutorial, we are going to implement an App Script function that outputs the bottom-most numeric value of a given range of cells. This function is useful for calculating the aggregate (for example sum, average, minimum, maximum) value of the ending balance from multiple columns.

Step By Step Instructions

1. Open a web browser and do 1 of the following:
   • Navigate to an existing Google Sheet document.
   • Navigate to https://docs.google.com/spreadsheets.
2. From the menu bar, click “Extensions” > “App Script”.
3. App Script editor will open in a new browser tab.
4. Each of the top-level functions will be available for use in the spreadsheet.
5. For this tutorial, let’s implement the function, BottomNumber.

    function BottomNumber(vRange) {
      var pOut;
      for (var pRowIndex = 0; pRowIndex <= vRange.length - 1; pRowIndex++) {
        var pRow = vRange[pRowIndex];
        for (var pColIndex = 0; pColIndex <= pRow.length - 1; pColIndex++) {
          var pCell = pRow[pColIndex];
          if (pCell == null)
            continue;
          if (!isNaN(pCell)
            && (typeof(pCell) == 'number'))
   
            pOut = pCell;
        }
      }
      return pOut;
    }
   

6. Although not required, it is a good idea to rename the project for easy identification.
7. Save the project.
8. Go back to the spreadsheet.
9. Upon typing “equals” (=), the name of your custom function will not be among the list of functions. So, do not be alarmed if you cannot see your custom function.
10. Use the custom function.

    =BottomNumber(C6:C16)
    

Resources

I’ve shared the sheet I used for this tutorial via this link. Don’t forget to make a copy of that document to be able to edit and access the script.

Closing

That’s it. That’s all you have to do to create and use a custom function in Google Sheets.