'Google Sheets Appscript | Multiple Level Dependent Dropdown Validation Lists | Formatting using Appscript
I've been tackling a basic program for fun, learning something new.
My specific problem is, based on the tutorials I've found, everything moves laterally, left to right.
I fill in one drop down cell, the adjacent cell offers me the drop down option, and so on.
Is there a way for me to move that first adjacent cell below the first? And is it possible for moving the next conditional drop downs one cell to the left? I would hope for the lists to repeat these attributes as I fill out the data going downward.
Any insight or help would be appreciated!
Here is my code that I've managed to build so far, without any of the cell formatting
var mainWsName = "master";
var optionsWsName = "options";
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var thirdLevelColumn = 3;
var fourthLevelColumn = 4;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2, 1,wsOptions.getLastRow()-1,4).getValues();
function onEdit(e){
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow()
var c = activeCell.getColumn()
var wsName = activeCell.getSheet().getName();
if(wsName === mainWsName && c === firstLevelColumn && r > 1){
applyFirstLevelValidation(val,r);
} else if(wsName === mainWsName && c === secondLevelColumn && r > 1){
applySecondLevelValidation(val,r)
} else if(wsName === mainWsName && c === thirdLevelColumn && r > 1){
applyThirdLevelValidation(val,r)
} else if(wsName === mainWsName && c === fourthLevelColumn && r > 1){
applyFourthLevelValidation(val,r)
}
} //end onEdit
function applyFirstLevelValidation(val,r){
if(val === ""){
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, secondLevelColumn).clearDataValidations();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
ws.getRange(r, fourthLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearDataValidations();
} else {
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, secondLevelColumn).clearDataValidations();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
ws.getRange(r, fourthLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearDataValidations();
var filteredOptions = options.filter(function(o){ return o[0] === val });
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, secondLevelColumn)
applyValidationToCell(listToApply,cell);
}
}
function applySecondLevelValidation(val,r){
if(val === ""){
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, thirdLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[2] === val });
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, thirdLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
function applyThirdLevelValidation(val,r){
if(val === ""){
ws.getRange(r, fourthLevelColumn).clearContent();
ws.getRange(r, fourthLevelColumn).clearDataValidations();
} else {
ws.getRange(r, fourthLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[2] === val });
var listToApply = filteredOptions.map(function(o){ return o[3] });
var cell = ws.getRange(r, fourthLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

