How to clear nextcell if checkbox value is changed to false on edit

Question

We have a shared google sheet with multiple sheets that need the same functionality per page. (additional sheets are being added a few times a year)

Short goal, check a box in column 1 and get a static timestamp in column 2. Uncheck a box in column 1 and column 2 contents are cleared.

I added a static timestamp to the cell to the right of the edited cell if it is blank. This way when a box is checked in column 1, the cell in column 2 receives a timestamp. This is working well (but might work better if based on the value of true for the checkbox)

The issue I am having is with creating a more reliable script to clear contents. Right now, if someone manually deletes the contents in column 2, the checkbox will start behaving backward because it is updating based on edit, not value.

Currently, if column 1 is edited, and the cell in column 2 is not blank, the contents are cleared. I would like this to clear only if the checkbox is unchecked (value changed to false). I have not figured out a way to make it work.

I have not been able to figure out how to make a script based on edit AND checkbox value. Is it possible?

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
   var r = s.getActiveCell();
   if( r.getColumn() == 1 ) { //checks the column
    var nextCell = r.offset(0, 1);
    if( nextCell.getValue() === '' ) //is empty?
    {
      var time = new Date();
      time = Utilities.formatDate(time, "GMT-6", "MM/dd/yy' - 'hh:mm a");
       nextCell.setValue(time);
   }
     else{ nextCell.clearContent()}
   };
}

ross ross · Accepted Answer · 2019-05-21T16:03:55

All you need to do is add a check if the box is ticked or not, this can be achieved by using isChecked() on your cell.

function onEdit(e) {
 var s = SpreadsheetApp.getActiveSheet();
   var r = s.getActiveCell();
   if( r.getColumn() == 1 ) { //checks the column
    var nextCell = r.offset(0, 1);
    if( nextCell.getValue() === '' && r.isChecked() )
    {
      var time = new Date();
      time = Utilities.formatDate(time, "GMT-6", "MM/dd/yy' - 'hh:mm a");
       nextCell.setValue(time);
   }
     else{ nextCell.clearContent()}
   };
}

As you can see, all I've added is a check to make sure your checkbox is ticked. Here are your options for ticked/unticked:

//check if box is ticked
if( nextCell.getValue() === '' && r.isChecked() )

//check if box is unticked
if( nextCell.getValue() === '' && !r.isChecked() )

How to clear nextcell if checkbox value is changed to false on edit

1 Answers