Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function dataValidation() {
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var Tr = ss.getRange("'Body Armor'!C2:C50").getValues(); //Testrange
- // Variables and sheetnames
- var b = "'Body Armor'!";
- var g = "GS256!" ;
- var gC = "'GS256 Classified'!";
- var h = "B10:B27";
- var l = "B28:B36";
- // Get DV ranges
- var gh = ss.getRange(g+h) ;
- var ghc = ss.getRange(gC+h);
- var gl = ss.getRange(g+l);
- var glc = ss.getRange(gC+l);
- // Build DV rules
- var ghr = SpreadsheetApp.newDataValidation().requireValueInRange(gh,true);
- var ghcr = SpreadsheetApp.newDataValidation().requireValueInRange(ghc,true);
- var glr = SpreadsheetApp.newDataValidation().requireValueInRange(gl,true);
- var glcr= SpreadsheetApp.newDataValidation().requireValueInRange(glc,true);
- // Loop through each cell
- for(i=0;i<Tr.length;i++) {
- var j = i+1;
- var Hr = ss.getRange(b+"H"+j);
- var Jr = ss.getRange(b+"J"+j);
- var Lr = ss.getRange(b+"L"+j);
- // Test the test range and apply corresponding DV
- if (Tr[i][0] == true) {
- Hr.setDataValidation(ghcr);
- Jr.setDataValidation(ghcr);
- Lr.setDataValidation(glcr);
- }
- else {
- Hr.setDataValidation(ghr);
- Jr.setDataValidation(ghr);
- Lr.setDataValidation(glr);
- }
- }
- }
- function onOpen() {
- var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
- var menuItems = [
- {name: 'Gearset stats dropdown', functionName: 'onEdit'}
- ];
- spreadsheet.addMenu('Custom Functions', menuItems);
- }
- function onEdit(e) {
- dataValidation();
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement