Brighten Up Your Checklist Automatically
Learn how to automatically brighten up your checklist in Google Sheets using Apps Script. This video guides you through the process of modifying background colors based on RGB values triggered by edits in your sheet.
In a 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. But in this video, 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 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 . 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 year. We have A through H, and 1 through 25.
We can absolutely hard code that, right? Get range , and we're gonna start at 1 comma 1, which is 1, row 1, column 1, and then how many Rows are there, what did we just say, 25? 25 rows, and how many columns? You can see here, number of columns. H is gonna be, do this, forget 8. That's the number 8. 8 here. And now this is the range that it will work. This tech this Apps 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 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. We can also programmatically look, do max columns, max rows, which we might do later. We're going to get the values of all of that range.
We're going for each of the values . We're going to go to the cell. And get the row and the column and change, 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. So from, 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. So 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. And let's look at it. We're going to hit this custom color. A, the R is 251. So we want 251. We want 188 for green. 188 for green. And 4 for blue. And what we're doing here is math. max. Meaning if we go go through. We get the RGB over the existing color.
Then we're gonna minus some number . We don't want to do minus one. We did before because we wanted a very slowly over time make the changes. But we want actually these changes to happen relatively quickly. We're gonna do 10 X. So minus 10. So if it's 2 55 then it's gonna go down to 2 50. 245, and it's going to then compare 4 to 255 and get the max. 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 4. 251, 188, and four if it's under each of those. 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. Let's go up and just change Brighton background and click run.
See if we get any errors. We do have to review permissions. 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. Alright, it is brightening. And there it goes. It's going through every single cell, brightening it up. By 10 each RGB. Let it let's let it work. Okay, that's done, but I don't really wanna be coming in here and clicking run every single time and now it's gonna do it again. I don't really wanna do that. I wanna have it actually be triggered when I do an edit on this A column. So let's look at what we can do here while that's running. Let's do function on, function on edit and we're gonna run brighten Brighten background. 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 source, get active sheet , got the sheet name , active sheet , do get name, and then I got the column number 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 you hear sheet name is different and the column is equal to one, 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. Just brightening up the entire page. Cell by cell by cell. We can actually try to quickly do this to see if this top one is gonna go past that yellow. 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, it's just going and yeah, it's gonna stop right at
that orange and not go any further. I don't know if you can tell the top one is not changing, but every time this trigger is triggered, it's gonna stop the old one and go through it. So we have this sort of shading, which is pretty cool. If you do your checklist pretty fast, it's gonna look like this. But if you do your checklist pretty slow, it's gonna look all orange all the time. I hope you enjoyed this. Brighten up your sheets. If you're a Google Sheet, if you're a Google Sheet user, you should be brightening up your sheets. If you're a BetterSheets member, you can get this sheet for free at BetterSheets. co.