Google Sheets Script: Deleting range based off checkbox

Question

I'm attempting to transition a macro into an OnEdit function.

My range is A2:R7. It has no visible filter. There are checkboxes in Col A (A3:A7).

If a checkbox is checked, I want it to clear the corresponding contents of the active row (A:O) and (Q:R). Note, Col P has a formula in it, which I wish to maintain.

When the contents have been cleared, I want it to uncheck the box, and sort the remaining contents in the range (A2:A7).

He is the Macro assigned to Row 5.

function macro1() {
  var spreadsheet = SpreadsheetApp.getActive();

  // Set checkbox to FALSE
  spreadsheet.getRange('A5').activate();
  spreadsheet.getCurrentCell().setValue('FALSE');

  // Clear contents Col B to Col O and Col Q to Col R
  spreadsheet.getRange('B5:O5').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('Q5:R5').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

  // Create filter, sort, remove filter
  spreadsheet.getRange('A2:R7').createFilter();
  spreadsheet.getActiveSheet().getFilter().sort(2, true);
  spreadsheet.getActiveSheet().getFilter().remove();}

My attempts at solving it have been relatively futile, as I'm very new to using scripts. The primary point of frustration is around assigning it to a checkbox.

Tanaike Tanaike · Accepted Answer · 2020-02-28T23:48:17

In your situation,
- "A3:A7" has the checkboxes.
- When the checkbox is checked, you want to clear content of the cells "B:O" and "Q:R".
- You want to uncheck the checked checkbox after the script is run.
- You want to keep the formulas of the column "P".
You want to achieve this using OnEdit event trigger with Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Sample script:

Please copy and paste the following script, ans save the script.

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (range.columnStart == 1 && range.rowStart >= 3 && range.rowStart <= 7 && e.value == "TRUE") {
    const row = range.rowStart;
    sheet.getRangeList([`B${row}:O${row}`, `Q${row}:R${row}`]).clearContent();
    range.uncheck();
  }
}

In this script, when a checkbox of "A3:A7" is checked on the Spreadsheet, the function of onEdit() is run by the OnEdit event trigger as the simple trigger. In this case, the if statement of if (range.columnStart == 1 && range.rowStart >= 3 && range.rowStart <= 7 && e.value == "TRUE") is used.
When the checkbox is checked, the cells "B:O" and "Q:R" are cleared with clearContent().
Then, the checkbox is unchecked with uncheck().

Note:

When you directly run the function of onEdit at the script editor, an error occurs like TypeError: Cannot read property 'range' of undefined. This script is automatically run by the simple trigger. So please check the checkbox. Please be careful this.
From your script, I used the active sheet. But if you want to run the script for the specific sheet, please tell me.
In this script, please use with enabling V8.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Added:

When you want to run the script for the specific sheet, please modify above script as follows.

From:

if (range.columnStart == 1 && range.rowStart >= 3 && range.rowStart <= 7 && e.value == "TRUE") {

To:

if (sheet.getSheetName() == "Sheet1" && range.columnStart == 1 && range.rowStart >= 3 && range.rowStart <= 7 && e.value == "TRUE") {

When the edited sheet is "Sheet1", the script is run. In this case, the sheet of "Sheet1" is required to have the checkboxes. Please be careful this.