Expense Tracker Template with Data Entry Form Inside Sheets
Learn how to create a simple expense tracker template in Google Sheets with a data entry form for easy expense management. This tutorial covers setting up a dashboard, automating data entry, and summarizing expenses over specific date ranges.
So here's a very simple expense tracker. We're going to have some expense, you know, dinner, we're gonna put in the cost, and we can put in a date. We can even pick this date here. Let's say that was last night. So. This is pretty cool, right? Just very simple. Expense tracker. We can create a summary, we can add up the sum of this cost, um, and I will do that, put a little dashboard together. But on that dashboard , I wanna do one more thing, which is I don't want to have to see all of the expenses that I've had. When I go and enter a new, new expense, I wanna just enter into a form, the expense, the cost of date, and it inserts it for me. So I'm gonna. Create a dashboard here. It's a brand new page.
I'm gonna have these three items, expense, cost, and date, and then we're going to create a little form. So let's view without the grid lines, let's delete a bit of the excess stuff. Let's create some grid lines here. Uh, make it a little bit thicker. And let's create that dashboard , right? So total it's gonna be equal to the sum of what's on the expenses in this can even put together a start and end and put a subtotal. Maybe flip this around there.
So let's put a date of validation and make this a date is validate done And we do that as well. And now we have a picker and we can say, Hey, we wanna look at everything from here. To here, and so we're gonna sum the filter of range . Just get the cost where the date is greater than the start date , actually greater than or equal to two, and the end date , and then. This date is less than or equal to this date.
And sum up all of that filter . And we have a subtotal so we can change these dates. Let's say there to there. We're gonna get an na, so we're gonna wrap . If na, I want to wrap that so we get a zero. Let's make sure that is dollar sign . So if we add some more expenses, let's say another dinner. But it was last week, 17th, and so now between the ninth and the 23rd, we have 30 bucks, but we have $60 total there. Let's make this all quick sand, make it a little bit bigger too. The fun size. There. So we have a total, total, then a subtotal. If we wanna know how much we've spent during a particular time, like maybe all
of April, let's say 60 bucks, and in this form here, let's create one more line. Give it a little bit more space. Are going to enter an expense. Let's say it's dinner with friends. The cost was $30 the date. We want the date to be the same as that there. Now it's a picker. Let's say it was today. Now we want to enter that into our expenses. The automation's going to be gonna insert a row above two. And then put whatever is in B four into a two, and so on and so forth. So let's go to Extensions app script and start building this.
We're gonna call it function , submit expense. We're gonna go spreadsheet , app, do get active spreadsheet , get sheet by name. I think it was Expenses. Yeah. Expenses. Insert row before two. Now you might be thinking, why don't we insert a row after one? It's because whenever we insert a row, it's going to take whatever format it's inserting from. So if we're going from one down, it's gonna take the format of the header column, but we don't wanna do that. We wanna take the format of the second row. So let's do that. So we're inserting before two now.
We are going to move everything. So we need variable expense equals what is that? Dashboard ? Dashboard get range . Surely. Let's make this a variable as well. Variable dashboard equals this, so we only have to write dashboard , get range . Just double check. The range is B four. B four get value. We just want the value inside there. That's our expense. Our cost is going to be C four and our date is going to be T four, right? Double check B four, C four and D four.
We can change that around if we need to. But how do we insert them? Well, we have a new row already because we've inserted this row up here. So let's go to our expenses. Let's create a variable expenses sheet. Close that and we can. Get rid of this code expenses sheet and now go to get range . We want to go to the second row in the first column. Set value expense. Now we could use the same notation here. A, uh, what is that? Row two, so a two. But for this particular case, I like to have the, uh, the
sort of row column notation . So we just change that second part, that column that it's in expense, cost, end date , save, save it. And now we wanna click run, submit expense. But before we do that, we wanna do one more thing, which is we want to clear. These ranges so that we can enter something else. So let's go back up here. This B four, clear content . We want clear content , not clear everything from it, not the formatting or anything. C four. D four. So now we're going to automate what we would normally do if we had to. Take all of this, three pieces of information and add 'em here.
We're gonna insert a row, we're going to take B four, put it in a two, so on, so forth, and then come back here and delete or clear this content from here. So let's click run with submit expense and test it out. We're going to have to authorize it the very first time we do it. We don't have any errors and we have our dinner friends submitted. Cool. It's working. I wanna do one extra thing, which is insert a drawing to a clickable button here so that we can actually do this without having to go to the app script . So the function is called submit expense. So I wanna copy that just so we have the exact thing. I'm gonna show you what to do. Insert a drawing . Let's create a fun shape.
This shape here. Submit expense. I wanna make it look a little bit bigger. The text there, save and close. We have a button. Now let's put it right there and click the three buttons next to it. Inside it. Assign script. Paste our name of a script, click okay. Now let's see if it works. Let's buy a new bike for a hundred bucks on. Let's say we're buying this, this Monday, submit expense. It's now cleared from here. Our expenses has a new expense here. Awesome. And our total is increased. That's how you create a sort of data entry form inside of sheets for expense
tracker that you might not wanna see all of your expenses when you're adding it up. Or adding a new one. You might not wanna have to scroll down to the bottom to add it. This dashboard makes a nice cool interface where you can submit that expense without any other information. And we have a nice dashboard . We can see the total, we can see the start and end date between two dates. What's the subtotal of that? Let's say it's the 20th to the 23rd. Cool. We've spent a hundred bucks in that time period. Awesome. Hope you enjoyed that video. If you want this. Sheet and you're on Better sheets.co. Right now. Down below is this exact template .