Search code examples
google-apps-scriptgoogle-sheets

How to disable certain cells or fix values in those cells based on the user input in another cell?

I have four input cells - A4, B4, C4 and C6.

When a user will select 'Start 1-Period' or 'Start 2-Period' in cell C6 that means certain scripts will be running behind, then I need to disable cells or fix values of those cells in A4, B4, C4. You may say it as Non-editable.

When the scripts will stop running, then in cell C6 it will automatically goes back to 'Please select' option, then A4, B4, C4 cells should be editable.

Please check the image attached.Goo

Solution

  • The process you are describing can be broken down into two parts:

    1. You clearly need an onEdit trigger function that will change the protection status of the cells A4:C4 according to C1.

    2. You can control the value of C1 according to your running scripts. For example, when your script is running, set the value of C1 to Start 1-Period or Start 2-Period. Before the running script is finished, you can set the value of C1 back to Please Select.

    In this way, process 1. will be responsible for updating the protection status as soon as C1 is edited and process 2. will be responsible for selecting the value of C1 according to the scripts that are running behind.

    Explanation of the onEdit script:

    Every time you edit cell C6 you apply protection rules to the range A4:C4 according to the selected value. In more detail, every time you edit cell C6 the script will initially remove all current protections. If the selected value of cell C6 is changed to Please Select then no protection is applied. Otherwise, if the new value of C6 is either Start 1-Period or Start 2-Period you apply protection to the range: A4:C4.

    Here is the complete solution:

    function onEdit(e) {

  var sh_name = "Sheet1"; 
  var sh = e.source.getSheetByName(sh_name)
  var row = e.range.getRow();
  var col = e.range.getColumn();
  
  if ( e.source.getActiveSheet().getName() == sh_name && row==6 && col==3 ){

    var protections = sh.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protections.length; i++) {
      var protection = protections[i];
      if (protection.canEdit()) {
        protection.remove();
      }
    }  
     var selection = sh.getRange('C6').getValue();
     var range = sh.getRange('A4:C4');

    if ( selection == 'Start 1-Period' || selection == 'Start 2-Period' ) {
      var protection = range.protect();} }
}

    You just need to copy this code to the script editor, save the changes and then the script will take care of this process.

    Note that my script applies to the sheet with name Sheet1. Change that part of the code if you work with a different sheet.

    References: