Advertisement
Guest User

Untitled

a guest
Feb 19th, 2018
178
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.11 KB | None | 0 0
  1. function dataValidation() {
  2. var ss = SpreadsheetApp.getActiveSpreadsheet();
  3. var Tr = ss.getRange("'Body Armor'!C2:C50").getValues(); //Testrange
  4. // Variables and sheetnames
  5. var b = "'Body Armor'!";
  6. var g = "GS256!" ;
  7. var gC = "'GS256 Classified'!";
  8. var h = "B10:B27";
  9. var l = "B28:B36";
  10. // Get DV ranges
  11. var gh = ss.getRange(g+h) ;
  12. var ghc = ss.getRange(gC+h);
  13. var gl = ss.getRange(g+l);
  14. var glc = ss.getRange(gC+l);
  15. // Build DV rules
  16. var ghr = SpreadsheetApp.newDataValidation().requireValueInRange(gh,true);
  17. var ghcr = SpreadsheetApp.newDataValidation().requireValueInRange(ghc,true);
  18. var glr = SpreadsheetApp.newDataValidation().requireValueInRange(gl,true);
  19. var glcr= SpreadsheetApp.newDataValidation().requireValueInRange(glc,true);
  20. // Loop through each cell
  21. for(i=0;i<Tr.length;i++) {
  22. var j = i+1;
  23. var Hr = ss.getRange(b+"H"+j);
  24. var Jr = ss.getRange(b+"J"+j);
  25. var Lr = ss.getRange(b+"L"+j);
  26. // Test the test range and apply corresponding DV
  27. if (Tr[i][0] == true) {
  28. Hr.setDataValidation(ghcr);
  29. Jr.setDataValidation(ghcr);
  30. Lr.setDataValidation(glcr);
  31. }
  32. else {
  33. Hr.setDataValidation(ghr);
  34. Jr.setDataValidation(ghr);
  35. Lr.setDataValidation(glr);
  36. }
  37. }
  38. }
  39.  
  40. function onOpen() {
  41. var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  42. var menuItems = [
  43. {name: 'Gearset stats dropdown', functionName: 'onEdit'}
  44. ];
  45. spreadsheet.addMenu('Custom Functions', menuItems);
  46. }
  47.  
  48. function onEdit(e) {
  49. dataValidation();
  50. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement