Spending Tracker Automations in Google Sheets

0:00 Let's automate our spending tracker. So you might have a spending tracker already or be building your own spending tracker in Google Sheets and want to create some automations for it. Let's create four automations that show you the extent of what Google Sheets can do. One is we're going to auto date every entry so we don't have to type in the date every time. We're going to highlight some incomplete rows. So if we Maybe add an expense, but don't have the price. It shows us, hey, you need to fill in the price. Once we fill in the price, we want to automatically total every month, or at least this month that we're in. And we're going to set a budget, and we're going to email ourselves when we are over budget. So our spending tracker looks like this. We just add in an expense, have a cost, and we're going to put the date here. So let's add an expense. Expense Tracker Template with Data Entry Form Inside Sheets Automate Your Expense Tracking in Google Sheets: A Step-by-Step Guide Expense Tracker Template with Data Entry Form Inside Sheets Track Anything! In a Google Sheet

0:45 Let's do this first one, the autodate entry. We're going to go up to extensions, Apps Script . Once you open it, it'll look like this. We're going to create a function called onEdit. This is actually a built in function . We just have to name our function this. We're going to use e as an event to get some really interesting results. information about each of the edits we do. So our variable row is going to be equal E. range . getRow and we'll know what column we're in by going E. range . getColumn. Column here. If call is equal to two, actually, I think. Let's look at our sheet. Spending. Yeah, we're just going to enter the cost here. Once we enter the cost, then we want to enter a date. So it's going to be. And we also want to make sure we're on the correct tab, so we want Spreadsheet Automation 101 Lesson 2: onEdit() Trigger How To Create a Stop Watch in Google Sheets Track Every Edit (Almost) Autofill Today's Date When Cell Edited

1:33 to make sure we're on spending. So variable sheet equals e dot, we'll do variable ss first, spreadsheet app dot get active spreadsheet , ss dot get active sheet dot get sheet name . And we'll add an ampersand here, two ampersands to say and, sheet is equal to, spending. We only want to do whatever comes after this. If, if we're editing column two B and on the spending tab, you can actually add one more thing here. Double ampersand row is greater than one. We don't want to do anything unless we're not on the header row . And if we are on the second column in the spending tab, Show Sheet Tabs Based on Edit Getting Started Coding in Apps Script Show Sheet Tabs Based on Edit Expense Tracker Template with Data Entry Form Inside Sheets

2:20 we want to get the date variable date equals. Utilities, format , date, new date, capital D, our time zone , I'm just gonna put in plus zero. You can put in whatever time zone you're in. And now the format of the date, I'm gonna do month, month slash dd, 2D slash four years. This could be any format you want. You can edit this text as the format you want. And now we're going to set the value of the date. Well, we're going to set it in s s dot get a cheat, get sheet by name, spending get range . We're going to go to whatever row we're editing on and the third column. And we're going to do dot set value. And the value will be the date. How To Change Date Format Automatically Writer Better Prompts How To Change Date Format Automatically Autofill Today's Date When Cell Edited

3:06 Let's save that. And check. This function on edit will always trigger any time we edit. Something will change the value of a cell. So let's put in breakfast today, five bucks. And there's the date automatically entered. We can do that again. Maybe we add second breakfast for ten dollars. And there's the date, automatic. So if we do not enter. Maybe brunch. If we enter an item, but we don't enter the cost, we want to add a automation for that. But we're just going to highlight it. So let's go up to Format , Conditional Formatting . Apply to range B colon B. And we're going to actually say, Custom Formula , because we want to do something other than just is it blank. Simple Inventory Management Automations The Simplest Bestest Checklist in Google Sheets Different Kinds of Automation Autofill Today's Date When Cell Edited

3:51 We want to say, Is blank. A one put not around it. We're gonna put, and, and two things is blank. B one. This A one. We need to put a dollar sign in front of the A and the parentheses. And now every time we have a something in A and nothing in B. It'll be highlighted. So it's Guinness and wrapped around everything not is blank a one with the dollar sign in front of the A and is blank B. That is true. So let's make this purple. Let's say that's pretty garish. So if we don't have a cost, it'll be there. So let's put in some 50 60 here and As we're entering these, Recreate a Starbucks Order Status Sign How To Grey Out Unused Cells

4:38 you'll see we need some template . As you see, we have our timestamps added here. And those that are not filled out have this garish purple here. So we're like, hey, we gotta fill that out. Alright, we've highlighted our incomplete rows. We've added an auto date entry. Let's create an automatic monthly total. Let's go on to our settings column. equals, we're going to sum up a filter . The filter is going to be the B column filtered by the C column. We're going to make sure that we're in the month of today. So we're going to wrap month around C and we're going to say equals month and wrap that around today. So today is a function that gives us a date every single day. We say Okay, whatever day is today, that's today. We now know what the month is. How To Filter Dates (They Are Numbers Too!) Filter a Database in Google Sheets based on Dates, Checkboxes, and Dropdown selections! Autofill Today's Date When Cell Edited

5:24 And if that's equal to anything in the C column, we'll add up the filter , the results of everything there, as a sum. And there we go. We got a monthly sum right here. And we can put a budget here. Thirty, three hundred dollars, let's say. So now we want to email when we're over budget. So let's go back to our automation and look at our onEdit. Now So, onEdit is definitely going to be of use to us here, but a simple trigger , which is this function onEdit, is not going to be able to email ourselves. So we're going to create a new function , emailWinOverBudget. We're going to have the E just like we have in our simple trigger here, and we're going to set it up almost exactly like our function up here. So we're going to need our variable row to know what row we're on. Simple Inventory Management Automations What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Different Kinds of Automation Email When Cell Changes

6:11 We're going to need our column. We're going to need to know the sheet as well. Almost everything, and we're going to use a similar if. If our, the one we're editing, the column we're editing is 2, and we're on spending and row, yeah, we want to do something. What is it that we want to do? Well, we want one more item here and budget spending is over budget. So let's get variable budget is equal to ss. getSheetByName and that's settings. ss. getRange . Let's go back to our sheet and make sure we're getting the right range . A2, and we need to get the value there. For spending, same exact thing, except this range is not going to be A2, it's going to be B2. Sheet Stories / Video Notes + Clear 24 Hour Old Videos Getting Started Coding in Apps Script Copy That, From One Sheet to Another - Learn to Code in Google Sheets Part 4 Square Cell

6:58 So we're just going to compare those two cells . And if spending is over the budget, We're going to do something. We're going to email ourselves, actually. So, who is it to? Variable to is ourselves, which is session. getActiveUser, getEmail. That's just us, or yourself. We're going to write a subject, equals overBudget, and we're going to write a body to the text, to the email. Hey, you're over your budget. Check this sheet and we can even put in the URL of this sheet. We'll get the URL of this active spreadsheet and let's actually email that to us. So Gmail app dot send email to comma subject body Automated Project Management in Google Sheets Simply Email Yourself From a Google Sheet Email Yourself a Cell from a Google Sheet, Every Day Automatic Email Report from Google Sheets

7:45 if we try to run this function right now. It will ask us to authorize and then it will give us an error . If you don't want to run this function right now and just want to put the trigger first, you'll have to authorize when you create the trigger . So let's do that. Let's go over to triggers. We need to do this in order for the Gmail app to work, add trigger on the bottom, right? We're going to select our email when over budget and select event type on edit, click save. Okay. At this moment, we'll have to authorize our automations, just the use of those items like Gmail app and drive app. Once it's set, you'll see this email over budget. So let's run this a couple of times. Let's say it's 25. Automate Emails Simply Email Yourself From a Google Sheet Different Kinds of Automation Email When Cell Changes

8:30 If we go over to our settings, our total is not over budget. Okay. We do 55. Puts in the timestamp , and now we're over budget. Let's do a new, new suit, 66. And we're way over our budget. Let's go to our executions and see if we've sent that email. It says completed. And if I check my email, I see I have an over budget email. Hey, you're over your budget, check this sheet. And it has a link to the sheet we're on. Very cool. So we've now emailed when over budget, we have automatically created a date entry highlighted in complete rows and conditional formatting . We've also created an automatic monthly total and email ourselves when we're over budget, thanks for watching and enjoy this spending checker automated, feel Simple Inventory Management Automations Automate Your Expense Tracking in Google Sheets: A Step-by-Step Guide Email When Cell Changes

9:18 free to subscribe here to better sheets on YouTube to get more automations, get more out of your Google sheets, make your life and Google sheets way better. Spreadsheet Automation for Beginners Absolute Basics of Google Sheets Better Sheets Videos Checklist Automated Daily Reminders