Apps Script tutorial: custom function for budgeting in Google Sheets

Course 2 of 5 Apps Script + Sheets tutorials

LEVEL: Beginner and/or curious!

Introduction

Companies, organizations, households, students, etc all benefit from budget planning in order to optimize savings, because you forecast your total expenses, and then closely monitor the true cost in order to course correct and you reach a savings goal.

In this article I will walk you through how to create your own custom function so you can prepare your annual household budget by entering your expenses in one row and marking their frequency in another (ex: annual, monthly, weekly, daily, or only one time). When you have rows and rows of expenses with different time periods, creating your own formula with a Google Apps Script helps you save a lot of time.

This is because when you have all your budgetary information entered in your sheet, you can use it to auto calculate the total annual amount of each expense by entering the formula ex: =ANNUAL_COST. This multiplies your expenses by 52 weeks if you mark it as annual, or 12 months if marked monthly.

Also note this is a simple example to inspire you to look at your process and create your own custom function, so let’s get started!

— — — — — — — — — —

A. Get your sheet

• Make a copy of this spreadsheet.

B. Organize sheet and enter data

We must first setup our sheet using data validated columns. Let’s import values from other sheets to create the drop-down options for these columns.

Data validation from another tab

• In your sheet there are two columns called Categories and Frequency that will have drop-down options that are imported from 2 other tabs in your sheet called.

Setup a drop down to categorize expenses

• From the budget tab, select the top of column A (so it selects the entire Categories column), then select the Data menu at the top, and choose Data Validation.

• In the new dialog box that pops up, locate the field Cell range, and change A1 to A2 (so it skips the header).

• In the Criteria field, select the grid icon

Note: a dialogue box appears which allows you to enter a data range, but in this case we wish to have this range automatically populated by selecting the fields we want.

• Without closing the dialogue box, visit the second tab called Categories — data validation and select A1 to A6, and then choose OK in the dialogue box.

Setup a drop down for how frequently a cost is incurred

• We will repeat this process for the budget column. Return to the Budget tab, and click the top of column B (this highlights the entire Frequency column) and select the Data menu, and choose Data Validation.

• In the new dialog box that pops up, locate the field Cell range, and change C1 to C2 (so it skips the header).

• In the Criteria field, select the grid icon

Note: a dialogue box appears which allows you to enter a data range, but in this case we wish to have this range automatically populated by selecting the fields we want.

• Without closing the dialogue box, visit the second tab called Frequency — data validation and select A1 to A6, and then choose OK in the dialogue box.

Populate data with data validation options

• Return to the budget tab, and choose a category for each item that best describes it from the drop down in column A (grouping items in this way helps you create reports if you wish by category).

• Now choose a frequency for each item from the drop down in column C.

• Your sheet will now look like this, but with your own options.

C. Create a custom formula to calculate your expenses on an annual basis

• Google Sheets has over 400+ functions to use, but sometimes you wish to create your own formula to use over and over again to automate calculations, and you can do this by creating a custom function in it’s built-in editor.
• Visit this free editor by selecting the Tools > Script editor menu at the top of your sheet.

• Your sheet already comes with the logic for calculating the values you selected from the frequency drop down.

Note: If you are interested in how the code works, I cover this at the end.

Use the custom formula in your sheet

• Return to the budget tab in your spreadsheet and in cell E2 paste the formula =ANNUAL_COST(D2,C2) and then click enter.

• The formula will begin to multiply the frequency you selected by the amount field. For example, it will list monthly rent to be a total of $16,800 a year.
• Apply this formula to the entire list of expenses by dragging the bottom right corner of cell E2 until the last expense (E11).

Note: it’s important to use drop-downs in order to minimize user errors from misspelling.

• Great so your annual budget is complete!

C. How the code works

By visiting Tools > Script editor in your sheet’s menu bar, you will encounter 2 functions. One is called `ANNUAL_COST` which is written in uppercase like all spreadsheet functions, and it multiplies two parameters: the amount by the frequency (which is a calculation from another function called `timesPerYear`).

That second function uses a switch statement which is similar to an IF statement, in that it triggers a different action based on if a condition is met.

And there you have it, your own formula to help with budgeting.