How to use drop down to lock and unlock cells according to values selected and still matain drop downs in the unlocked cell

Question

Google Sheets

I have a sheet with two cells (B2 and B3) and I added drop downs to the cells, Cell B2 has Yes and No as its values(drop downs) and B3 has Yes and No as its values (drop downs). How can I make cell B3 locked and only open when cell B2 value is Yes. and still maintain the drop down action in B3.

Answer 1

Hey try the below code to achieve the desired outcome.

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var check = ss.getRange('B2').getValue();

// Remove all protections if B2 = 'Yes'
if(check == "Yes"){
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
  var protection = protections[i];
  if (protection.canEdit()) {
    protection.remove();
  }
}
}

// Place protection in cell B3 when B2 = No/Empty
else{
var range = ss.getRange('B3');
var protection = range.protect().setDescription('Sample protected range');

// Ensure the current user is an editor before removing others. Otherwise, if the user's edit
// permission comes from a group, the script throws an exception upon removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}
}
}

When B2 = Yes

When B2 = No/ Empty