0

In Google Sheets I have a column that needs to be updated with the current date & time every time another cell in the same row is updated, but skip the top row which is the header. I found this script from a YouTube video that works great except it updates the entire column. I either need it to exclude the top row or start on row 2 or specify which row to start on if I decide to add more header rows at the top.

I am not a programmer at all, so any help is greatly appreciated.

function onEdit() {
   var s = SpreadsheetApp.getActiveSheet();
   var r = s.getActiveCell();
   if( r.getColumn() != 2 ) 
   { 
      var row = r.getRow();
      var time = new Date();
      time = Utilities.formatDate(time, "GMT+05:30", "dd/MM/yyyy, hh:mm:ss");
      SpreadsheetApp.getActiveSheet().getRange('I' + row.toString()).setValue(time);
   };
}

1 Answer 1

0
function onEdit() {
   var s = SpreadsheetApp.getActiveSheet();
   var r = s.getActiveCell();
   var row = r.getRow();
   if( r.getColumn() != 2 && row > 2) 
   {
      var time = new Date();
      time = Utilities.formatDate(time, "GMT+05:30", "dd/MM/yyyy, hh:mm:ss");
      SpreadsheetApp.getActiveSheet().getRange(9, row).setValue(time);
   };
}

Google apps script is built using javascript. The && is a logical and. This means that the block under the if will only execute if both of the criteria are true. To prevent the code from running on the top rows, the only modification needed was this extra check.

2
  • Sorry, but no, this does not work. The script doesn't work at all with the && row > 2)
    – user198427
    Feb 26, 2019 at 22:50
  • Sorry, but no, this does not work right. It puts the date/time in Row 9, column C, no matter where I make a change in the spreadsheet.
    – user198427
    Feb 26, 2019 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.