Brighten Up Your Checklist Automatically

Intermediate

About this Tutorial

Create a cool function that brightens your sheet background from white to some specific color a little bit each time you check off a to-do item.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 So in the previous video, I shared with you how to whiten your sheets, meaning bring the text and the background both to white from wherever they are, over the course of, say, some triggered events or some amount of time with triggers. 0:15 But in this video, I'm going to show you how to whiten your sheets.

I'm going to share with you another idea, which is you can brighten up your checklist and I'm going to change the code in this video so you can see how I edit the code from whiten your checklist to brighten up your sheets if you want 0:35 to take a white background and just increase the RGB until it's some color and then keep it at that color. How do we do that? Well, the first thing is we have this range of get data range . 0:42 We actually want to make sure that that's the entire sheet. We don't want just the data range .

So I'm going to look at this sheet. 0:48 We have a through h and one through 25. We can absolutely hard code that, right? Get range and we're going to start at one comma one, which is one row. 1:05 One column one and then how many rows are there? What do we just say? 25. 25 rows and how many columns? 1:14 You can see here a number of columns. H is going to do this if we forget eight. That's the number eight. 1:23 And now this is the range that it will work, this app script .

If you are unfamiliar with this app script , I will walk through it very quickly here, but basically it's just going to take all the sheets, the active sheet you're on. 1:37 For every single sheet that there is in the spreadsheet file, it will look at the range . In our case, we only have one sheet, so I'm setting the range to this one sheet. 1:47 We can also programmatically look, do max columns, max rows, which we might do later. We're going to get the values . 1:55 We're going for each of the values .

We're going to go to the cell and get the row in the column and get the background color , and then we're going to morph that background color in RGB, then we're going to set the background to whatever that morph is. 2:09 For us, we have a we know we're setting it at white, 255, for all red, green, and blue. But we want to get to a point where it's at some color and then it'll stay that color. 2:25 Let's pick a color now. I'm actually going to pick sort of a, sort of a random color. Let's pick this yellow. 2:31 And when let's look at it, we're going to hit this custom color.

The R is 251, so we want 251. 2:38 We want 188 for green. And four for blue. And what we're doing here is math.max, meaning if we go through we get the RGB of the existing color, then we're going to minus some number . 2:56 We don't want to do minus one. We did before because we wanted to very slowly over time make the changes, but we want actually these changes to happen relatively quickly. 3:06 We're going to do 10x, so minus 10. So if it's 255 then it's going to go down to 250.

245, and it's going to then compare 4 to 255 and get the max. 3:20 This math.max just looks at these two numbers and grabs the maximum. That means that if we get to 251, 188, and 4, and we try to minus 10 it will always pick 251, 188, and 4 if it's under each of those. 3:37 So let's save this and actually run this a little bit. So, just to double check that it is correct. It is working okay. 3:48 Let's go up and just change, brighten background and click run. See if we get any errors. We do have to review permissions. 3:58 We will have to review permissions.

Let's go through that process every single time. Not every single time, sorry. The first time you run this code, I just created this code. 4:08 All right, it is brightening. And there it goes. It's going through every single cell, brightening it up by 10 each RGB. 4:16 Let it, let's let it work. Okay, that's done. But I don't really want to be coming in here and clicking run every single time and now it's going to do it again. 4:25 I don't really want to do that. I want to have it actually be triggered when I do an edit on this A column. 4:33 So let's look at what we can do.

Here, while that's running, let's do function on edit and we're going to run Brighton background. 4:48 So we're going to run when there's an edit, but we don't want that edit.

To be anything, we want variable sheet equals this event dot source dot, and so what I did is I got the active sheet , e dot source dot get active sheet , got the sheet name active sheet dot get name, and then I got the column number 5:13 of whatever the edit is, this event, this e is an event, and I said if the sheet name is equal to sheet one, which you can change if your sheet name is different, and the column is equal to one. 5:23 Then brighten background, so we're only going to be brightening the background when and if we edit this a column, so let me Click here, and I think it'll just start right away. 5:34 Just brightening up the entire page cell by cell by cell, one cell at a time.

We can actually try to quickly do this to see if this top one is going to go past that yellow. 5:50 Let's, we might need to do even more than ten at a time to get it. That fast, let's see. So each one is just going and yeah, it's going to stop right at that orange and not go any further. 6:06 I don't know if you can tell the top one is not changing, but every time this trigger is trying. Triggered it's going to stop. 6:12 They'll all want to go through it. So we have this sort of shading, which is pretty cool.

If you do your checklist pretty fast, it's going to look like this. 6:18 But if you do your checklist pretty slow, it's going to look all orange all the time. I hope you enjoyed this. 6:24 Brighten up your sheets. If you're a Google sheet, if you're a Google sheet user. You should be brightening up your sheets. 6:30 If you're a Better Sheets member, you can get this sheet for free at BetterSheets.co</div>