jordan rhea header

Multi-DropDown DataValidation

September 20th, 2017

Updated on Oct. 22nd 2018!

I have finally updated this add-on due to getting tons of messages about it having stopped working in the summer. Some of the differences between the development and production environments for the code here caused some difficulties, however, I think it is working now!

Let me know in the comments below if you have problems!

It works by saving in the PropertiesService the sheet where you want the dropdown, and the location of the categories. For example, here is the function that creates the named range dropdown.

function createNamedRule(data) {
  var ranges = SpreadsheetApp.getActiveSpreadsheet()
    .getNamedRanges()
    .reduce(function(acc, curr) {
      if (curr.getName().indexOf(data.source) > -1) {
        acc.push(curr.getRange().getValue());
      }
      return acc;
    }, []);

  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(ranges, true)
    .build();
  return rule;
}

Then in the onEdit(e) I am looking for the new value of the cell.

function changeNamedRule(title, select) {
  var ranges = SpreadsheetApp.getActiveSpreadsheet()
    .getNamedRanges()
    .reduce(function(acc, curr) {
      var category = curr.getRange().getValue();
      if (category == select) {
        acc = curr
          .getRange()
          .getValues()
          .slice(1);
      }
      return acc;
    }, []);

  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(ranges, true)
    .build();
  return rule;
}

It is a fairly simple process but there are so many different custom use cases, it is really difficult to build a tool that fits all needs. If there are custom implementations that you need, contact me and with any luck we can work together on a similar project!

Find Multi-Dropdowns on the Chome Webstore


jordan rhea wearing a hoodie

Written by jordan rhea a developer focused on building solutions in the cloud

You should follow me on Twitter, or Github, or even on LinkedIn

© 2022 jordan rhea