How can you conditionally swap out a dropdown on a range of cells in Google Sheets?

Question

I'm trying to work on a sheet that allows me to switch between a true/false value and based on that result it toggles between 2 different dropdown options in another cell.

Desired Result

---

• Cell C2 contains a dropdown of TRUE/FALSE
• Cells H2/J2/L2 contain a dropdown that is also dependent on the value specified by the C2 dropdown.
• H2/J2/L2 get the data from 2 separate sheets.
• Solution scales infinitely based on the corresponding row in the C column IE if it's C3 then the logic needs to apply to H3/J3/L3, etc.

Example

---

I have the sample spreadsheet that I'm working on here.

• C2 is pulling the data from the MISC sheet for a boolean value
• H2/J2 need to pull the data from B10-B27 on either GS256 OR GS256 Classified
• L2 needs to pull the data from B28-B36 on either GS256 OR GS256 Classified
  • GS256 contains the data if C2 is set to FALSE and GS256 Classified contains the data if C2 is set to TRUE.
• I want to be able to handle variable amounts of values if for instance more values are added or subtracted on each of the corresponding one.

What I've tried

---

I've tried to do a mix of to using ArrayFormula with an if statement, however the issue that I face here is that it's dependent on a single cell, and it swaps the data in the multiple places, not just the single cell that I want to impact. So this effectively is just setting the drop downs to a single dataset.

Looking for feedback on how to approach this. All the data I'm getting from an external source via ImportRange. I'm fairly new to using sheets, but understand the basics. If I need to add a script I'm fine doing so since I'm more of a programmer. If this would be better served via a different interface like a web app then I'm fine doing so since this seems like this might be fairly complex with the variability of the data I'm working with on the spreadsheet.

Answer 1

function dataValidation() {                                                                                     
var ss = SpreadsheetApp.getActiveSpreadsheet();                                                             
var Tr = ss.getRange("'Body Armor'!C1:C20").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);
                }
        }
}