Expense Tracker Template with Data Entry Form Inside Sheets

Intermediate

About this Tutorial

Create a data entry form for an expense tracker. Simple dashboard too with sum and sum between two dates.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

FILTER() IFNA() SUM()

Video Transcript

<div>0:00 So here's a very simple expense tracker. We're going to have some expense, you know, dinner. We're going to put in the cost, and we can put in a date. 0:11 You can even pick this date here. Let's say it was last night. So this is pretty cool, right? Just very simple expense tracker. 0:21 We can create a summary. We can add up the sum of this cost.

Uhm, and I will do that, put a little dashboard together, but on that dashboard I want to 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 expense. 0:37 I want to just enter into a form the expense, the cost, the date, and it inserts it for me. So I'm going to create a dashboard here. 0:46 It's a brand new page. I'm going to have these three items, expense, cost, and date. And then we're going to create a little form. 0:55 So let's view without the gridlines . Let's delete a bit of the excess stuff.

Let's create some grid lines here, make it a little bit thicker. 1:12 And let's create that dashboard , right? So, total, it's going to be equal to the sum of what's on the expenses Can even put together a start and end. 1:36 And put a subtotal. Maybe flip this around. So let's put a date of validation and make this a date. 1:58 IsValidDate, done. And we do that as well. And now we have a picker.

And we can say, hey, we want to look at everything from here to here. 2:08 And so we're going to sum the filter of range , just get the cost, where the date is greater than the start date . 2:24 Shake that. Greater than or equal to. And the end date . And then this date is less than or equal to this date. 2:41 And sum up all of that filter and we have a subtotal. So we can change these Let's see. There to there. 2:50 We're going to get an N-A. So we're going to wrap if N-A. We want to wrap that so we get a zero. 2:58 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. 3:08 17th. And so now, between this and the 9th and the 23rd, we have 30 bucks. But we have $60 total. 3:18 There. Let's make this all quicksand. Make it a little bit bigger too. Fun size. There. So we have a total total, then a subtotal if we want to know how much we've spent during a particular time. 3:34 Like maybe all of April, let's say. $60. And in this form here, let's create one more line. Give it a little bit more space. 3:47 We're 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. 4:06 Now it's a picker. Let's say it was today. Now we want to enter that into our expenses. The automation is going to be going to insert a row above two and then put whatever is in B4 into A2 and so on and so forth. 4:22 So let's go to extensions app script and start building this.

We're going to call it function submit expense, I'm going to go spreadsheet app dot get active spreadsheet dot get sheet by name. 4:41 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. 4:54 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 going to take the format of the header column. 5:05 But we don't want to do that, we want to take the format of the second row. So let's do that. 5:12 So we're inserting before two.

Before two. Now we're going to move everything. So we need variable expense equals, what is that? 5:23 Dashboard . Dashboard . Get range . Actually let's make this a variable as well. Variable dashboard equals this. 5:37 So we only have to write dashboard dot get range . Just double check the range is B4. B4. Get value. 5:49 We just want the value inside there. That's our expense. Our cost is going to be C4. And our date is going to be D4. Right? 6:04 Double check. B4, C4, and D4. We can change that around if we need to.

But how do we insert them? 6:19 Well, we have a new row already because we've inserted this row up So let's go to our experiment. 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 6:59 now we could use the same notation here, a, uh, what is that, row two, so A2 but for this particular case I like to have the, uhm sort of, row column notation . 7:17 So we just change that second part, that column that it's in. Expense, cost, and date.

Save it. And now we want to click run, submit expense, but before we do that we want to do one more thing which is we want to clear these ranges . 7:37 So that we can enter something else. So let's go back up here to this B4. Clear content . We want to clear content , not clear everything from it, not the formatting or anything. 7:51 D4. 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 them here. 8:05 We're going to insert a row, we're going to take B4, put it in A2, so on and so forth, and then come back here and delete or clear this content from here. 8:16 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. 8:22 We don't have any errors and we have our dinner friend submitted. 8:41 Cool, it's working.

I want to do one extra thing, which is insert a drawing , a clickable button here, so that we can actually do this without having to go to the Apps Script . 8:50 So the function is called submitExpense, so I'm going to copy that just so we have the exact thing. I'm going to show you what to do. 8:58 Insert a drawing . Let's create a fun shape. This shape here. SubmitExpense. I want to make it look a little bit bigger, the text. 9:17 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 OK. 9:31 Now, let's see if it works. Let's buy a new bike for $100,000. Let's say we're buying this, this Monday. 9:44 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 an expense tracker that you might not want to see all of your expenses when you're adding it up. 0:02 Or adding a new one.

You might not want to 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. 0:13 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? 0:19 Let's say it's the 20th to the 23rd. Cool! We've spent $100. In that time period. Awesome! Hope you enjoyed that video. 0:30 If you want this sheet and you're on BetterSheets.co right now, down below is this exact template .</div>