Google Sheets – Change Cell Background Color Based on Hex Value

google sheetsgoogle-apps-script

So I've been looking online and I am not able to find an answer that is working for me. I have a Google Sheet, and some of the cells within that sheet have hex values in them. What I am trying to do is make it so that the cell background changes to the hex value within that cell, and if need be the font will inverse that color so it's always readable.

I checked
How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?
that link which kind of does what I'm looking for but it appears to do the whole range I'm working on, not individual cells.

Here is my code, which like I said isn't doing quite what I want. Seems close, but not there yet.

function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Color Groups")
    var range = sheet.getRange("A2:G1000");
    range.sort({column: 2, ascending: true});

    var color = e.value;
    var newRange = sheet.getRange("C2:G1000");
    newRange.offset(0, 1).setBackground(color);
}

Any help is appreciated, kinda new to scripts within Google Sheets.

Best Answer

I created a sheet called colors and with the code below it will automatically change the background color from #ff00ff format. I included comments for you to understand, but it should be strait forward.

function onEdit(e) {
  r = e.range;

  if(r.getSheet().getSheetName() == "colors"){ //the sheet I want to apply this to is called colors

    var rows = r.getNumRows();
    var columns = r.getNumColumns();
    var colors = [] //this is our 2 dimensional array of colors in case you copy and paste a large amount of colors

    for (var i = 1; i <= rows; i++) { //go down each row
      var row = [] //create a new row array to clear the old one when we go down a row
      for (var j = 1; j <= columns; j++) { //then go across each column
        row.push(r.getCell(i,j).getValue()) //put together the row of colors
      }
      colors.push(row); //insert our row of colors so we can go down the next row
    }
    r.setBackgrounds(colors) //batch update in case you update many colors in one copy and paste otherwise it will be very slow
  }
}

https://developers.google.com/apps-script/guides/support/best-practices

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Max & Color', 'getmaxColor').addToUi();
}

function getmaxColor() {
  var ss = SpreadsheetApp.getActive(), aRange = ss.getActiveRange(); 
  var data = aRange.getValues(), colors = aRange.getBackgrounds();
  var dest = aRange.offset(0, data[0].length, 1, 1);
  var index = getmaxIndices(data);
  var max = data[index.i][index.j], color = colors[index.i][index.j];
  dest.setValue(max).setBackground(color);  
}

function onEdit() {
  var ss = SpreadsheetApp.getActive(), col = ss.getActiveCell().getColumn();
  if(col < 7) {
    var aRange = ss.getRange("A1:F7"), data = aRange.getValues();
    var colors = aRange.getBackgrounds();
    var dest = aRange.offset(0, data[0].length, 1, 1)
    var index = getmaxIndices(data);
    var max = data[index.i][index.j], color = colors[index.i][index.j];
    dest.setValue(max).setBackground(color); 
  }
}

function getmaxIndices(data) {
  var max = 0, indexI = 0, indexJ = 0;
  for(var i=0, iLen=data.length; i<iLen; i++) {
    for(var j=0, jLen=data[0].length; j<jLen; j++) {
      if(data[i][j] > max) {
        max = data[i][j], indexI = i, indexJ = j;
      }
    }
  }
  return {"i":indexI, "j":indexJ};
}

Explained

The onOpen() adds a menu item. Select the range you want to evaluate and select the menu option Max & Color. The values and backgrounds are collected as is an offset range. Then the indices (2d-array) of the max value are obtained, through the getmaxIndices(). Afterwards the corresponding max value and color is set.

The onEdit() does almost the same, but it has a fixed range. It will only fire if a cell is edited and if the edited cell belongs to the A1:F7 range.

Note

The reason for getting the error message, can be read in this answer: custom function

Example

I've added the sample code into your example file.

Google-sheets – How to change the background color of a cell based on the cell contents using a color lookup sheet

Without a script

You may have thousands of words, but it's not practical to have thousands of colors in your spreadsheet, unless you are writing a color vision test. Most people can't reliably tell the difference between subtly different colors, especially on a computer screen. So, if you use a reasonable number of colors (e.g. 50) and group the words on Sheet 2 in 50 columns accordingly, then 50 conditional rules would do the job: each would be a custom formula like

=match(A1, indirect("Sheet2!C1:C"), 0)

where A1 is the upper-left corner of the range to be formatted on Sheet1, and C is one of the word columns on Sheet2.

With a script

If you insist on having thousands of colors, then a script below can do the job. It does the following:

Runs on every edit
Checks that the edit is to Sheet1
Grabs the data from Sheet2
Looks until either there is a match or the table ends.
Sets cell background (white if no match).

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Sheet1') {
    var values = e.source.getSheetByName('Sheet2').getRange('A:B').getValues();
    var color = '#ffffff';
    var i = 0;
    while (i < values.length && values[i][0]) {
      if (values[i][0] === e.value) {
        color = values[i][1];
        break;
      }
      i++;
    }
    e.range.setBackground(color);
  }
}