Coloring Google Sheets Cells from Color Values in Other Cells

by Dec 28, 2019Google

I was recently working on an LED-lighting project, part of which involved mapping a single command value to a color. The code for this project was based on the Adafruit NeoPixel library, specifically the wheel() function used in various NeoPixel example sketches. The function is fairly simple. As an argument, the wheel function takes a value between 0 and 255. From that value, the function returns a color that can be used to set an RGB LED to a particular color.

// Input a value 0 to 255 to get a color value.
// The colours are a transition r - g - b - back to r.
uint32_t Wheel(byte WheelPos) {
  WheelPos = 255 - WheelPos;
  if(WheelPos < 85) {
  	return Color(255 - WheelPos * 3, 0, WheelPos * 3);
  }
  else if(WheelPos < 170) {
  	WheelPos -= 85;
  	return Color(0, WheelPos * 3, 255 - WheelPos * 3);
  }
  else {
  	WheelPos -= 170;
  	return Color(WheelPos * 3, 255 - WheelPos * 3, 0);
  }
}

The value passed to the wheel function could come from a Serial command as it does for my project, or it could come from some kind of sensor value. Anyway, the issue I ran into is that it is a bit difficult to visualize colors just from numeric values. This would be the same case for typical RGB color values. Even hexadecimal color values can be hard to picture unless you spend a lot of time working with hex colors. So, I decided to create a spreadsheet using Google Sheets so that I could have a quick reference for the color corresponding to command values. This is a great tool for debugging code and adding new functionality.

There is no built-in function for coloring cells.

However, as I began putting the spreadsheet together, I found that there is, in fact, no built-in tool for setting a cell’s fill color based on the value in a different cell. The only way to create a group of cells that update their color based on the value of a different cell is by using the Script Editor. Google Sheets obviously includes a large number of built-in functions that can be written into formulas directly into cells. You likely use this functions almost every time you create a spreadsheet. However, for more advanced functionality, the Script Editor tool allows custom functions to be created and run on specific cells.

Set up the Spreadsheet

Before we dive into the Script Editor, we first need to set up all the color values that will be used to fill in cells after the script is complete. I created a spreadsheet with colors expressed in various ways. The first column has color values used in the Wheel() function from the Adafruit Neopixel library. These values are simply integers between 0 and 255.

The next three columns have red, green, and blue values, respectively. Each of these columns also contain integers between 0 and 255. The values in these cells are calculated from the first column using the logic from the Wheel() function.

The final four columns contain the hexadecimal colors values. The first three have the hexadecimal values for the individual red, green, and blue color values. These columns are calculated by simply converting the integer red, green, and blue color values to hexadecimal. The final column contains the full hexadecimal color value, calculated by concatenating the individual hexadecimal color values.

Create the Script

Now that we have a spreadsheet populated with color values, we can write the script that will fill the cells to the right of the values with the color in the row. First, open the Script Editor under the Tools menu.

First off, we will create a function. There are a couple different ways to run scripts created using the Script Editor tool. In this case, we will directly input the range of cells to modify with the function directly into the function itself. It is also possible to run a script on an entire spreadsheet, or on a group of selected cells. Anyway, in this case, we will create a function called CellFillRGB().

function setColorHEX() {
	// ...
}

Next we will add a couple lines to select the cells that will be targeted by the function when it runs. This involves two lines. First, the function gets the active spreadsheet and stores it into a variable. Then, the method getRange() is used to target a range of cells.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getRange("I2:I256");
With the group of cells selected, the function will loop through the selection. This is accomplished by a simple for loop. For each pass through the loop, the function accomplishes three things. First, out of the range of cells in the range function, the function targets one cell that will display the fill color, and a source cell containing the color value. In this case, we will use the hexadecimal color values. Last, using the setBackground() method, the function changes the background color of the target cell.
for(var i = 2; i <= 256; i++) {
  var targetCell = ss.getActiveSheet().getRange("I" + i);
  var sourceCell = ss.getActiveSheet().getRange("H" + i);
  targetCell.setBackground("#" + sourceCell.getValue());
}

Results

Here’s the full function.

function setColorHEX() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange("I2:I256");
  for(var i = 2; i <= 256; i++) {
    var targetCell = ss.getActiveSheet().getRange("I" + i);
    var sourceCell = ss.getActiveSheet().getRange("H" + i);
    targetCell.setBackground("#" + sourceCell.getValue());
  }
}

Once you’re finished with the script, run it by pressing the play button at the top of the screen.

When the script runs, it will gradually fill the column of cells specified in the function with the color values in the hexadecimal values column.

Share This