I am trying to create a script in Google Sheets that will allow me to have a datestamp entered in a cell when a checkbox is checked in the row that cell is in. I have gotten the first part done thanks to web searches, but the second part of the task I am having issues with.

Once the checkbox has been checked the datestamp does indeed go in the cell, but when I uncheck the checkbox it just updates the date in the cell, but does not remove it.

I am sure I am just missing something easy, but as I do not normally code, and only have a somewhat firm grasp on formulas even, this is a bit out of my ability to troubleshoot. If anyone could look at this and help me figure out what variables would be tied to what I am doing, and which ones I have gotten wrong (ideally with comments so I can understand what each line of code is trying to do), I would appreciate it.

Here is the script:

function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Writing Team Tasks" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( r.getColumn() == 5 ) { //checks that the cell being edited is in column A
var nextCell = r.offset(0, 4); { //offset for the non-adjacent column
if( r.getValue() === "") { //checks if the cell being edited is empty or not?
nextCell.setValue("");
}
else
{
nextCell.setValue(new Date());
}
}
}
}
}

I am hoping to have a script that will enter a date stamp when a checkbox is checked, and then remove the datestamp if the checkbox becomes unchecked. Currently, I am able to do all of that, except remove the datestamp.

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Writing Team Tasks" && e.range.columnStart == 5 && e.value =="TRUE") { 
    e.range.offset(0,1).setValue(new Date());
  }
  if (sh.getName() == "Writing Team Tasks" && e.range.columnStart == 5 && e.value =="FALSE") { 
    e.range.offset(0,1).setValue('');
  }
}