0

I have checkboxes in columns A, E, H, L, O, S, V, and Z I have a script that is working which allows me to check the box in a2 and it deletes data in e2 and f2. I then am able to reverse the script so selecting the checkbox in e2 will delete data in a2 and b2. Perfect.

The problem is I need it to do it for 29 rows and I can't figure out how to make it work unless I duplicate the script 232 times.

So the checkboxes go together as follows;

  • A needs to delete E and F - while E needs to delete A and B
  • H needs to delete L and M - while L needs to delete H and I
  • O needs to delete S and T - while S needs to delete O and P
  • V needs to delete Z and AA - while Z needs to delete V and W

I hope this makes sense.

Is there an easy way to do this or am I stuck duplicating the script?

function onEdit9(e) {
 var ss = e.source;

 var activeSheet = ss.getActiveSheet();

 var cell = e.range;

 if (activeSheet.getName() == "OPEN" && cell.getA1Notation() == "E2" && cell.isChecked()){

   activeSheet.getRange("a2:b2").clearContent();

 }
}
function onEdit10(e) {

 var ss = e.source;

 var activeSheet = ss.getActiveSheet();

 var cell = e.range;


 if (activeSheet.getName() == "OPEN" && cell.getA1Notation() == "A2" && cell.isChecked()){

   activeSheet.getRange("E2:F2").clearContent();

 }
}

Note: the onEdit9 and onEdit10 are due to other onEdit scripts that are running.

2
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jun 20, 2023 at 3:37
  • Welcome to Web Applications Stack Exchange. Jun 20, 2023 at 13:46

1 Answer 1

0

You only need, and should only have, exactly one onEdit(e) function that handles all columns, like this:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* A needs to delete E and F
* E needs to delete A and B
* H needs to delete L and M
* L needs to delete H and I
* O needs to delete S and T
* S needs to delete O and P
* V needs to delete Z and AA
* Z needs to delete V and W
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  const logic = {
    1: [5, 6],
    5: [1, 2],
    8: [12, 13],
    12: [8, 9],
    15: [19, 20],
    19: [15, 16],
    22: [26, 27],
    26: [22, 23],
  };
  const colNum = e.range.columnStart;
  let sheet;
  if (!Object.keys(logic).includes(String(colNum))
    || !(sheet = e.range.getSheet()).getName().match(/^(OPEN)$/i)
    || !e.range.isChecked()) {
    return;
  }
  sheet.getRange(e.range.rowStart, logic[colNum][0], 1, logic[colNum][1] - logic[colNum][0] + 1).clearContent();
}

See Don't Repeat Yourself and these 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.