On edit trigger that inserts timestamp dependant on checkbox value

Question

I'm trying to develop an inventory sheet for my company I work for in Google Sheets. In the system, I'm wanting if a checkbox in Column C is ticked it will display the "checked out time" in Column D, and if unticked will display the "checked in time" in Column E. However, I'm wanting when the item is checked in, the checked out date/ time cell turns back to blank.

I've used this custom function script to create a TIMESTAMP function that works, but every time the spreadsheet is reopened is recalculates all the date to the current date/time. Any way of making it non-volatile?

function timestamp() {
  return Date();
}

Would I need a onedit trigger that could do what I described above?

WARNING: I'm not scripting savvy at all.I've been scouring the forums for the past week and can't get anything that works. Any help would be greatly appreciated :D

Link: https://docs.google.com/spreadsheets/d/1Oj6eustjvk9opXFNR2jHa0uMjPnKVXsvwOxFjb1t7-8/edit?usp=sharing

Welcome to Stack! Check out the documentation here developers.google.com/apps-script/reference/spreadsheet/… ... it should point you in the right direction. — Chef1075
There's a function I use when I'm catching the onEdit event to isolate my logic to only the range of cells in which I want to catch changes. Look at this answer to see what I'm talking about. In your case, you would use it to determine that the change is in one of your checkboxes, then use the state of the checkbox to generate the timestamp and put it in the right cell. — PeterT
Possible duplicate of Automatic timestamp when a cell is filled out — Rubén
Thanks very much for all the help. I've been able to get it partially working with the post you sent me @Rubén, but they just won't be removed when I untick the checkboxes, but the main function I needed works. Thanks very much for your help folks. — Robbie Stewart

Yossi K Yossi K · Accepted Answer · 2019-09-16T15:41:29

Here is the code i used for exact same scenario. probably not the most efficient code but it works, you can adjust it to your needs

    var timezone = "GMT-0400";
    var timestamp_format = "MM/dd/yyyy HH:00"; // Timestamp Format. 

    function onEdit() {   

    var s = SpreadsheetApp.getActiveSheet();
    if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();      //watches edited cell 

    if( r.getColumn() == 2 ) { //checks if edit is in column 2, 
        var nextCell = r.offset(0, 2);
    if( r.getValue() === false)
    nextCell.setValue(new Date()).setNumberFormat("dd-MM-yyyy HH:mm:ss");

    if( r.getColumn() == 2 ) { //checks the column 
        var nextCell = r.offset(0, 1);
    if( r.getValue() === true)
    nextCell.setValue(new Date()).setNumberFormat("MM-dd-yyyy hh:mm:ss");       

    if( r.getValue() === false)
    Browser.msgBox("Data refreshed.");

    };  

    };

    }
    }

On edit trigger that inserts timestamp dependant on checkbox value

1 Answers