Google Sheets App Script Goal: Timestamp todays date in next empty column cell whenever checkbox is-CodePudding

I'm trying to create a Google Sheet to record each day a certain activity is done (watering plants). I have written a script so that when the checkbox in each row is checked (TRUE), todays date is timestamped into the adjacent column cell. But I can't figure out how to timestamp a new "todays date" into the next empty cell in the same row each time I activate the checkbox. I'd like to keep adding new dates the activity is performed into the adjacent empty cells in the same row. (ultimately I want to then average the number of days between each date so I can collect data and average the number of days between waterings for each plant - which is a different formula challenge). The working script I have created is:

   function onEdit(e){
  if(e.value != "TRUE") return;
  e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart   1).setValue(new Date()).setNumberFormat("dd/MM/yyyy");
}

Here is a screenshot of the sheet and what I would like to achieve: Screenshot visualisation of what I want the script to be able to do

Much appreciated if anyone could help me out with a working code. Thanks!

CodePudding user response：

Try

function onEdit(e){
  if(e.value != "TRUE") return;
  var cel = e.source.getActiveRange()
  if (cel.offset(0,1).getValue()!=""){cel = e.source.getActiveRange().getNextDataCell(SpreadsheetApp.Direction.NEXT)}
  cel.offset(0,1).setValue(new Date()).setNumberFormat("dd/MM/yyyy");
}

CodePudding user response：

Try this:

function onEdit(e){
  const sh = e.range.getSheet();
  if(sh.getName() == 'Your sheet name' && e.value == "TRUE") {
    e.range.offset(0,1).setValue(new Date()).setNumberFormat("dd/MM/yyyy");
  }
}