根据唯一的单元格文本随机分配单元格背景颜色 [英] Randomly assign cell background color based on unique cell texts

问题描述

我正在寻找一些条件格式设置解决方案，其中将新的单元格背景色设置为唯一的单元格文本.下图显示了这一点.

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.

推荐答案

如何为单元格分配唯一的颜色 onEdit

• Apps脚本 onEdit 触发会在每次(手动！)在您的脚本中进行了编辑
• 您的 onEdit 函数应将您的新条目(预定义列的最后一行)与该列中已有的值进行比较，并使用 getBackground()检索相应单元格的背景色，并使用生成随机颜色
• 使用 indexOf 检查该颜色是否不包含在已经存在的背景颜色中
• 建立新的条件规则将新颜色分配给新文本

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

  示例:

  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);
  
  }
  
  
  

  这篇关于根据唯一的单元格文本随机分配单元格背景颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！