00:00 Hey, have you seen the Pride thing that's going on in Sheets? This has been a thing that has existed for a while.
00:08 It is Pride Month, it's June 2nd as I film this. If you just go into A1 on any Google Sheet and type in P in A1, R in B1, I in C1, D in D1 and E, which actually that's funny enough, D and E line up there, E and E1 hit enter, you got a very colorful sheet and it goes all the way to the end, 
00:33 it colors it as the Pride in D1 and E. So not sort of actually replicate this, and that's what this video is about, we're gonna replicate this Pride flag.
00:47 It's gonna be an on edit, we're gonna do, it is Apps Script so get ready, but we're gonna do it from blank, from scratch.
00:54 This is a blank sheet, I just started it, it is completely white, we're gonna go up to a blank sheet, we have no formulas written yet, and we'll write them now, step by step.
01:07 So the first thing we're gonna do in our function, we're gonna use what's called a simple trigger, and that's the on edit, and we literally just write it like that, on edit, function on edit, we don't have to name it or anything.
01:20 And when we have it on edit, we have something called an event, but actually we can write anything we want here.
01:27 This is a, it is called the event, but we can say event, we can also write e, you might see that if you ever try to look for on edit an event appscripts online, you might see e, but this is just variable, it'll mean the event object but we'll write event for now, but you might see it 
01:46 other places, like e. We're going to grab, basically, what we need to replicate, or we can replicate this with any word, and then we're gonna create like a different coloring for pride.
02:01 It already exists, we don't have to write it, we don't have to make it, so we're gonna do something different, we're gonna just like write style, or like, better, right?
02:09 Let's do better. So, we need this to exist, P-E-T-T-E-R. We're gonna write better, from A1 to, F1. The last thing that someone edits that we need to then pop on, if you notice, if you just type in P, nothing happens, R, nothing happens, I, nothing happens, D, nothing happens, the moment 
02:34 you hit E, something happens. So, we want to, the event trigger, the thing that we're going to trigger this, event, we only care about the last letter.
02:45 We only care that it is an R in which one, let's do B, E, T, T, E, R. So we just care about F1 being edited.
02:57 But we do want to check if B, sorry, A, through E, are filled in with the appropriate letters. Okay, so we want to check for all that.
03:08 So the first thing we're going to do is we need our event. We need a variable, let's say, row. We need to know what row our event is happening.
03:16 Where is that edit happening? We're going to write equal sign, variable row, we're going to write, event.range.get row. We're going to call them, variable call, C-O-L, event.range.get.
03:31 Call. Now, if you don't remember how to do this at any point in the future, you're like, oh, I want to write this, but I can't remember exactly how to do this.
03:39 And like, you want to try it out instead of having to like, Look it up and get it right every single time.
03:44 I'll show you how to try this out. So what you can do is log, logar.log, and we're going to log the row, and we're going to log, logar.log.
03:54 And now what this allows us to do is every edit that happens, we can see what the row and column is if we got this correct.
04:01 This should be, something like this should be, let's see, b. We'll just edit. We go to our executions here and we'll see if, do we save?
04:15 I don't know if we saved. On edit, we should have some executions. Let's see. E-t-t-e-r. There's our execution. And we've failed.
04:30 What are we failing on? What is going on? And this always happens, right? We'll see what our failure is. We'll see what we've not logged or something.
04:37 We'll refresh that. Let's refresh. Right. Oh. I might have typed that in wrong. Get that supposed to be column. Great.
04:48 So, yeah, of course. Get call is not a function. Whoo! Okay. Thank gosh. We found that out. Let's do some more edits.
05:00 Let's do this edit again. And these are, Distinctly different edits. This B, this E, this T, T, E, and R.
05:10 And so we're gonna get, Oh, here we go. We got a completed one. We got a bunch of completed. All these edits are completed, right?
05:16 Let's refresh one and see what, which one we got. We'll have the row and the column. Takes a moment. Always.
05:24 One and six. So that was one, two, three, four, five, six. That was the R. And then we'll see one down below.
05:33 Oops. Just click on the header. One, five. Yeah. So each one of these is logging both the row and the column.
05:41 Great. So now we know the row and the column, We want to also know the sheet name but actually, no, we don't even need the sheet name at all for this particular case.
05:51 We want it in any sheet that happens. If we just write better, we're gonna get some coloring happening. Okay. So we're gonna write if row double equal sign is equal to, who it has to be one.
06:06 And we're gonna use two ampersands to be n. We want n, the row and the column to be a certain thing.
06:15 So call equals is equal to. And what is, I think it was six, is the r. Alright. We're gonna do six there.
06:23 And then we're gonna, have curly brackets here. And now we're gonna be able to execute something. Basically if if these two things don't happen, if the, if the row is not one or the column is not six, nothing inside these brackets will happen.
06:38 We can do something else if we want, in, in other cases. But in this particular example, we just want to, Create some background coloring when we have the letter R, right?
06:50 So we need to know what is the event value. And I think we can do this. I'll try to figure it out right now.
06:57 I think it's just value. So we want to call it, Is, I don't know if this is a, Good. Name is it R.
07:06 We're going to go event.value. I think that's all we need to write. But again, just in case we don't know, we can, Is it R?
07:15 We can log it. And then we can go R, E, T, T, and C if we logged it correctly. We might get a, Error.
07:25 Might. Let's see. We'll go to the last one. Oh, we just have to refresh. Refresh. There's a lot of them.
07:36 There we go. We got R. We got the actual letter that we want to see. What is the thing being edited?
07:43 What is the actual value that's going in? We got, Is it R? And so now we need to know, We also want, and, is, it R is equal to R.
07:55 We actually want lower case, I think. We don't, Ah, now I'm wondering, if it's uppercase, Is it still work? Still works!
08:08 Okay, so we might want to do that too. But in our case, let's just do lower case. Or actually, no, we can do this.
08:15 We want and, in parentheses, maybe. I think we can do it in parentheses. And, and then, do pipes. Is it R?
08:29 Is R? Or, R? I think that's gonna be OR. So it's gotta be this, and this, but one of these two.
08:37 Okay. But we also want we need to know, that, this is B, E, E, T, T, E. We need to know that those are there.
08:53 We can't just type in, obviously we can't just type in, E in the column E and it works, right? We need to know that the other, does it work?
09:03 Any other, nope, it's only, the first row. Oh, okay. So, we're right. It's the only first row we know the six and we need to know let's do this.
09:16 Let's do variable. I don't know if we want to call it values. What are we going to call it? An array, let's see, equal to spreadsheetapp.getactivesheet.getrange.
09:35 And we want row one. We want column one to start off with, but we number of rows one, but the number of columns, we want five columns.
09:46 The f, First five, right. Get values. And then what we want to do is we want to join those all together.
09:56 So they're, we're gonna get an array here. And you can see that if we just log it, right? Logger.log. We'll call it, Better array.
10:10 We'll, Betty. Actually, it is Betty. We'll call it Better array. Might want to just call it actually Betty. That's, that's actually much funnier.
10:19 Betty. And so we'll log Betty. And what you'll see now is that that is going to be a that is going to be, an array of whatever is in these first five columns.
10:33 But let's start doing, before we get back, we want to get this. But let's do something. Because we want to like see some action here, right?
10:40 We're ten minutes into this video. We're like, let's see something go on. And so we're going to do spreadsheet app.
10:47 Get active sheet. I think we can do it with active sheet. Get range a colon. I think we want a colon Z dot.
11:02 We don't want background set background. I think that's it. Set. Back on RGB. Mmm. And we can set any RGB we want.
11:18 What is the RGB for like the papa high risk that I like a lot? Like paper? One second. Actually, we want green.
11:27 I know. I know we want. I know we want. I want to want the green hex code for Google Sheets.
11:33 I actually have a blog post about this. I wonder if it's gonna come up. There it is. Here's the green color hex code.
11:40 I want the RGB 15. This is a 15. 157.88. That's what we want. Ooh. That's what we're gonna color it green.
11:50 Like this is very Google Sheets, right? And so if we right now, we don't have this bedding yet. So if we just type in R in column six, row one, I think it'll work.
12:03 So we can have like a T here. E. R. There we go. Whoa, we're very green. Whoo. And if we delete it, nothing happens.
12:17 We should probably do something if if it's deleted as well. So if is it R is the all is the new value?
12:28 But if the old. Value if you. It should turn white, but let's let's figure out that that later right now.
12:36 It works, but we need to make sure that we have Betty first Betty than the are.. Let's see. Okay. We need to have Betty before the are so we need to make sure that this bed.
12:49 Oh, we can. Actually save this. And let's look at the execution. See if that array. Came out correctly. Mm hmm.
13:02 We have a no. No means there's nothing there. Is it are? That's why it's no. And here's the array. So this in this particular case we had nothing.
13:18 Alright. Alright. What we're getting is that when we join this Betty together we're getting with commas but what we actually have to do is we have to do one more step Betty.
13:31 We need to take this Betty array. Ah, no, not one more step. Sorry. We just need to do zero. Bracket notation zero.
13:41 And now when we log this Betty we should be getting just a string with Betty. No commas. So let's test this out.
13:49 B-E-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T-T T-E. Right before we hit R, let's go back to our executions and see what happened. This might pop up some more.
14:03 Yeah, there we go. And now we have our Betty without commas. That's pretty cool. Alright. Last thing we need, To do now in order to make this appropriate, right?
14:15 We need to, We know the row, the last edit is on the correct row one, correct column six. It is R.
14:23 We know it's Betty, right? So we're gonna add another and, And we're gonna write Betty. Equals a string Betty. Now, if it does not, if one through six, one to five, sorry, it's not Betty, then the R won't work.
14:43 Ideally, right? Let's test this out, right? If we just put in R, before we put in R, it worked, right?
14:47 It went green, and it's not going green now. If it's a capital R, it's not going green. But if we do B, hit enter, oh, hit tab.
14:58 Nothing's happening, and then E, and let's see, R does it work? Hang on, it works! That's so exciting, right? So now, we made a, I don't think this is a better pride flight, but what it mean, what this, means is that we're now able to write sort of a Easter egg, create like an Easter egg inside of our
15:18 sheets, and turn everything green now. It doesn't work if we delete it. So let's see this. So if, let's see if, we'll take the whole thing again.
15:32 We'll copy it and we're gonna try to set this background back to normal or not normal. It won't be back to normal.
15:41 It will be we just want to turn it white. And they think RGB white is going to be just 25.
15:51 That's it. So we want here if bt equals, we don't care about that actually. We just care that it's the old value is r.
16:04 So we need to get variable, is it r now? Or is it not r actually? Is it not r? Is our event dot, I think it's old value.
16:19 We'll see what that is in a hot second. We'll do logger.log. Is it not r? And what we want to do, make sure is that that's blank right or anything.
16:34 Could be, we want the, well no, the old value is gonna be, was the old value r? That's all we care about.
16:42 And is the new value not r? It needs to be not r. So we can't edit it from r to r.
16:50 If we do that we don't want it to change. But if we want, is it r? The, The new value definitely we want it and to this is the not equal to r.
17:05 The new value needs to be not equal to r. Actually we need, we need to put this in parentheses as well.
17:12 And, or, and do, who want to make sure it's also neither r or capital r. We, if we capitalize it, we don't want it to change as well.
17:23 Ooh, this is exciting. So now let's look at, is it not r? Let's see if this old value actually works.
17:29 We're logging that old value. And we're saying well, first off, let's just, It's changing back as we type this hello.
17:39 Now the old value is nothing. Now I just deleted hello. So the old value should be hello. And let's make sure that that is the case.
17:49 We should be seeing here now new ones, new events. We're gonna wait, wait, wait. No? I think this is our old, was it the fifth one?
18:05 Was it the fifth log? Let's Let's do this old value. Hmmm, and we can make, just so we can get it right.
18:19 Let's do that again. Hello, world. And now we wanna do, kill hello and kill the world. There we go. Let's look at our old values.
18:30 Should be, not, this one, yeah, is not it. It'll pop up in a hot second. There we go. This is old value world.
18:39 Perfect. Alright, so our old value is right. So now, ideally, if we have got this correct, we have now if the row is 1, the column is 6, the letter that we type is either lowercase r or capital R.
18:57 And the other letter is in of a1 through e1 is Betty for better. And as long as that happens, we'll turn on the green.
19:08 Turn on the green full blast. Then if the, the edit we do is in the 1 and 6. And it's not our, we did not adding our in any way shape or form.
19:22 And in fact, is it not our, the past one we did was our. So if we're, if the past one.
19:28 Yeah, I think this will work. Let's see if, let's see if this will work. B-E-T-E-R. BAM! We got it. And now we do, I don't know if we delete.
19:45 It's gonna work. The new value needs to, did I screw that up? I think I screwed this up. The new value.
19:58 The value is not R, that's correct. And the old value is R. Is either R or R. I guess that doesn't matter what the new value, what the old value was.
20:13 Let's see if we can just delete that and it works. So BE. Well, B-E-T-E-R. Well, probably shouldn't have happened. Okay, we gotta figure this out.
20:29 Okay, I think I figured out what's going on. But if we do better, it'll definitely turn green and if we delete the R, it will turn white.
20:39 So that is good. That is very good. So now, if you are a better-seats member and you're watching this on betterseats.co, then you can definitely get this content for free.
20:49 And content. This Apps Script for free. You can get this sheet for free and I'm so happy you made it through the entire video.
20:56 And we got to create an on-edit sort of Easter. So this is a very big inside of a sheet just like the Pride Flag.
21:02 We can now sort of color our sheets in a very interesting way, sort of a personalized way. Really interesting way also to add this cool thing that might happen in your sheet.
21:14 If you are selling a sheet, if you are giving away sort of a lead magnet sheet. And you want to create a fun Easter egg for your own brand name or some kind of emotion.
21:26 Maybe you want a certain style if something happens along the way. Maybe you are creating an escape room. I have an escape room, a free escape room for everyone called Escape Spreadsheet Help.
21:36 Go check that out. You can find on better sheets. And it's absolutely free. Go learn some keyboard shortcuts. Bye.