如何禁止Google Sheets下拉框已选有效值触发的数据验证错误提示

我正在创建如下所示的下拉列表：

// 生成包含所有可选位置的下拉框
function doPopulatePositionDropdown() {
  var positionMD = getPositionMD();
  var positions=[]; var j = 0;
  for(var i = 0; i<positionMD.length;i++){  

    if(positionMD[i][1] != ''){
      positions[j] = positionMD[i][1]; 
      j++;
    };
  };

  var rule = SpreadsheetApp.newDataValidation()
                           .requireValueInList(positions, true)
                           .setAllowInvalid(false)
                           .setHelpText('只能选择尚未被占用的空闲位置')
                           .build();
  var range = SpreadsheetApp.getActive().getRange('ChoosePosition');
  range.setDataValidation(rule);  
};

场景说明 #

• 使用场景为教练需要为每个上场位置选择对应的球员
• 为了确保每个上场位置只能分配给一名球员，positionMD仅存储尚未分配给球员的位置（即positionMD为空闲位置列表）
• 当教练为球员分配位置后，onEdit触发器会调用doPopulatePositionDropdown()函数，可选的空闲位置下拉列表会对应缩减
• 每次教练完成位置分配后，空闲位置下拉列表都会更新，教练可以轻松查看剩余未分配的位置，也不会出现重复分配同一位置的问题，整体功能运行符合预期

遇到的问题 #

已经选中了位置的单元格现在会出现类似评论的红色小三角错误标识。触发错误的原因是单元格中的值不再有效，因为该位置被选中后就从positionMD的可选列表中移除了。
但从应用逻辑来看，这种情况不属于错误，想要禁止这类错误标识和悬浮提示文本的显示。

补充说明：这个问题可以简化为：如果给已经包含内容的范围设置数据验证规则，且这些已有内容不符合新的验证规则，如何禁止这些已存在的有效值触发错误提示？
这纯粹是易用性问题，教练看到自己从下拉列表中选中的位置被提示为无效会感到困惑。
注意：答案不接受「先调用setDataValidation(rule)禁用范围的数据验证允许任意输入，填入需要的数据后再恢复旧的验证规则」这类方案，不符合核心需求。

可复现问题的示例代码 #

function onEdit() {
  doPopulatePositionDropdown();
};

function doPopulatePositionDropdown() {
  var pos = [1,2,3,4,5,6,7,8,9,0,''].filter(e => e !== '')
  var range = SpreadsheetApp.getActive().getRange('Sheet0!A1:A2');
 
  var takenRange = SpreadsheetApp.getActive()
                       .getRange('Sheet0!A1:A2')
                       .getValues()
                       .flat().filter(function(value) {
    return ( value != '')
  });
  takenSet = new Set(takenRange);
  pos = pos.filter(dataRow =>!takenSet.has(dataRow));

  var rule = SpreadsheetApp.newDataValidation()
                           .requireValueInList(pos, true)
                           .setAllowInvalid(false)
                           .setHelpText('只能选择尚未被占用的空闲位置')
                           .build();
  range.setDataValidation(rule);  
}

你可以通过拆分数据验证应用范围的方式解决这个问题，不需要改动现有业务逻辑，只需要修改doPopulatePositionDropdown函数的逻辑：

1. 把目标范围拆成两类单元格：已经填了值的单元格、空值单元格
2. 只给空值单元格设置带空闲位置列表的验证规则，已经填了值的单元格单独配置仅允许当前值的验证规则即可，不会触发错误提示。

修改后的代码如下：

function doPopulatePositionDropdown() {
  var pos = [1,2,3,4,5,6,7,8,9,0,''].filter(e => e !== '')
  var allRange = SpreadsheetApp.getActive().getRange('Sheet0!A1:A2');
  var allValues = allRange.getValues();
  var takenValues = allValues.flat().filter(value => value !== '');
  var takenSet = new Set(takenValues);
  var availablePos = pos.filter(dataRow => !takenSet.has(dataRow));

  // 构建空闲位置的验证规则
  var availableRule = SpreadsheetApp.newDataValidation()
                           .requireValueInList(availablePos, true)
                           .setAllowInvalid(false)
                           .setHelpText('只能选择尚未被占用的空闲位置')
                           .build();
  
  // 逐行判断单元格状态，单独设置验证规则
  for (var i = 0; i < allValues.length; i++) {
    var cell = allRange.getCell(i+1, 1);
    if (allValues[i][0] === '') {
      // 空单元格应用空闲位置验证规则
      cell.setDataValidation(availableRule);
    } else {
      // 已填充的单元格设置允许当前值的验证规则，不会触发错误
      var cellRule = SpreadsheetApp.newDataValidation()
                            .requireValueInList([allValues[i][0]], true)
                            .setAllowInvalid(false)
                            .build();
      cell.setDataValidation(cellRule);
    }
  }
}

这个方案可以同时满足三个需求：

• 空单元格的下拉列表仅显示未被占用的位置
• 已经填充了位置的单元格不会出现无效值的红色错误提示
• 不会出现位置重复分配的问题，空单元格无法选择已被占用的位置

内容的提问来源于stack exchange，提问作者mortpiedra