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.

Nikko J. · Accepted Answer · 2022-02-25 22:32:35Z

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)

Collectives™ on Stack Overflow

App Script dynamic data validation based on list

1 Answer 1

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related