I have two separate scripts in google sheets that are triggered by "TRUE" value checkboxes. The first script is set to uncheck all checkboxes from the sheet and the second is resetting a dropdown list. I'll post the code below for the two scripts. Ultimately I would like both conditions to run on one checkbox. If the code can be written in a more simplified manor that would not be a bad thing.

Best regards, Jon

Script 1:

function onEdit(e) {
  if (e.range.getSheet().getName() == 'Audits') {
    if (e.range.getA1Notation() == 'J27' && e.value == "TRUE") {
      e.range.setValue("FALSE");
      var ss = SpreadsheetApp.getActive();
      var sh = ss.getActiveSheet();
      var rg = sh.getDataRange();
      var vA = rg.getDataValidations();
      var cbA = [];
      for (var i = 0; i < vA.length; i  ) {
        for (var j = 0; j < vA[i].length; j  ) {
          var rule = vA[i][j];
          if (rule != null) {
            var criteria = rule.getCriteriaType();
            if (criteria == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
              sh.getRange(i   1, j   1).setValue(null)

            }
          }
        }
      }
    }
  }
}

Script 2

function onEdit(e) {
  if (e.range.getSheet().getName() == 'Audits') {
    if (e.range.getA1Notation() == 'E27' && e.value == "TRUE") {
      e.range.setValue("FALSE");
      var ss = SpreadsheetApp.getActive();
      var sh = ss.getActiveSheet();
      var rg = sh.getDataRange();
      var vA = rg.getDataValidations();
      var cbA = [];
      for (var i = 0; i < vA.length; i  ) {
        for (var j = 0; j < vA[i].length; j  ) {
          var rule = vA[i][j];
          if (rule != null) {
            var criteria = rule.getCriteriaType();
            if (criteria == SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) {
              sh.getRange(i   1, j   1).setValue(null)

            }
          }
        }
      }
    }
  }
}

CodePudding user response：

To combine and simplify your script, I extracted the variable declarations, which are common to both scripts, outside of the if statements, and then directly added the conditional statement for the criteria of the second script to the first criteria using an or statement (||) as shown below:

function onEdit(e) {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  var rg = sh.getDataRange();
  var vA = rg.getDataValidations();
  if (e.range.getSheet().getName() == 'Audits') {
    if (e.range.getA1Notation() == 'E27' && e.value == "TRUE") {
      e.range.setValue("FALSE");
      for (var i = 0; i < vA.length; i  ) {
        for (var j = 0; j < vA[i].length; j  ) {
          var rule = vA[i][j];
          if (rule != null) {
            var criteria = rule.getCriteriaType();
            if (criteria == SpreadsheetApp.DataValidationCriteria.CHECKBOX || criteria == SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) {
              sh.getRange(i   1, j   1).setValue(null)
            }
          }
        }
      }
    }
  }
}

To test the script's functionality, I created the following setup:

When the checkbox in cell E27 is toggled, all of the sample dropdown lists and checkboxes are reset.

I hope this helps.