Automation is not Magic

0:00 So as much as I talk about wizards and becoming a Google Sheet wizard, I think that automations don't have to be treated like magic. And I want to share with you in this video a few things, a few formulas, and a little bit of ab script just to get you started and make your automations in sheets. So these are things that you can do that can be super quick automations. And just gets you introduced to the idea of App Script , nothing major. We're gonna type it out, we're gonna type it out pretty slow. If you do want to go deeper, check out my course Master Spreadsheet automation that I'm gonna go deep into exactly what app script is and how to really bend it to your will. So let's start without going to App Script and just do a filter .

0:46 Now I'm gonna change this sheet to data . And we have some tasks. We have owners of each of those tasks. We have the status and the priority. And I think filter or function is perfect for showing just a little bit of information, not everything. So we're gonna go equals filter in our cell and the range . We're going to click over here and we're going to select the entire spreadsheet of data. We just need to put the space bar in there and there we go. Data. We can also change this data. Exclamation point to data. A colon. Let's see here. We have up to H. We can also do that now. It's gonna give you an NA right now 'cause it doesn't have anything to filter.

1:33 We just added comma inside of here, and we have a condition . Now we're going to take ex almost exactly the same thing. We're gonna say data que exclamation point. And we're gonna look at owners. The C column. C colon C is equal to Alex. I'm gonna hit enter and I have some things. Now what do these mean? I'm gonna go grab this header . All of this and put it right here. So we have that information, all of the information, and we can see our filter is just looking at Alex. But if I wanted to look at Jamie, I would have to go inside of this cell, type in Jamie, and hit enter. And there's owner Jamie, but I don't want to, I want it to feel like magic. So I'm gonna insert a row above in this C column right here.

2:19 I'm gonna add a dropdown menu, right click. Drop down, I'm gonna change drop down to from a range and that range can click here, click in data , click in the owner column and you can see automatically. I have owner , Alex, Sam, Jamie. I can change this to data C tube to see, to get rid of that owner perhaps click done. Little trick. Now I have a drop done menu that I can select the owner. So I'm gonna go over to my filter and instead of the text, Jamie, I'm gonna delete that and I'm gonna enter C one on this particular sheet. So I'm gonna reference inside of this filter, whatever is in cell C one. And now I'm gonna change it to Alex and like magic, this is

3:07 updating and this is changing, and I only have to see the owner . So I'm gonna call this by owner . Maybe search by owner , and I'm going to duplicate this. I'm gonna create another one called Search by Priority. I'm going to delete this, CI this sell C one, and go over to Priority and create another dropdown from a range . Do the exact same thing. Click here, select this range . Priority. Actually, I wanna do E two E. I don't want the word priority. Click done. And now I'm gonna go change my filter. It's the e column is equal to E one. Let's go edit that to, hi. Now I see all of my high priority items here.

3:55 I can format this just like I format this, or I could delete part of parts of these. Maybe I don't need to know the due date. Maybe I don't need to know the owner . Maybe I don't need to know the status. Maybe I just want the task names. So instead of filtering data A through H, I'm going to filter B, colon B, and let's move that over here and see. Now I just have the task name. I can probably delete all of this. I can cut and paste this here. So it actually updates the reference here. So there, that's a nice little filter to create. I can put it on another tab, I can create a dashboard that that's a part of it. So it's an interactive interface dashboard, pretty cool stuff, and it's creating something that really feels like magic.

4:43 But without the mystification of it. Let's look at this last updated and do a little bit of app script . Anytime I edit one of these, I want to know when was that last updated? Let's go up to Extensions app script and every single Google sheet has this. It is free along with every sheet. So if you have access to the sheet, you have access to the app script , go to extensions app script . And this is a brand new sheet, so it has Untitled project. I'm gonna call this Automation Magic Rename. It'll have this function called My Function , and it'll have these parentheses and then curly brackets. And what's going on is this parentheses can include some kind of variable. You can basically create functions that.

5:28 Go into other functions and sometimes you need to carry variables between those or little items of information. And then inside of the curly brackets, we're gonna actually execute the automation. Now we can create functions that are just like update last edit, but there are a few. That we can type in a name and it automatically does it, like on edit or on open. If I type in on open, any function that I write inside of here will execute every time the spreadsheet is open or on edit with a, capital E will be when I make a change to a cell. So let's put a little event here. This is a variable because this. Whole on edit is a function that's already built in.

6:14 It's called a simple trigger . We're not adding any triggers. We just have to type on edit, and this event will give us some information so we know variable row equals event range . Get row. Now how do I know this? It's because this event is gonna be a whole lot of information. We don't need to know all the information that's happening, but we do need to know the part that is the range . And this.is gonna allow us to access the range of the event and then we execute a function dot get row. We're getting the row of the range . So we know a few things already that are, feel, maybe like a little bit of magic or a little like, uh, mystery. But essentially we are editing a cell of data . We're changing the value. We know the row, now we know which row we're on, and so we're gonna go

7:03 spreadsheet app with two capital s and a. Dot. So in the spreadsheet app is just like the entire function of the spreadsheet itself. It's sort of a special function that exists out there in the ether. Inside of this app script , get active sheet . So whichever sheet we're on now, actually what I want do, I do know we wanna be on the data tab. So we want to get active sheet as equal to this. So we wanna know what name is it. So we're gonna get name and now if, and we're gonna put parentheses and, and have some expression that has to either be true or false. If active sheet this variable, this. Name of the active sheet is equal to in quote data. Is it all caps?

7:49 Yes, it's all caps. Make sure it's spelled correctly. Then we're gonna execute whatever is inside of these curly brackets. And what that is is gonna be spreadsheet app dot get active spreadsheet , getting the entire sheet file, get sheet by name, which is the actual. Name of the tab is data get range . We want it to be on the row that we're on, comma and the number of the column. So this is G. We can find it out. If we don't want to go through the alphabet , we can see that is the seventh column. Do set value and that value we're gonna set is what's called a timestamp new with a date with a capital D and parentheses. This is a timestamp , so we could also do it where we say timestamp as a variable

8:36 and add a variable timestamp there. We can format this any which way we want by doing utilities, format , date, and then put that new date inside of here, comma, put in a time zone , so maybe GMT comma format , and we can say, Hey, we just want. Year, year. Year. Mm. For month, day, day day. Two days. Now let's save this. We need to save command S or click on the save project to drive button right next to run. Now let's see if this is gonna work. Write YouTube script today, question mark. And there we go. In the G column, it has updated the date automatically. Isn't that awesome? Doesn't it feel like magic film automation video tomorrow?

9:21 Who is that supposed to be? Sam? Let's give it to Alex instead. Same date that it's last updated. I hope that was just difficult enough to be interesting and easy enough to try to implement yourself. We went over a few variables. We went over this on edit, a simple trigger , and we also did the if statement here where we have if parentheses, some kind of true or false statement. So if we edit somewhere else on another tab, it's not going to edit the last updated. So we're just looking at GMEC. We're not adding the date. Hopefully that was fun for you. If you want to get deeper into App Script and you're like, oh, this is interesting, gimme more, then go check out Master Spreadsheet automation. I'll put a link in the description.

10:07 It's over on Udemy. Awesome course. Very deep minimum. Put in three hours into it and you'll get the core idea of ABS script, but put in more hours and you're gonna see a ton of different examples of ABS script, ai, APIs, all sorts of cool things. You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.