0:01 Alright, BetterSheet members, you probably already know this. I might have shown this on TikTok. But here for you, I want to show you something cool and something interesting that you can build yourself.
0:12 So, you can type in P-R-I-D-E and your Google Sheet will turn into colors. Okay, here's the thing that I just found out today before we get into this lesson, because I'm gonna show you how to create sort of a variation.
0:33 But before that, I want to show you something I learned today, which is you can also type in o, I'm gonna try to remember this, o-r-g-u, g-u-l-l-o.
0:48 And it will do the same thing. Okay, so it's doing something weird right now because I have some conditional formatting on, but like, here, let's do a new sheet sheet.new, and I'm gonna show you this, so again, pride, p-r-i-d, but also, o-r-g-u-l-l-o also does it, same exact thing, because this is like
1:08 a Spanish translation, and I think the Portuguese translation with the H o-r-g-u-l-h-o also works, isn't that crazy? Like, oh my god, I just found out about the different languages today, and not all languages translations of Pride or Proud works, but those three are what I found today.
1:31 Okay, but, I want to show you, in this video, how to create your own version of this. Essentially let's go over to one where I got it correct, and let's type in N-I-H, N-I-G-H-T, night.
1:48 Let's do a dark mode, and you can see right here, here's the answer, here's con- con- conditional formatting to do this.
1:54 But I want to show you what you might be getting wrong if you think through this and you try to do this yourself.
1:59 So what you might think is it's conditional formatting that looks like this, where you make sure that the N is in A1, uhm, we can look here, we've applied a range, we can say custom formula is, and we type in equals dollar sign A dollar sign 1 equals in quotes N, and we set the apply to range to everything
2:22 , and we set the background to black and the text color to white, hit done, and you think like this might be it, and you're like okay, now if I type in N, oh it happened right away, just N, not N I H, N I G H T, so how do you make sure that the N is in the right place, the I is in the right place, the
2:46 G is in the right place, the H is in the right place, the T is in the right place, and that when you delete them all, or any of them, it also goes away.
2:53 So this is definitely conditional formatting, but I'm going to show you Apps Script. I want to show this to you because I think Apps Script is really cool here this application.
3:02 So I get N, I, G, H, T, boom. It, it takes a moment because it's, it's Apps Script, and what we're doing here.
3:12 And again, actually, so when you look down below at the video down not at the video, at the sheet down below, you're going to get this Apps Script version and, and I'll share this with you down below for free.
3:26 So, so as a BetterSheets member you can grab the video not the video, the sheet down below. Okay, function onEdit, and we have an E in there.
3:35 We're going to get the variable row and variable column with E.range.getRow and E.range.getColumn. We're logging here because I had some issues with it and I was logging, but you don't need to outlog it.
3:45 It works. Uhm, the, the active is just SpreadsheetApp.getActiveSpreadsheet.getSheetByName. So actually, I just named the sheet so we can actually rename this active.
3:56 It's not active. It's actually just Sheet1. So I'm going to just rename it. Rename this Sheet1. And all of these actives, all the way down here, I'm going to rename Sheet1.
4:09 Okay, so that's a little bit of change for you there. So it's not actually the active sheet. It's just Sheet1.
4:14 And we name it here. And then we get the nRange, the iRange, the gRange, the hRange, the tRange. We get them all and we get the value of each one.
4:22 So we are grabbing Sheet1.getRange. We're saying row1, column1, row1, column2, row1, column3, row1, column4, row1, column5, and we get the values of all these.
4:36 And then we say if the row is 1, if the row that we're editing is row1, and column is 5, that's two things, and then each of these are true, that the, there's the n, the a, that's the n, the i, the g, the h, the t, that these are all good.
4:52 Then, what we're gonna do is we're gonna get the maximum rows, get the maximum columns, and then just paint everything page.setBackground to black, page.setFontColor to white.
5:03 That's it. So, that's the Apps Script. Apps Script is that if you delete it, it does not go away. Uhm, yet you have to like code that part yourself of like, hey, if there's an edit to this and none of these are correct, then just paint it, paint something or back to default.
5:33 But let's see, okay, so again. With Conditional Formatting, you might be doing this where you have Conditional Formatting of the N, the I, the G, the H, the T, all separate.
5:43 Totally wrong. Like, I tried that and I was like, okay, this is not working. But here we go, we have the answer.
5:50 And what it is, is it's same custom form formula, but we're using the AND formula. So, equals AND, and then in the parentheses, we're doing all of the stuff we did before.
5:59 So, the A1 is N, and then a comma, B1 equals I, and we're making sure that we're using dollar signs in front of the A and the 1, B1, all of the stuff so that it stays the same throughout, through the entire range.
6:15 We need to use those dollar signs. Those dollar signs are so important. And we're putting all of them all in one AND formula.
6:22 A little issue that I ran into that you might run into yourself is I use single quotations first and that didn't work.
6:30 Only double quotations for some reason. So a lot of like little things that add up to you have to get this right.
6:36 You have to use the AND formula. You have to put an equal sign at the beginning. You have to use dollar signs in front of the ah column and the ah rows.
6:44 And you have to use double quotation marks, quotations around the ah and the I. I don't know, I don't, let's double check.
6:53 I haven't checked this yet if it works with capital. N-I-G-H-T. It does. Okay. So that was like the one thing that I was going to try to do is like, oh if it didn't work with a capital we would have to do something else.
7:07 But it works. So we can change ah our sheet to night mode just by t- typing in N-I-G-H-T. And I think it was really cool.
7:15 And now the world is your oyster. You can go and create any kind of cool Easter eggs in your sheets that you want with this conditional formatting.
7:23 Let me know also if you do something cool with this. If you put it into a sheet, let me know.
7:27 I'm happy to hear from you. And also, ask any questions. I'm happy to answer them. Hopefully this was helpful to you and fun to explore in Google Sheets.