Randomly assign cell background color based on unique cell texts

Question

I'm looking for some conditional formatting solution where a new cell background color is set to a unique cell text. The image below shows this.

The tricky part is that I won't know any of the values within the list beforehand.

Hindgut

So if a new text is introduced in a cell - you want a new color for it, but if a text already exists in another cell, you want to assign to the new cell the same color? It is possible with Apps Script in combination with an onEdit trigger. – Hesperus

@Hesperus You described it exactly - better than I did hah. Any other specific tips/recommendations on how this can be accomplished? – Hindgut

Answer 1

How to assign a cell a unique color onEdit

• Apps Script onEdit trigger run your function automatically each time a (manual!) edit was performed in your script
• You onEdit function should compare your new entry (the last row of a predefined column) against the already existing values in the column with indexOf()
• If the value already exists - use getBackground() to retrieve the background color of the corresponding cell and assign it to the new cell with setBackground(color)
• If the value does not exist yet:
  • use a function to generate a random color
  • check with indexOf either this color is not contained in the already present background colors
  • build a new conditional rule assigning the new color to the new text
• If the value already exists - it will be automatically assigned the correct background color by the already present rules.

Sample:

function onEdit(e) {
  if(e.range.getColumn()==1){
    var text = e.value;
    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    var range = sheet.getRange(1,1,sheet.getLastRow(),1);
    var values = range.getValues();
    var array = [];
    var row = e.range.getRow();
    for (var i =0; i <values.length; i++){
      if(row!=(i+1))
      {
        array.push(values[i][0]);
      }
    }
    if(array.indexOf(text)==-1){
      var backgrounds = range.getBackgrounds();
      var color = getRandomColor();
      while(backgrounds.indexOf(color)>-1){
        color = getRandomColor();
      }
      buildConditionalFormatting(text, color)
    }
  } 
}

function getRandomColor() {
  var letters = '0123456789abcdef';
  var color = '#';
  for (var i = 0; i < 6; i++) {
    color += letters[Math.floor(Math.random() * 16)];
  }
  return color;
}


function buildConditionalFormatting(text, color){
  var sheet = SpreadsheetApp.getActiveSheet();
  var formattingRange = sheet.getRange("A:A");
  var rule = SpreadsheetApp.newConditionalFormatRule()
  .whenTextEqualTo(text)
  .setBackground(color)
  .setRanges([formattingRange])
  .build();
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);

}

Hesperus

Hi @ziganotschka. Thanks for the detailed explanation for the script! I'm trying to expand it to several columns but no luck so far. I did the following changes to include column B as test: if(e.range.getColumn()==2) var range = sheet.getRange(1,1,sheet.getLastRow(),2); var formattingRange = sheet.getRange("A:B"); Why isn't working? – Somali

I found a way with these changes: if(e.range.getColumn()==1 || e.range.getColumn()==2) var range = sheet.getRange(1,1,sheet.getLastRow(),2); var formattingRange = sheet.getRange("A:B"); . Thanks again. I'll try to figure out next how to make is accept any column. – Somali

Could you explain rapidly how to use this script I do not get it. I can save it in my App Scripts and it will take only column 1 if I understood. But seems to do nothing. Thanks – Murry