Dependent Dropdowns: needed for Cepheus Engine Ship, Vehicle, Robot, Suit, Mech, etc...

Traveller and Cepheus Engine uses the Bill of Materials or Scope of Work method of building things. Its basically a Table where an Item's attributes: Cost and Specifics, are arrayed and summed. 

 Here it is for anyone to use. My philosophy make it that someone who will make a better set of tools I WOULD BUY would come around, until then I will make these tools.  Someone hopefully will come around - make automated spreadsheets for making their Cepheus Engine stuff much easier,  and uses that automation and blender to make HARD SCIFI more accessible. Guys who are as bad in science as me getting to play "accessible" hard scifi until eventually we can wrap our heads around it. 

So to make 2-Parsec Playable I will be fixing Cepheus Engine ship Combat. I'm making good progress despite the Social Obligations of the Holiday. 

Sending good vibes this holiday. I don't think nicco and I will finish in time. But Nicco made a bunch of Space Stations for 2-Parsec. I need to make a Delta V map generator for Mneme World Generator 

//23:40 https://www.youtube.com/watch?v=s-I8Z4nTDak&t=670s //24:00 Making sure it grabs the right List, the dependent dropdown. //global script // 40:31 making the SHEET a global variable var mainWsName = "APPSCRIPT"; var optionsWsName = "SECTION"; // var firstLevelColumn = 1; var secondLevelColumn = 2; var thirdLevelColumn = 3; var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName) //I need to make an Array where all the Sections is mapped to all the Options of that Array. //creating the array is in 23:31. row and column start. var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName); var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,3).getValues(); //31:05 stuck here but fixed. I was missing the applyValidationito Cell visible at 12:59 //now stuck at 32:11 - it doesnt clear // 36:28 we are going to make it that the Main column can easily be changed without changing the code. // function myFunction() { // var list = ["a", "b", "c"]; // var cell = ws.getRange("C2"); // applyValidationToCell(list,cell); // } //removed at 40:57 //it limits the edits to Column 1 and Row >5 // this way only this column works. // unforunately this means this script only works with // this sheet. function onEdit(e){ var activeCell = e.range; var val = activeCell.getValue(); var r = activeCell.getRow(); var c = activeCell.getColumn(); var wsName = activeCell.getSheet().getName(); //37:01 Chaging this to firstLevelColumn if(wsName === mainWsName && c === firstLevelColumn && r > 5) { applyFirstLevelValidation(val,r); } else if (wsName === mainWsName && c === secondLevelColumn && r > 5) { applySecondLevelValidation(val,r); } } // end of onEdit //38:18 new function function applyFirstLevelValidation(val,r) { if(val === ""){ ws.getRange(r,secondLevelColumn).clearContent(); ws.getRange(r,secondLevelColumn).clearDataValidations(); // 48:15 clear validations ws.getRange(r,thirdLevelColumn).clearContent(); ws.getRange(r,thirdLevelColumn).clearDataValidations(); //will try this in 33:16 } else { ws.getRange(r,secondLevelColumn).clearContent(); ws.getRange(r,thirdLevelColumn).clearContent(); ws.getRange(r,secondLevelColumn).clearDataValidations(); ws.getRange(r,thirdLevelColumn).clearDataValidations(); var fileredOptions = options.filter(function(o){return o[0] === val }); var listToApply = fileredOptions.map(function (o){ return o [1]}) //console.log(listToApply); //removed console log in 37:40 var cell = ws.getRange(r,secondLevelColumn); applyValidationToCell(listToApply,cell); } //else } // applyFirstLevelValidation function applySecondLevelValidation(val,r) { if(val === ""){ ws.getRange(r,thirdLevelColumn).clearContent(); ws.getRange(r,thirdLevelColumn).clearDataValidations(); //will try this in 33:16 } else { ws.getRange(r,thirdLevelColumn).clearContent(); //44:25 var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue(); var fileredOptions = options.filter(function(o){return o[0] === firstLevelColValue && o[1] === val }); //46:10 var listToApply = fileredOptions.map(function (o){ return o [2]}) var cell = ws.getRange(r,thirdLevelColumn); applyValidationToCell(listToApply,cell); } //else } // applySecondLevelValidation // visible at 12:59 this was the other mistake. I forgot to encode this part. function applyValidationToCell(list,cell){ var rule = SpreadsheetApp .newDataValidation() .requireValueInList(list) .setAllowInvalid(false) .build(); cell.setDataValidation(rule); }