0

I would like to run function onEdit(e) to remove Data validation with these 2 criterias as below:

  1. Remove for columns G,H,I,K if L is "no need".
  2. Remove for columns M,N,O,Q if R is "no need<2>"

My script runs well for the first criteria. However, it does not work for the second. I can not figure out the solution. Pls help me to correct the script.

function onEdit(e) {
 var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var values = sheet.getRange(`L${row}`).getValues()[0];
  var r = sheet.getRangeList(["G", "H", "I", "K"].map(f => `${f}${row}`));
  if (values.includes("no need")) {
    r.clearDataValidations().setValue("no need");
  } else {
    var rules = ["'Data validation'!$G$5:$G$1003", "'Data validation'!$H$5:$H$1003", "'Data validation'!$I$5:$I$1003", "'Data validation'!$K$5:$K$1003"];
    r.getRanges().forEach((rr, i) => rr.setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(e.source2.getRange(rules[i]), true).build()));
  }
  var values1 = sheet.getRange(`R${row1}`).getValues()[0];
  var r1 = sheet.getRangeList(["M", "N", "O", "Q"].map(f1 => `${f1}${row1}`));
  if (values1.includes("no need<2>")) {
    r1.clearDataValidations().setValue("no need<2>");
  } else {
    var rules1 = ["'Data validation'!$M$5:$M$1003", "'Data validation'!$N$5:$N$1003", "'Data validation'!$O$5:$O$1003", "'Data validation'!$Q$5:$Q$1003"];
    r1.getRanges().forEach((rr1, i) => rr1.setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(e.source.getRange(rules1[i]), true).build()));
  }
}

Many thanks

Here is my GG sheet link:

https://docs.google.com/spreadsheets/d/1osCKdZsvaKTFJyD2wfcCoQtIfGxTR6ovt9eE7zabyzs/edit?usp=sharing

1 Answer 1

0
  1. Remove for columns G,H,I,K if L is "no need".
  2. Remove for columns M,N,O,Q if R is "no need<2>"

Use Array.includes() and the event object e, like this:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* When the value 'no need' is entered in column L, removes data validation
* in columns G, H, I, K in the active row.
*
* When the value 'no need<2>' is entered in column R, removes data validation
* in columns M, N, O, Q in the active row.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
  if (!e.range || !['no need', 'no need <2>'].includes(e.value)) {
    return;
  }
  const columnsToClear = {
    12: ['G', 'H', 'I', 'K'],
    18: ['M', 'N', 'O', 'Q'],
  }[e.range.columnStart];
  if (!columnsToClear) {
    return;
  }
  const rangeList = e.range.getSheet().getRangeList(
    columnsToClear
      .map(columnLabel => `${columnLabel}${e.range.rowStart}`)
  );
  rangeList.clearDataValidations();
  e.source.toast(`Cleared data validations in ${rangeList.getRanges().length} cells.`);
}

See onEdit(e) best practices.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.