Revisions to Update column with last update of row in Google Sheets... except the header

deleted 1 character in body

Source Link

edited Oct 15, 2015 at 21:28

46.1k
18
100
297

#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");
    SpreadsheetApp.getActiveSheet()s.getRange("E" + row.toString()).setValue(time);
  };
};

#References

#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
    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;  
  if( r.getColumn() != 5 && r.getRow() != 1) { //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);
  };
};

#References

#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

added 659 characters in body

Source Link

edited Oct 15, 2015 at 21:01

Rubén - Volunteer Moderator - ♦

46.1k
18
100
297

#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##Full code minimal modification

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //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);
  };
};

##Full code improved

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

function onEdit(e) {
  var r = e.range;  
  if( r.getColumn() != 5 && r.getRow() != 1) { //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);
  };
};

#References

#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

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //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);
  };
};

#References

JavaScript Comparison and Logical Operators - w3schools.com

#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
    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;  
  if( r.getColumn() != 5 && r.getRow() != 1) { //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);
  };
};

#References

added 727 characters in body

Source Link

edited Oct 15, 2015 at 16:43

Rubén - Volunteer Moderator - ♦

46.1k
18
100
297

Replace#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

iffunction onEdit() r{
  var s = SpreadsheetApp.getColumngetActiveSheet();
 != 5var r = s.getActiveCell();

by

  if( r.getColumn() != 5 && r.getRow() != 1) { //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);
  };
};

#References

JavaScript Comparison and Logical Operators - w3schools.com

Replace

if( r.getColumn() != 5 )

by

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

#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

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //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);
  };
};

#References

JavaScript Comparison and Logical Operators - w3schools.com

Post Undeleted by Rubén - Volunteer Moderator -♦

occurred Oct 15, 2015 at 16:36

Post Deleted by Rubén - Volunteer Moderator -♦

occurred Oct 15, 2015 at 16:28

Source Link

created Oct 15, 2015 at 16:27

Rubén - Volunteer Moderator - ♦

46.1k
18
100
297

Loading

Stack Exchange Network

Return to Answer