script to clear note from unchecked checkbox

user_12196309 0
Viewed 221

I'm not a programmer but like to play around with scripts every now and then as a hobby.

I built a spreadsheet to capture the timestamp in a 'note' every time a checkbox is checked, by piecing together code from various sources. However, I can't clear the note in the checkbox, when it is unchecked.

CODE SAMPLE 

function onEdit(e) {
  if(e.value != "TRUE") return;
e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart).setNote('Completed: ' + Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd-MM-yy HH:mm:ss"));
}

a side note: is this possible with comments instead of notes?

google-apps-script google-sheets checkbox scripting
2 Answers

Try this:

function onEdit(e) {
  if(e.range.getSheet().getName()!='Sheet1')return;//Limits action to one sheet
  //e.source.toast('flag1');
  if(e.range.columnStart==1 && e.value == "TRUE") {//I have the checkboxes in columnA
    //e.source.toast('flag2');
    e.range.setNote('Completed: ' + Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd-MM-yy HH:mm:ss"));//sets note to completed
  }
  if(e.range.columnStart==1 && e.value == "FALSE") {
    //e.source.toast('flag3');
    e.range.setNote('');//Clears note
  }
}

In your case this is probably what you want.

function onEdit(e) {
  if(e.value == "TRUE") {
    e.range.setNote('Completed: ' + Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd-MM-yy HH:mm:ss"));
  }
  if(e.value == "FALSE") {
    e.range.setNote('');
  }
}
user_72150910

Try this:

function onEdit(e) {
  if(e.value == "FALSE"){ 
    e.range.clearNote();
    return;
  }
  e.range.setNote('Completed: ' + Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd-MM-yy HH:mm:ss"));
}

Your code was right, you just needed to remove the note inside the if statement, I used clearNote() (Documentation here) to remove them if the values was "FALSE" The note itself might be still visible for a second after you uncheck the box, but it''l go away and it'll be removed from the cell.

user_115818300
Related