4

I've been using this code to find the most recent update of a given row:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
if( r.getColumn() != 5 ) { //checks the column
var row = r.getRow();
var time = new Date();
time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
SpreadsheetApp.getActiveSheet().getRange("E" + row.toString()).setValue(time);
};
};

The problem is that I'd like to have a header on column 'E' and I need to make sure that the script doesn't overwrite the header if someone changes something in that header row. I've tried using "E2:E" or "E2:1000" as a replacement in getRange but the effect is that when any row is updated, ALL of column E gets updated with the timestamp.

Question is: how do exclude Row 1 from being overwritten by this script?

Improve this question

1 Answer 1

Reset to default
3

Short Answer

Replace if( r.getColumn() != 5 ) by if( r.getColumn() != 5 && r.getRow() != 1 ) .

Long answer

&& is the AND operator r.getRow() != 1 compares the row of the edited cell with the row number that you want to be skipped.

Full code minimal modification

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange("E" + row.toString()).setValue(time);
  };
};

Full code improved

Taking advantage that onEdit() event gets an event object that include the range.

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    s.getRange("E" + row.toString()).setValue(time);
  };
};

References

Improve this answer
0

Your Answer

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

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