Google-sheets – How to get a cell to change its value based on another cell’s color

conditional formattinggoogle sheets

I have three colors in column F: green, grey and red. What I want is for column G to display a number (1, 2, 3) depending on the color value of F.

Is there a way to do this?

Best Answer

Why

The cosmetic features of cells like width, font, color, and even number formatting are not themselves considered data by Google Sheets. The cells shown here are all set to the value 0.5, and both by what was typed into them and according to Sheets formulas, they are all identical:

Screenshot of Sheets range with eight adjacent cells all set to value 0.5 but formatted differently to demonstrate how different identical data can look. In order: zero period five stacked vertically ; 12:00:00 which is half a day in hours ; $0.50 ; 50.00% vertically top justified ; a datetime in 1899 with green outlines ; brown 0.5 in a horrible font ; giant 0.5 cropped by tiny cell, 5E-01

In the future, always carefully consider if you really need to use anything outside the best practices of "normalized spreadsheet" usage; be it color-is-data, bi-directional data flow, spreadsheet-as-a-database-system, transactional writes, script onEdit dependence, or any other attractive divergence into bad plans.

Here, since those features are not data, we cannot natively use them as a data source in formulas. Achieving the new functionality the question described will therefore require extending the existing features of Sheets by way of a third-party app, an add-on, or Google Apps Script.

I've chosen, though don't normally recommend, Apps Script. If you're new to Apps Script, there are endless tutorials to follow. Two notes though: for one, Sheets has changed over the years so things will look the same if it's a more recent guide; and two, scripts copy-pasted from the web could potentially be malicious, accidentally destroy your data, or steal your file's info. With scripts, but even with Google-ecosystem Add-ons, you should ensure you understand what you're about to run.

How

That out of the way, you can choose Script Editor from the Tools menu, erase the starter function, enter some code, save it, name the project when prompted, and lastly when the script tries to access your Sheets file, click through (set, your account, advanced, allow, …) to grant it permission to run when prompted.

On to my example script. First a bit of convenience:

function onOpen() {SpreadsheetApp.getUi().createMenu("Scripts").addItem("Number by Colors", "numberColors").addToUi();}

To run the rest of the code on demand right from the Sheets file, I opted to have the script add a menu to the Sheets interface. If you put the above in your script and save it, a new "Scripts" menu will be available in that Sheets file every time you open or reload the Sheets page (or to make it appear right away, choose Run→ Run Function→ onOpen from the script editor menus).

Here is a sample Sheets file to try it all out, to experiment, or before using it on real data. The script is included, and some colors to be turned to numbers. You'll still need to open the Script Editor once, run onOpen, and grant permissions as above.

What

The following should populate column G according to the background coloration of cells in column F. I'm not sure exactly which colors you used, so I chose the most-pale green and red, and "light gray 1", for their subtlety.

function numberColors() {
  // ⬇ script 'Settings' below. UPDATE IF RESTRUCTURING OR RECOLORING SHEET! ⬇

  var colorCol = 6,     // 6 is F
      numberCol = 7,    // 7 is G
      firstDataRow = 2, // first row in which to begin color checks (to skip headers)
      palette = [
        "#ffffff",      // white (or your spreadsheet default)
        "#d9ead3",      // light green 3
        "#d9d9d9",      // light gray 1
        "#f4cccc"       // light red 3
      ];

  // ⬆ script 'Settings' above. UPDATE IF RESTRUCTURING OR RECOLORING SHEET! ⬆

  var numbers = [],
      activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(),
      dataNumRows = activeSheet.getMaxRows()-(firstDataRow-1),
      colorColColors = activeSheet.getRange(2, colorCol, dataNumRows, 1).getBackgrounds();
  for (var rowIndex in colorColColors) {
    numbers[rowIndex] = [];
    var rowColor = colorColColors[rowIndex].toString(),
        colorIndex = palette.indexOf(rowColor);
    if (colorIndex == -1) {
      numbers[rowIndex].push(rowColor); // custom output when cell color is missing from script palette.
    } else if (colorIndex == 0) {
      numbers[rowIndex].push(""); // custom output for first palette color (spreadsheet default). "" means blank.
    } else {
      numbers[rowIndex].push(colorIndex);
    }
  }
  activeSheet.getRange(2, numberCol, dataNumRows, 1).setValues(numbers);
}

Huh?

Roughly restated in English:

  1. Create a custom function 'numberColors' to encompass all this and be activated by the Scripts menu.
  2. A // line, which means a comment. You'll see these all over and I won't mention them again. Even mid-line, from any // to the end of that line is just a note you can edit to best help you, or just remove.
  3. Define a bunch of variables that depend on the setup of your Sheets file. These should be edited to suit!
    1. the Sheets column with background colors to check
    2. the column of numbers to be overwritten
    3. the row in which your data starts—e.g. if you have four rows of headers, change the firstDataRow assignment to 5.
    4. the ordered list of colors from your question, but as hex color codes, and with your assumed default color white inserted as "color 0". Note that order matters here—e.g. list index 2, the 2nd color after white, will be numbered 2, so I entered the hex color code for gray. Also note the # symbols, quote marks, comma separation, and the setup lines before and after the list—these are important for the script to capture your array of color codes.
  4. Set up a bunch of variables you shouldn't edit. They automatically gather needed info based on the state of things when you run the script.
    1. an empty output list to later hold the array of numbers we come up with
    2. the sheet you're on
    3. the number of potentially-colored cells (total rows minus header rows)
    4. an input list of the current colors from the actual Sheet
  5. Then, once for each of the colors in that input list,
    1. Add an empty slot in the output list to keep track of another number (1, 2, or 3).
    2. Turn the color found into plain text of the color's hex code, like the palette colors.
    3. Finally check where that color is in your color-number mapping list.
    4. If it's not in your list of [white,] green, gray, red; then we have a new color in the Sheets file! Rather than a 1-3 number, save this mystery color's hex code to the list, so the Sheets user can fix the color, or you can add it or update it in the script.
    5. Otherwise, if the color is your default Sheets color (white?), we don't want to output "0" so we save a custom output value to the list instead, nothing, which is written as "" in Sheets.
    6. Otherwise, (and last but most common?), if it wasn't missing from the palette, and wasn't blank, it must be a known color that was found somewhere in the palette list, so save that "palatte placement-spot number" to the numbers output list.
  6. Done with the colors, we have our array of numbers too. Overwrite the actual number column in the Sheets file with our numbers list.
Related Topic