Google工作表中具有重复列下拉列表的多个相关动态下拉列表

Question

我的谷歌表单使用了用户马克斯·马克洛夫（Max Makhrov）编写的代码，这里的代码，在D-F列（用于位置）和H-L列（用于目标）中创建多个相关的动态下拉列表

我想帮助修改脚本以完成两件事：

1. 无论从第一列的下拉菜单中选择了什么活动，我都希望J-L列可以使用相同的下拉菜单选项（重复）。正如您所看到的，我找到了一种方法，但对我来说，它似乎笨重且不理想，并且为错误留下了太多的空间。用户不应该选择活动两次，但我已经在其中添加了条件格式来标记是否选择了。但是：
2. 理想情况下，但不太重要的是，如果下拉菜单项仍然可以对J-L列重复，但一旦在以前的单元格中选择了某个活动，则该选项将从以下每个重复的下拉菜单的附加列（包括L列）中删除。这将有助于避免意外重复某个活动

注：参考问题“如何在谷歌工作表中进行动态/相关下拉？”

谢谢你！

Answer 1

编辑其中一个下拉单元格时，您可以使用onEdit触发器[1]迭代4列（I-L），并更新每个单元格中的下拉列表，删除编辑单元格中选择的选项。您还需要将旧的选定值（以前从其他选项中删除）添加到其他下拉列表中。为此，您可以使用链接到Range对象的getDataVal事实上[2]和getCriteriaValue[3]函数来检索该范围上的当前下拉值数组，并删除与所选选项匹配的选项。

使用newDataValidation（）[4]函数使用更新的下拉值数组创建新规则，并使用setDataValidation[5]函数将规则设置为范围。

function onEdit(event) {
  var range = event.range;
  var sheetName = range.getSheet().getSheetName();
  var col = range.getColumn();
  var newValue = event.value;
  var oldValue = event.oldValue;

  //If the edited range is in sheet '3W' and beetween columns I-L
  if(sheetName == '3W') {
    if(col>=9 && col<=12) {

      for(var i=9; i<13; i++) {
        //Don't change anything for edited cell
        if(col == i) { continue; }
        else {
          //Get range to update and current dropdown values for that range
          var rangeToUpdate = range.getSheet().getRange(range.getRow(), i, 1, 1);
          var dropdownValues = rangeToUpdate.getDataValidation().getCriteriaValues()[0];

          //Find new edited value and delete it from options array
          var index = dropdownValues.indexOf(newValue);
          if (index > -1) {
            dropdownValues.splice(index, 1);
          }

          //If previous selected value is not beetween the options, add it
          if(oldValue && dropdownValues.indexOf(oldValue) == -1) {
            Logger.log(oldValue)
            dropdownValues.push(oldValue);
          }

          //Set new dropdown values to range 
          var updatedRule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues, true).setAllowInvalid(false);
          rangeToUpdate.setDataValidation(updatedRule);
        }
      }    
    }
  }
}

仅在第一次运行时设置I-L列中的所有下拉列表，这些下拉列表从范围E1:E10:

function setDropdownsInitially() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //Range with the dropdown values 
  var sheet = ss.getSheetByName("indicators");
  var dropdownValues = sheet.getRange("E1:E10").getValues();

  //Data validation rule
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues, true).setAllowInvalid(false);

  //Range where the dropdowns will be created
  var targetSheet = ss.getSheetByName("3W");
  var cells = targetSheet.getRange("I2:L"); 

  //Set data validation rule
  cells.setDataValidation(rule);
}

[1]https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events

[2]https://developers.google.com/apps-script/reference/spreadsheet/range#getdatavalidation

[3] https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder.html#getcriteriavalues

[4] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#newdatavalidation

[5] https://developers.google.com/apps-script/reference/spreadsheet/range#setdatavalidationrule