Cannot get the column specific timestamp to display on checkbox

1
votes

I have set up a Google sheet for attendance of Employees on which clicking on the checkbox, the current time is recorded.

I cannot get the specific timestamp in the respective columns when the checkbox is ticked.

Clicking on a different checkbox also change the timestamp for previously checked checkboxes.

I have used this formula here : =If(B2 = TRUE,now(),"") and used filter handle to apply the formula for other rows underneath.

I have used this formula: =If(B2 = TRUE,now(),"") The sheet can be seen here: https://docs.google.com/spreadsheets/d/1p6jmnHXtCu2m7BdLfC-23A51MrwkJJJpXidpbsDwxfU/edit?usp=sharing

I want to record the current timestamp, for each row. i.e on clicking of the checkbox the current time should be changed/recorded only for the column in the same row and not for other records.

1
checkboxgoogle-apps-scriptgoogle-sheetsscriptingtimestamp
The problem is with the now() formula which you are using. It will refresh it's self every time the sheet recalculates. I am not sure what you want is possible without writing code to enter the timestamp at the time the checkbox is checked. – Gravitate

1 Answers

1
votes

this needs to be done with the script:

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet();
  { 
    var r = s.getActiveCell();
    if( r.getColumn() == 2 ) { 
      var nextCell = r.offset(0, 1);
      var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy hh:mm:ss");
      nextCell.setValue(newDate);
   }
}

how to add a script to your spreadsheet

  • go to Tools

  • select Script editor

    0

  • copy paste the script

  • save the project under some name

  • click on run icon and authorise it...

  • select your account

  • click on Advanced

  • select Go to * (unsafe)

  • click on Allow and return to your sheet (you can close script window/tab)