CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsdrop-down-menu
user7254740
user7254740

Reputation: 111

Create dropdown list based on values

I'm looking to create a dropdown list based on value.

Screenshot

I have these two columns in Sheet 1 with values, Column A is the Category and Column B is the Value. On a different sheet I want to create a dropdown list of column Value based on column Category.

For example, on Sheet 2 Column A, the DropDownList choices should be only those Values that have a Sample1 value in the Category and in Column B I want all the choices in the dropdown is only those values that has Sample2 category.

I know cascading dropdown lists but I think this is a bit different.

Upvotes: 2

Views: 1921

Answers (1)

Tanaike
Tanaike

Reputation: 201378

How about following script?

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheets()[0];
  var data = sheet1.getRange('a1:b7').getValues();
  var choises = ['Sample1', 'Sample2'];
  var list = [];
  for (var i in choises){
    var ar = data.filter(function(e){return (e[0]==choises[i])}).map(function(e){return e[1]});
    list.push(ar);
  }
  var sheet2 = ss.getSheets()[1];
  for (var i=0; i<list.length; i++){ 
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(list[i], true).build();
    var range = sheet2.getRange(2,i+1)
    range.setValue(list[i][0]);
    range.setDataValidation(rule);
  }
}

Dropdown list of Sample1

enter image description here

Dropdown list of Sample2

enter image description here

If I misunderstand and this isn't what you want, feel free to tell me. I'll modify this.

Upvotes: 3

Related Questions