When a checkbox is checked, clear contents within of row range

[Kevin Mok]

Hi there, 

I am trying to create a script that allows me to clear a row of inputs within a certain row range when a checkbox is checked in Google Sheets. 

For instance, there is a checkbox in A1 and inputs in B1:G1. When A1 checkbox is checked, it clears B1:G1.

This is a sample spreadsheet: https://docs.google.com/spreadsheets/d/1JFkWBERs-ogijK6OMTVNT-ljmYNsoSGYkrKK89leytU/edit#gid=0 that I'm working off.

Any help appreciated. 

Regards,

 

[Darren D'Mello]

You can use the onedit trigger.

[Clark Lind]

This should do the job. Basically checks when something is changed. If the change is a checkbox, it sees if it is now "checked" (value==true, unchecked==false). If checked, clear contents in columns B-G on same row.

function onEdit() {
var sheet = SpreadsheetApp.getActiveSheet();
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aRow = cell.getRow();
var data = sheet.getRange('B' + aRow + ':G' + aRow);
if (cell.getValue() == true) {
     data.clearContent();
  }
}

[Clark Lind]

p.s., if you add other things to run "onEdit", then put this in its own function and call it from onEdit:

function checkBoxClear() {

var sheet = SpreadsheetApp.getActiveSheet();

var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aRow = cell.getRow();
var data = sheet.getRange('B' + aRow + ':G' + aRow);
if (cell.getValue() == true) {
     data.clearContent();
  }
}

function onEdit() {

  checkBoxClear();

  someOtherFunction();

}

[Reno Blair]

Another solution using the event object and moving magic values to the top of the function. Also should reset the checkbox.

function onEdit(e) {

  var watchColumn = 1;

  var watchValue = true;

  var clearWidth = 7;

  if (e.range.getColumn() === watchColumn && e.value === watchValue) {

    e.range.getSheet().getRange(e.range.getRow(), 1, 1, clearWidth).clearContent();

  }

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a674f53e-7ba6-4b5c-aeea-0f7663a2ba24%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Reno Blair

​

Educational Technology Services​

Grossmont Union High School District

619 644 8250

[Benjamin Hauch]

Don't forget that checkboxes may have any value for checked and unchecked -- they're Data Validation. So while it would be confusing for someone who is viewing the values on the sheet, one can create a checkbox where it is checked when the value is `false`, and unchecked when the value is `true`.

You need to check the cell for a `DataValidationRule`, and then look at its `CriteriaValues`:

function onEdit(e) {
    var edited = e.range;

    var rule = edited.getDataValidation();
    if (rule && rule.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
        var choices = rule.getCriteriaValues();
        if (!choices.length) {
            choices = [true, false];
        }
        if (edited.value === choices[0]) {
            // checked
        } else if (edited.value === choices[1]) {
            // unchecked
        } else {
            // cell probably has a DV error tooltip
        }
    }
}

I wrote about this a bit on SO: https://stackoverflow.com/questions/54935257/finding-all-checkboxes-in-a-google-sheet

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a402eb22-bbb7-4d74-a3bd-f86e272eb44e%40googlegroups.com.