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!