App Script dynamic data validation based on list

Question

I have a list of user and data validation mapping in column A and B (Table 1)

User         Data Validation
Customer          Jim
Customer          Pam
Customer          Tim
Employee          Beth
Employee          Larry
Employee          David
Manager           Krista
Manager           Bob

Next I have a list of the User and Data Validation required in column D and E (Table 2)

User          Data Validation
Customer
Employee
Manager
NA
Employee

I'm attempting to apply Data Validation from the first table to the second table.

Customer would be able to select (Jim, Pam, Tim). NA would be blank. Employee would be able to select (Beth, Larry, David).

The tricky part is we may add or change User functions. We may also add items to the current Data Validation list from table 1.

Answer 1

Here I created a code that will generate a data validation in column D based on the value of column A and B.

Code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Sheet1");
  var bLastRow = ss.getRange("B"+(ss.getLastRow()+1)).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  var range = sh.getRange(2, 1, bLastRow-1, 2);
  var data = range.getValues();
  
  var obj = {};
  data.forEach(val => {
    if(!obj[val[0]]){
      obj[val[0]] = [val[1]]
    }else{
      obj[val[0]].push(val[1])
    }
  })
  var cLastRow = ss.getRange("C"+(ss.getLastRow()+1)).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  var validationRange = sh.getRange(2, 3, cLastRow-1, 1);
  var validationData = validationRange.getValues();
  
  for(var i = 0; i < validationData.length; i++){
    if(obj[validationData[i][0]]){
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(obj[validationData[i][0]]).build();
    sh.getRange('D'+(i+2)).setDataValidation(rule);
    }else{
      continue;
    }
  }
}

Sample Data:

Output:

Changed the user type of Tim from Customer to Employee:

Added value in Column C:

References:

• requireValueInList(values)