google-apps-scriptcheckboxgoogle-sheetsgs-conditional-formattingonupdate

Google Sheet Script onEdit(e) if H2=Yes then make Column I (Checkbox) pressable


I'm trying to find a specifc conditional format for a scenario on google sheet but I was unable to find anything regarding this (than changing color, format text etc).

Is it possible to write an onEdit(e) script that conditions if column Hn==Yes then make column I(checkbox) available to be pressed?

I tried with setFrozenColumns(9) is H column is blank or No but it doesn't work.

function chechBox(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = e.range.getSheet();
  var range = e.range;
  if(sh.getName()!="Test")return;
  var colHValue=sh.getRange(e.range.rowStart,8).getValue()
  if (range.getColumn()==8 & colHValue==0 & colHValue=="No"){
    sh.setFrozenColumns(9);
  }
};

Thank you, M


Solution

  • Try it this way:

    function chechBox(e) {
      var sh = e.range.getSheet();
      if(sh.getName()!="Test")return;
      var colHValue=sh.getRange(e.range.rowStart,8).getValue();
      if (e.range.columnStart==8 && (colHValue==0 || colHValue=="No" )){
        sh.setFrozenColumns(9);
      }
    }
    

    I'm not sure where you wanted to do this:

    if(sh.getRange('H2').getValue()=="Yes")sh.getRange('I2').insertCheckboxes();