Advertisement
Guest User

Untitled

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