Automate Expense Tracking in Google Sheets
Learn how to automate your expense tracking in Google Sheets with powerful formulas and Apps Script. This video covers creating dropdown menus, running totals, timestamps, and email alerts for low balances.
We're starting off the year right. We are tracking all of our expenses, but we want to automate them. We want to automate expense tracking in Google Sheets. I'm going to show you some really cool automations we can do once we have our sheet set up, and we're going to be able to do this with formulas. We're going to do a little bit of Apps Script . Anything you see here, you can get the code to, and learn more about the code. at BetterSheets. co, sign up for a class, sign up for a membership if you want to get all the code and everything downloaded directly to your Google Drive. But for right now, let's change a few things in this expense tracking so that it's a much easier to handle. Here we have all these negative numbers. We're just going to turn these all to positive just because we know these are all currencies.
We can also take that and just hit that currency symbol and now they're all currencies. We know these are all negative. We're spending this amount. We want to add a category drop down menu instead of having to enter a new item and having to type out the category. So right click drop down menu. We're going to create a drop down menu here, food and drink, utilities and housing. We can also color these. We may want to color these by categories of categories, like things that we cannot not have, like utilities and housing, or even health. Color those all the same so that we can see generally what we're spending on. Click done. Notice that we do need to change the range to D2 colon D to apply to the entire range , and that's it.
Here's some automations we're gonna be adding. We're gonna add a running total , running balance left. I'm gonna show you how to edit that if you want a running balance left instead of just a total. We're gonna be able to duplicate a template every month or every week, depending on how much expense tracking you're doing. We're gonna automate a timestamp . We're going to also highlight unfinished amounts for items listed. If we're not filling it out correctly or completely, I'm gonna show you how to do that with a timestamp . Very simple conditional formatting . And we're going to actually automate an email at the end of this video. That'll be really cool. So let's do the running total first. All we need to do is equal some, and right away, it's trying to auto configure that this is the only amounts we have, but what we want to do is actually do Automatic Calculations
C2 colon C don't put an eight there. C2 to C2. . We just need to add a dollar sign in front of the 2. And if we autofill it, you see the numbers are going up all the time. Each one. And if we look at the formula there, the second number is changing each time. So I'm actually going to copy this all the way down, and notice there's a total down here, so we can hide that. So if, is blank, c2, two commas, and at the end we'll put a parenthesis, now copy paste that all the way down, and see now we have it hidden . So now as we enter a new thing, maybe we're paying our rent early, another 800 bucks. Housing. You see the total is here.
That is now automatic. But if you want to do it from a balance, let's say, instead of adding up, we're subtracting. So whatever our balance is, let's say we're spending 2, 000 minus this sum. So now we see how much is left over after each item. So that was running total and running balance left. Let's duplicate a template every month. Let's call this template and let's duplicate it right now. So this will be say January for our template . We're just going to delete everything except this formula now. We're going to actually be adding some more interesting formulas and stuff, but let's just do this for now.
We want to copy this template and duplicate it at the beginning of every month go to extensions app script . If you've never opened this before, it's where we're able to code our app script using very basic JavaScript and some built in functions . So I'm going to create a function here called create. Duplicate . We're going to use SpreadsheetApp. getActiveSpreadsheet. You can type along with me. We're going to getSheetByName, and we're going to call this template, which is the name of this template here, the sheet, the name of the sheet. We can use a variable called variableTemplate equals this. And now to duplicate it. All we have to do is go template. copyTo, and where are we going to copy it to? Well, we can just copy it to our active spreadsheet.
That's all we have to do. So let's see how this runs. The first time we click Run, you're going to have to authorize it. So you'll see something like this. You'll have to authorize and click Allow. Once it's copied, it's now a copy of template . But let's say we're. creating this copy at the beginning of the month, say on the first of the month. We can name this actually. Set name and we're going to name it the name of the month. How do we get the name of the month? We'll call name of month variable is equal to utilities dot format date new date. Our time zone is just going to be GMT plus. Actually, we can get the time zone of the sheet. Or we can just set it here. Let's just do GMT plus zero. Our format in quotes is going to be four Ms.
Let's log this and see what it is. Name of month. I'm going to set the name name of month. Click run again after we save it. We can see it is January. See, a sheet already exists that's January, but once we set this in motion, it's not going to have the same month. So right now it's January, and we're trying to create one with January in the name. But what we're going to do is we're going to set this to only run at the beginning of the month on the first, so there will be no other sheet with that name. So our function is called create duplicate . How do we automate this? Go over to the left side, click triggers. Now go to the bottom right once it shows up. It'll be here called add trigger . Choose which function to run.
If you have multiple functions here, maybe at the end of this video we'll have multiple functions . Choose which one to run. We're going to create duplicate . Select event source is the most important here. Here we're going to click. Click on time driven, and we're going to choose month timer, and select the other month. Yes, the first. We want to run it as soon as possible, probably, or even just early morning. We are in GMT 8, so I'm actually going to go back and change the script a little bit, just to allow for that time. We can totally edit the script after we create this automation. Now, if you're Wanting to delete this automation, just come back to this triggers and click the three buttons and click delete trigger here. Let's go back to our editor and change this GMT to plus eight so
that it aligns with our sheet. And that's it. So now we have automated our duplicating of the template . So let's create a timestamp when we're entering an item here. Dinner with wife. We want to add a date here, automatically. So what we're going to do is at the very top, we're going to use a function that's built in called onEdit. That's the name of it. We have an event e, which is the event where we can get some really interesting information here like variable row equals e. range . getRow. We can know what column we're in. e. range . getColumn. Capital C. We can also know what sheet we're on. Sheet equals, actually we can know that by just SpreadsheetApp . getActiveSheet. GetName.
And if row is greater than one, meaning we're not in the header row , and column is greater than one as well, we're not actually editing the time. maybe. Actually we only wanna do it when we enter an item, so actually instead of column grade than the one we'll do equal is equal to two. So making sure we're only editing that name of the item. And if so, let's get the date. This utilities dot format date, new date again, GMT plus eight. And the format . So we want, let's look at our dates. Now we can choose any format we want. Let's do year, year, year, hyphen, M M hyphen, day, day. Actually, I think it's going to be all caps.
That's our date. And we want to set it in the first column. So let's go spreadsheet app dot get active sheet . Get range , the first row, the first item will be the row we're in, so we're going to get that from here. The column, we want the first column. And we want a dot set value date. And now we're going to save this and see how it goes. So we're deleting that, there we go. So dinner with wife. Dinner with family. And once we enter it, we get our date right here in the date column. Fantastic. We've now automatically entered a time stamp when entering the item. Let's highlight some unfinished items or amounts. So if we have here nothing in this amount category, amount
column, but we want to say, hey, we need to fill in these amounts. Let's go up to format , conditional formatting . We're going to apply to the whole range c colon c and we're going to choose format rules custom formula or do equals b one equals not equal to blank. So let's say not is blank b one Our custom formula is equal is blank, B1. We're going to say not is blank, B1. We also need to say that C1 is blank as well. So we're going to wrap this with, we're going to wrap this with and, and we're going to have two items. Not is blank, C1. So we want to know that it is blank. So we're not going to use not there.
There we go. So we're having two items. One that B1 is not blank and C1 is blank. And that's just wrapped around the and. And instead of green, let's do a little bit of red here. Done. So now, every time we have an item in the B column, We have an automated red to show us, Hey, we haven't put in an amount yet. Let's also add a conditional formatting to see that says, Hey, if there's something above a certain amount, we want to highlight it as well. Conditional formatting , add another rule. We're going to change the format rules to greater than 1000 and this will be orange done. Let's put in car payment, twelve hundred, and there it is.
But maybe we want the entire row . So let's go back to our value is greater than a thousand, and apply it to the entire range . But you notice there's some extra dates here that are missing. highlighted. So we actually have to put in a custom formula is equal to C1 and put a dollar sign in front of the C greater than 1, 000. If we don't want this top row we can do A2 and change C1 to C2. There, so now the entire row is highlighted this orange if we have an amount that's too high. Click Done. We've highlighted transactions above a certain threshold. Now we want to email ourselves if we have a low balance. So if our E column is some certain number or even maybe less than 50 or something.
Let's see. Let's go back to our app script . Now you might think we can do this with the on edit function that's built in. There is however one little problem is that this on edit function will not be able to email. So we need to actually create another function , low balance alert. Still with the E as in the event, because we're going to add this as a trigger later on. But we need to write the function first. Let's get all of the same stuff that we got here. And instead of the sheet, we want to know if column is equal to, we want to make sure we're in the C column, 3. And we need the value. So variable edit value is e value. So that's what we're e value.
This is the actual value we're editing. Whatever we're putting in edit value is greater than, no, we don't need to check the edit value, delete that. We wanna know balance is less than 50. And because we know the row and the column, we can get balance equals spreadsheet app, get active spreadsheet , get range , actually we need get active sheet , get range , will be on the row, and our column will be the fifth column, get value. So that's where our balance comes from. And if that balance is less than 50, we're going to do mail app dot send email.
Who are we sending it to? Well, let's just get the spreadsheet app, get active. Let's do variable owner email equals spreadsheet app dot get active spreadsheet . Get owner , get email. Whoever is the owner of this spreadsheet will get their email. Usually gonna be us owner email. That's who it's going to. The subject is going to be over, or actually low balance alert. Our body will be, let's just put the URL of the sheet. Spreadsheet, variable URL equals spreadsheet app dot get active. Spreadsheet, get URL, and we'll put check this sheet plus URL.
So this low balance alert will email us, hopefully, let's see. We have to create a trigger for this over on the left side triggers, add trigger on the right. Choose which function low balance alert and we'll select from spreadsheet on event type will be on edit. This is slightly different than our on edit in our app script . It allows us a few more options like this emailing. Let's click save. We will have to authorize it the very first time we run it or when we're creating this trigger . And let's test this out. So let's say we have an expensive night out, 150. Our balance is now 34. And here's the email that I just got zero minutes ago. Check this sheet. Low balance alert.
Very cool. This worked. If you don't know if it worked, just go to your triggers or your executions. And here it might say failed for some particular reason. Some reasons might be that you didn't get this email. Maybe there's no email or balance is different. There might be some errors in this if formula , perhaps. There we go. We've created a bunch of awesome automations of running total , balance left, duplicating every month new template , automatic timestamps, highlighting unfinished amounts, highlighting transactions above a certain threshold, and even emailing us a low balance warning. If you're looking to get more out of your Google Sheets than you ever have before, you're learning automation here on BetterSheets on YouTube. Subscribe today.