Google-sheets – Update column with last update of row in Google Sheets… except the header

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

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?

Best Answer

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

Related Solutions

Google-sheets – Want to email specific spreadsheet row data on event

Have an additional column, "flag" (leave it blank). Now, in your onEdit event:

Look for rows that have a "Closed" status and an empty flag field. (Search the matrix generated by SpreadsheetApp.getActiveSheet().getDataRange().getValues())
Now, for each row, do the following:
Find the email cell in that row (you know the column number), send the emails
Set the flag cell in that row to 1 or something

That's it :)

Google-sheets – How to loop a script through all pages but the first one

First, the error: shs is an array, since you populate it with ss.getSheets(), so it doesn't have a method getActiveCell(). However, the elements of the array has that method.

You are also referencing a variable sheet, which you haven't defined anywhere.

Currently, your code is more suitable for going through a single sheet, you need to iterate through the shs array to have it operate on all sheets.

The following function will go through all cells in all sheets:

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 0; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
      ...
   }
}

But you wanted to skip the first sheet, so let's start with element 1 in the array (since the array is 0-based, element 1 is the second sheet):

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 1; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
        var editedCell = shs.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99"; // What to sort.

        if(editedCell.getColumn() == columnToSortBy){   
          var range = sheet.getRange(tableRange);
          range.sort( { column : columnToSortBy, ascending: false });
        }
      }

   }
}

As for your comment, your code

function onEdit(event) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    for (var n in ss.getSheets()) {
        var sheet = ss.getSheets()[n];
        var name = sheet.getName();
        if (name == 'Dashboard') 
           return;
        var editedCell = ss.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99";
        if (editedCell.getColumn() == columnToSortBy) {
            var range = sheet.getRange(tableRange);
            range.sort({column: columnToSortBy, ascending: false});
        }
    }
}

could work, if you replace return with continue. You don't want return, it ends the script. continue skips to the next element in the for loop.