Consider the following example:
Screenshot of Sheet "ROI" which has the data from which we have to get the value of dependent data validation:
Screenshot of Sheet "Sales" in which Data Validation is required:
Following is the script code which will get us the desired dynamic data validation (drop down):
//////////////////////////////////////////////////////
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
if( activeSheet.getName() === 'Sales' ) {
var activeCell = activeSheet.getActiveCell();
var activeRow = activeCell.getRow();
if( activeRow >= 3 ) {
var activeColumn = activeCell.getColumn();
if( activeColumn === 5 || activeColumn === 6 ) {
var activeCellValue = activeCell.getValue();
var v = ss.getSheetByName('ROI').getDataRange().getValues();
if( activeColumn === 5 ) {
var f = v.filter(function (currentValue,index,arr) {
return arr[index][0] === activeCellValue
});
var option = f.map(function (currentValue,index,arr) {
return arr[index][1]
});
}
else if( activeColumn === 6 ) {
var prevCellValue = activeSheet.getRange(activeRow,activeColumn-1).getValue();
var f = v.filter(function (currentValue,index,arr) {
return arr[index][0] === prevCellValue && arr[index][1] === activeCellValue
});
var option = f.map(function (currentValue,index,arr) {
return arr[index][2]
});
}
var dv = SpreadsheetApp.newDataValidation();
dv.setAllowInvalid(false);
//dv.setHelpText("Some help text here");
dv.requireValueInList(option, true);
activeSheet.getRange(activeRow,activeColumn+1).setDataValidation(dv);
}
}
}
};
//////////////////////////////////////////////////////
