How to Change Dropdown Colors and Gray Out Rows in Google Sheets with Apps Script

0:00 you're trying to change your dropdown color and when it changes text, it's very simple to do this in a dropdown . But let's say we have something outside of the dropdown that we wanna gray out that entire row or that cell, or we want to do something other than change the color that it's set to. So let's first set a color in our dropdown menu, which we'll go here to our dropdown menu, edit button. And let's say we say want completed here in progress, is yellow not started as red? And we're gonna click done. And that's gonna apply to all of them. So now they're colored, right? But when I click done here, or maybe disregard or some other thing outside of these statuses, I want it to affect this. It still has the color right? So let's do some app script to both get rid of this data validation ,

0:49 keep the value, but also gray out or do something else here. So let's go up to Extensions app script . Once there we can write this function spreadsheet app, get active sheet , get range , and we're just gonna test it on two comma two and it's clear data validations. So let's run this and see what it does. It's gonna do this to the active sheet , the B two, and there it goes. So that clear data validations allows us to get rid of the data validation that's that color, but still leave the value. Okay? So let's do some other stuff here. We want an function on edit. And put an E here. That's the event. That means we're gonna get some interesting information, like the row

1:36 we're on from this E range do get row. We know what column we're on equals E range . Do get column. And if f the row that we're on is greater than one double amp, percent for, and the column we're on is equal to. Three. That means this checkbox here, we're gonna do something. We're gonna do well. We need to also say the value. We need to know what the value is. Value equals e value. And if that's true. Meaning checked. Then we want to use this, which is spreadsheet app dot get active sheet , get range , the row we're on. You need that not capitalized row and the column. Clear data .

2:21 Validations, not clear content . Data validations. There we go. So now if we're editing here, it's going to just take away the row and the column we're on. Actually the root column needs to be column minus one. Almost forgot that. Let's save that and check if that is gonna be true. I think we need to change this to true, not the text. True. Oh, it needs to be all caps. True. That's what it is. There we go. So now it's clearing that data validation . Let's do one more thing, which is gray out this entire row . So once we get rid of that data validation , we're going to also do spreadsheet app duct, get active sheet get range . We're gonna be on the row that we. Are already on. We're going to start in column one, and the number of rows is only gonna

3:10 be one, but the number of columns is going to be this active sheet . Get max columns. There we go. Spreadsheet app, get active sheet . Do get max columns, and we're gonna go the full length of the columns. Set background. And the background we're going to set is DDDD, DD sixties, I think. Let's save that and see what we got. So we're going to take away all those and let's try this one. I think we need six D's there and we had seven. Let's try again. There's, there is our graying. We can also change the text if we want the text color to be changed. But for right now, we just want it all gray to say we're done or disregard, or.

3:56 Allow, or we can change this to even bright green if it's like an approval process. It's like maybe these are the statuses until we get approved and there you go. You can change the dropdown chip color and even gray it out when you're doing something else other than just that dropdown menu. You can see all of the script here, and I'll provide the script in a sheet down below. You can get absolutely for free. 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

4:43 be your next Google sheet.