CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom Sawkins
Tom Sawkins

Reputation: 361

CheckBox - Once Checked you cannot uncheck ? - GoogleAppsScript / GoogleSheets

Is it possible to code a checkbox that once checked it stays checked ?

My code would run again if a person checked - then unchecked - then accidently checked again, something I dont want to happen.

Here is the code

  function onEdit(e) {

  AddPalletCount(e)
  AddCheckBox(e)
  }

 function AddCheckBox(e){
 var row = e.range.getRow();
 var col = e.range.getColumn();

 if(col === 2 && row > 2 && e.source.getActiveSheet().getName() === "PREPSHEET"){

  e.source.getActiveSheet().getRange(row,4).setValue(new Date());
  e.source.getActiveSheet().getRange(row,3).setValue("Done");

  var sheet = SpreadsheetApp.getActiveSheet();
  var activeRange = sheet.getActiveRange();
  var criteria = SpreadsheetApp.DataValidationCriteria.CHECKBOX;
  var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  var range = sheet.getRange(activeRange.getRow(), 5);
  range.setDataValidation(rule);
  }}

Thanks In Advance

Upvotes: 0

Views: 807

Answers (1)

Cooper
Cooper

Reputation: 64062

A checkbox that stay's check once it's checked

It doesn't actually stay checked but once you check and try to uncheck it, it rechecks itself. It has to change in order to get the onEdit trigger.

Also note that this has to be an installable trigger because simple triggers can't do things that require permission like changing user data.

function onMyEdit(e) {
  //e.source.toast('Entry');//debug
  var sh=e.range.getSheet();
  //var log=Utilities.formatString('oldValue: %s value: %s',e.oldValue,e.value);//debug
  //sh.getRange(sh.getRange(1,9,sh.getLastRow(),1).getValues().filter(String).length+1,9).setValue(log);//debug  
  if(e.range.columnStart==2 && e.range.rowStart>2 && sh.getName()=='DONATIONS RECEIVED') {
    //e.source.toast('Access');//debug
    if(e.oldValue=='true' && e.value=='FALSE') {
      //e.source.toast('SetValue');//debug
      e.range.setValue("TRUE");
    }
  }
}

You might wish to keep lines 4 and 5 for future onEdit debugging work as I found them quite helpful in solving this problem.

Upvotes: 1

Related Questions