Google SpreadSheet Script to Set Current Date

Question

I have a google sheet script that moves a row from one sheet (Review) to another (Reviewed) when the checkbox in a cell (column 20) on the row is true. Please see a copy of the script below.

I need assistance in modifying the script so it sets a timestamp {setValue(new Date())} on the adjacent cell (21) when the cell (column 20) is set to true or as soon as it is moved to the target sheet (Reviewed). Thanks in advance.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Review" && r.getColumn() == 20 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Reviewed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);```

question from:https://stackoverflow.com/questions/65600326/google-spreadsheet-script-to-set-current-date

Answer 1

Explanation:

If you want to set the timestamp in the active sheet s then:

s.getRange(row,21).setValue(new Date());

or if you want in the target sheet targetSheet then:

targetSheet.getRange(targetSheet.getLastRow(),21).setValue(new Date());

Solution:

function onEdit(event){
  var ss = event.source;
  var s = ss.getActiveSheet();
  var r = event.range;
  if(s.getName() == "Review" && r.getColumn() == 20 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Reviewed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
    s.getRange(row,21).setValue(new Date());  
  //targetSheet.getRange(targetSheet.getLastRow(),21).setValue(new Date()); 
  }
}