Create connected drop-downs in google sheets where value can be present only in one drop-down

It's easier to explain my problem using this sample sheet.

I have 3 rooms with 3 seats in each room. I also have a list of people who can be placed at seats. The problem is I can put the same person on two or more seats. The desired behavior is when I select the same person for the second time on another seat it should be removed from his previous seat automatically, so this way it will be impossible to duplicate persons.

Using Nested For Loops to Remove the First Duplicate Value

The script uses nested for loops to check the range for matching values, with the first loop iterating through the rows and the second through the columns. Then it clears the first duplicate while keeping the second occurrence/updated value.

Below is the full code:

const onEdit = (e) => {
  var sh = e.source.getActiveSheet();
  var rg = e.range;
  var vl = rg.getValue();
  var row = rg.getRow();
  var col = rg.getColumn();
  if (vl && (col == 2 || col == 3 || col == 4)) {
    for (let i = 1; i <= 4; i++) {
      for (let j = 2; j <= 4; j++) {
        var srg = sh.getRange(i, j);
        if (!(i == row && j == col) && vl == srg.getValue()) {
          srg.setValue("");
        }
      }
    }
  }
}

Expected value

Paste the script and test it by updating the spreadsheet.

Before:

After:

References

• JavaScript for Loop
• Spreadsheet Service