Styjun

I'm building a spreadsheet that tracks specific data. I want to place a timestamp in the corresponding adjacent cell when I tick a checkbox - alternative I would be happy with a note on the checkbox stating the time it was last ticked TRUE.

I have literally spent an entire day searching everything on Stackoverflow and google, and I have tried many different methods and functions. Primarily I have been using the onEdit(e) function to (try and) add the time stamp. Early attempts identified a known flaw that meant onEdit(e) wasn't capturing all instances of the checkbox change state. I changed my approach accordingly, both attempting to add the timestamp to the corresponding adjacent cell, and also add a note to the checkbox cell - both seemed to work sporadically which was extremely frustrating.

I have three primary issues with this problem at the moment:

1. Knowing the syntax for checking the value of the checkbox (do I use ==, or ===, and do I use '', ", or no quotation marks?). That sounds like a very stupid question, but I have learning difficulties and have significant trouble retaining written information in my memory :(




2. Successfully setting an `if' -> 'else if' statement that will "toggle" the timestamp on or off depending on the state of the checkbox.




3. I would prefer to set a note with the timestamp on the checkbox cell that is cleared when the checkbox is unticked - however the few times I did get it to work the timezone was incorrect. I did a lot of reading about timezone but without a working example I was unable to comprehend how to implement it into my code. I'm +10 GMT

I have provided a cut down version of my spreadsheet containing only the specific sheet & code I am having trouble with:

https://docs.google.com/spreadsheets/d/1NCdMziBpj0joSv9lQfqT9etz9hMsvgMeuT8X9XTxR20/edit?usp=sharing

Here is the latest iteration of code I have been working on. I've probably attempted half a dozen completely different functions but have deleted each one after several hours of hitting brick walls:

function onEdit() 
 var ss = SpreadsheetApp.getActiveSheet();

 if(ss.getName() == "Quests" ) //checks that we're on the correct sheet
 var tickboxCell = s.getActiveCell();
 if(tickboxCell.getColumn() == 3 && tickboxCell.getValue() === 'TRUE' ) //checks the status of the tickbox
 var dateCell = tickboxCell.offset(0, 1);
 dateCell.setValue(new Date());
 
 

Here is the sample code I modified for adding a note to the checkbox:

function setNote(note)
 var note = note;
 var cell = SpreadsheetApp.getActiveSheet().getActiveCell();

 if(note == "")
 cell.clearNote();
 else
 cell.setNote(note);
 

I would expect that any time a checkbox is ticked, either the corresponding cell to the right would input date(), or alternatively the checkbox would add a note stating "Quest Completed: dd/mm/yyy" (+10 GMT timezone). I would then expect the timestamp cell to clear, or the note to be cleared if the checkbox state is unticked.

try this:

function onEdit(e) 
 if(e.range.getSheet().getName() != 'Quests') return; 
 if(e.range.columnStart==3 && e.value=="TRUE") 
 e.range.offset(0,1).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd:HHmm"));
 

Thanks to Cooper who put me on the right path with his code. It didn't do exactly what I was looking for, but it enabled me to make a few minor changes to it to get exactly what I was looking for.

EDIT: I have found the solution for setNote as well which I will provide here.

Code for adding/removing date to adjacent cell:

function onEdit(e) 
 if (e.range.getSheet().getName() != 'Quests') return; 
 if (e.range.columnStart==3 && e.value=="TRUE") 
 e.range.offset(0,1).setValue(new Date());
 
 else if (e.range.columnStart==3 && e.value=="FALSE") 
 e.range.offset(0,1).clearContent();
 
 

Code for adding/removing setNote from tickbox with correct timezone:

 if(e.range.getSheet().getName() != 'Quests') return; 
 if(e.range.columnStart==3 && e.value=="TRUE") 
 e.range.setNote('Completed: ' + Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd-MM-yy HH:mm:ss"));
 
 else if (e.range.columnStart==3 && e.value=="FALSE") 
 e.range.clearNote();