How to Turn Background Color to Text

Intermediate

About this Tutorial

This tutorial showcases a practical solution to convert visual cues into actionable data in Google Sheets. It's a step-by-step guide on using Google Apps Script to automate the process of reading background colors and converting them into text. The focus is on simplifying data processing tasks and enhancing the utility of spreadsheets for data analysis.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:02 Hey, so we're gonna try to turn background color into text. I thought this would be an interesting process here because there is no formula to say what's the background color here. 0:13 And a lot of times when we're cleaning up data in sheets, we get data that looks like this where someone has gone by hand and colored in some array of data . 0:23 But we want actual, like, data from this.

We don't want it just to be visually pass or fail this bread for fail and green for pass, of course. 0:32 We're going to use Apps Script , haven't written Apps Script yet, so let's do that right now. First off, let's get the actual background color of these. 0:41 So this is, let's look at this. This is the heck code he- f- f- f- f- so we'll write that here as a note . 0:49 Variable red equals f- f- f- 0- 0- and then what's this green? Let's get this green. 0-3.

Just so we have these available. 1:05 To us, once we need them what we want to do is let's get the background color of, let's say C2, just to see that we can actually get the background color . 1:16 So variable vgcolor equals, I'm gonna do spreadsheet dot app, get active spreadsheet , get ring get sheet by name, I think it's just gonna be sheet one, sheet one, actually this we'll call this. 1:37 Sheet one, then we'll do, we're gonna get this out of the way we're gonna call bg.

Color equals sheet one dot get range , we want to just see two. 1:53 And I think we can get get background, I think. Let's see what that does. Let's go. Logger.log bgcolor. Go get bgcolor. 2:22 Let's just run this and see what happens. We're gonna have to have permissions, of course. What I like to do in this process is just sort of get this piece by piece instead of having to do the entire thing all at once. 2:36 And so what we do is we got bgcolor fff00. Let's double that as correct. And in our background color we sort of forgot this hashtag or hex code. 2:50 We now want c3.

And let's see what that looks like. We do this just to double check that we can. 2:57 Get this color, background color . CC. Let's change it to c3. Save it, run it, and hopefully yes. Well, would it. 3:14 Do we get the wrong hex code? C3. Let's see what that is. I guess we did get the wrong one. 3:23 That's good that we double checked. Or did I press the wrong button there? I don't know.

But that's good that we double checked the background color . 3:32 So now, what we can do is basically go through all this data , the C2 down to what we got into C, 347. 3:41 We got 347 grades to give people. All right. How do we do this? Well, we'll get the variable cells . That'll be an array of sheet1. getRange C2 colon actually in quotes C2 colon C3 4 7. 4:07 That's the cells . And actually we want, let's call this colors. GetRange . getValues . And what this is gonna look like is an array again. 4:19 Let's just log this and look at what it is again.

I like to do these logging just to see what we get. 4:30 There we go. We get nothing. Okay. Because we get with these our values . Oh, that's, that's actually a really good point. 4:37 We need to get each of these cells . We need to get the background color here. So we need to say git range . 4:44 Okay. Gotcha. But now we have, okay, we will call the cells and we will.

Say for we'll do a for loop here for I equals zero semi colon cell I is less than cells that link semi colon I plus plus we'll do. 5:07 For loop through the eyes and what we're going to do we're going to go cells I oh wait, this is going to get us the. 5:23 Value okay, so let's actually create variable colors equals it'll be a blink one and we'll say colors dot push. Shh. 5:39 A color, I think we need to put this in semi maybe not, maybe we do. Let's check.

Let's actually do this but for colors what are we gonna push the color up? 5:55 Of the color, variable color equals sheet one dot get range .

The first one is the row so it'll be one, no, two. 6:11 Two plus i because i is going to start as zero and go up and we want the second row second column because we are looking at, oh, c column so it's three column and we're only going to see one cell at a time so we're going to get this i range and we're going to dot get background and that is going to be 6:32 the color of that cell and then we're going to push it but we're just going to look at what this is at the end. 6:39 This should be an array of all the things we might not necessarily need it because we just need to basically set a text here maybe let's say pass fail here as well and we just need to set this text. 6:55 But let's look at this logger dot before we do our stuff we'll look at this logger dot log colors again i like to just look at these things in the log and see if we're getting everything correctly perfect so now we have all we now we can actually graph all of the colors but we don't actually need an  7:18 array of colors we just need to execute what we're doing so let's delete this let's delete colors we got color and now if color is equal to this red that is fail right so we are going to get this range so we're going to do sheet one dot get range two plus I the column we need is four because we're gonna 7:50 write that there four one one dot set value and the value we're gonna set is fail now underneath this if if Have a great day. 8:05 We're gonna actually do the exact same thing that if and instead of this color, we're gonna look look at that green if it's equal to that green, we're gonna say pass. 8:20 There is something the matter.

Oh, I see. We didn't need elses, we just need another quote here. We don't need these elses. 8:54 We just need a couple of ifs because if neither of these are true, it just won't do anything. We just don't care. 9:00 We only care if it's one of these two colors. So now this should, based on just running this once, we are going to be able to put pass fail into each of the d column where it is here. 9:13 So let's see if it works. There we go. Fail pass, fail pass, fail pass. Perfect.

We only need to do this once and we are done with our Let's just double check that it's working correctly, right? 9:28 We're already on 50. Here we go. We're almost at 100 now. We're past 200 and here we go. We are at the final all passes. 9:38 We've now graded</div>