Clear a cell when a certain value of a dropdown list is selected

Question

I would need to fix my script "code" so that when "-", "--" or "---" values are selected from the dropdown list, the cells controlled by the script are cleared.

Here's the link of the Google Sheets:

https://docs.google.com/spreadsheets/d/1ZmmqPgH2lNC_eYivkYcKQrNH8BdM-g3z-ZVK5ePgxnM/edit?usp=sharing

The code:

function onEdit(e) {
  var ss=e.source;
  var rg=e.range;   
  var sh=rg.getSheet();
  if(rg.columnStart!=2 && rg.columnStart!=10 && rg.columnStart!=18 && rg.columnStart!=26 && rg.columnStart!=34){return;}
  setClearOnNoValue(e);
}

function setClearOnNoValue(e) {
  var rg=e.range;
  var sh=rg.getSheet();
  if(e.value && e.value.length>0) {//added e.value.length>0
    sh.getRange(rg.rowStart, rg.columnStart + 3).setValue(Utilities.formatDate(new Date(new Date().getFullYear(),new Date().getMonth()+1,new Date().getDate(),new Date().getHours(),new Date().getMinutes()),Session.getScriptTimeZone(),"d/MMM/yyyy HH:mm"));
  }else{
    sh.getRange(rg.rowStart, rg.columnStart + 3).setValue('');
  }
}

Please post some of the code you are referring to. Just linking to your code somewhere is not encouraged on SO. minimal reproducible example — Chris

Cooper Cooper · Accepted Answer · 2019-02-12T21:19:06

Try this:

function onEdit(e) {
  var ss=e.source;
  var rg=e.range;   
  var sh=rg.getSheet();
  var name=sh.getName();
  if(rg.columnStart!=2 && rg.columnStart!=10 && rg.columnStart!=18 && rg.columnStart!=26 && rg.columnStart!=34){return;}
  setClearOnNoValue(e);
}

function setClearOnNoValue(e) {
  var rg=e.range;
  var sh=rg.getSheet();
  if((e.value && e.value.length>0)) {//added e.value.length>0
    sh.getRange(rg.rowStart, rg.columnStart + 3).setValue(Utilities.formatDate(new Date(new Date().getFullYear(),new Date().getMonth()+1,new Date().getDate(),new Date().getHours(),new Date().getMinutes()),Session.getScriptTimeZone(),"d/MMM/yyyy HH:mm"));
  }else{
    sh.getRange(rg.rowStart, rg.columnStart + 3).setValue('');
  }
  if((e.value && e.value=='-')||(e.value && e.value=='--')||(e.value && e.value=='---')) {
    sh.getRange(rg.rowStart, rg.columnStart + 3).setValue('');
  }
}

Clear a cell when a certain value of a dropdown list is selected

1 Answers