Multiple dependent dynamic dropdowns with repeating column dropdowns in Google Sheets

Question

The Google Sheet I have uses code made by user Max Makhrov, code here, to make multiple dependent dynamic dropdowns in columns D-F (for location) and columns H-L (for objectives & activities) in my sample sheet here.

I would like help to modify the script to do two things:

1. Whatever activity is selected from the dropdown menu in Column I, I would like the same dropdown menu options to be available (to repeat) for columns J-L. As you can see I found a way to do it, but to me it seems clunky and not ideal, and leaves too much room for errors. Users should not select the activity twice, but I've put conditional formatting in to flag that if they do. However:
2. Ideally, but less importantly, if the dropdown menu items could still repeat for columns J-L but once an activity is selected in previous cells, that option is removed from each of the following repeated dropdown menus in additional columns, up to and including column L. This would help avoid accidentally repeating an activity.

NB: Reference question "How do you do dynamic / dependent drop downs in Google Sheets?"

Thank You!

Answer 1

When one of the drop-down cells is edited you can use an onEdit trigger [1] to iterate through the 4 columns (I-L) and update the drop-downs in each cell removing the option selected in the edited cell. You also need to add the old selected value (previously deleted from other options) to the other drop-downs. For this, you can use getDataValidation [2] and getCriteriaValues [3] functions chained to a Range object to retrieve the current drop-down values array on that range and delete the option matching with the selected option.

Use newDataValidation() [4] function to create a new rule using your updated drop-down values array and setDataValidation [5] function to set the rule to the range.

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

Run just the first time to set all the drop-downs in columns I-L, which are get it from range 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