Google-sheets – Add timestamp to two different columns in Google Spreadsheets

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

Need help in adding another function using the below script.
I would like to have a timestamp "start" and "end" on two different columns, whenever the other two separate columns are edited. Based on the below script, I could only have one time stamp, from one column which is the column 2 and time stamp on column 10.

function onEdit() 
{
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var x = s.getActiveCell();
   if( x.getColumn() == 2 ) { //checks the column
     var nextCell = x.offset(0, +8);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
     time = Utilities.formatDate(time, "GMT", "hh:mm");
       nextCell.setValue(time);
   };
 };
}

Best Answer

See if this helps ?

function onEdit(e) {
var s = e.source.getActiveSheet(),
    watchCols = [2, 5],
    offsetCol = [8, 10],
    ind = watchCols.indexOf(e.range.columnStart)
    if (s.getName() !== "Sheet1" || ind === -1) return;
e.range.offset(0, offsetCol[ind])
    .setValue(!e.value ? null : Utilities.formatDate(new Date(), "GMT", "hh:mm"))
}

Code

I've reduced your code to the following:

function onEdit(e) {
  var sName = e.source.getActiveSheet().getName();

  if(sName !=  'Image Timeline and Status') {
    var s = e.source.getActiveSheet();
    var c = s.getActiveRange().getColumnIndex();
    var r = s.getActiveRange().getRowIndex();

    if(c == 8 || c == 9 || c == 10) {
      var username = Session.getActiveUser().getUsername();
      var datestamp = new Date(); 
      var rCol = 5 + (c-8);
      s.getRange(r,rCol).setValue(username);
      s.getRange(r,13).setValue(datestamp);
    }
  }
}

Remarks

The user name (getUsername) should be created, only if the criteria of the columns are met. The same accounts for the date. Therefore these two are placed within the second IF statement.

It's also valid for the column and row indices. These are only created when the sheetname criterion is met.

Given the logic of the IF statements and the additional check, I've come up with only two IF statements.

The changes made, follow logic and will minimize API calls, but the effect on the execution time will be minimal.

Google-sheets – Auto Sort Google Sheet with onEdit function

If you want to sort only the active sheet, it's simpler:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn());
  range.sort({column: 1, ascending: false});
}

You had sheet.getLastRow() - 1 for the number of rows in the range, which doesn't seem correct if you start the range from the 3rd row. I put -2.

Best Answer

Related Solutions

Google-sheets – Why would this Google Spreadsheets onEdit script be so slow (2 secs to change cells, 3 secs to update)

Code

Remarks

Google-sheets – Auto Sort Google Sheet with onEdit function

Related Topic