CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetstriggers
Ryan Kochan
Ryan Kochan

Reputation: 5

Update Timestamp when ANY cell is row is edited

I have a sheet with 10 columns, Currently I have a script that will update Column A "Date Called" when Column C "Status" Is updated

function onEdit(e)
{
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == "Hiring Leads") //
  {
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf("Date Called") + 1;
    var orderCol = headers[0].indexOf("Status") + 1;
    if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
    {
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "MM-dd-yyyy"));
    }
  }
}

What I need is a script so that when ANY cell is edited, it will update the "Date Called" cell in A

If I removed the && editColumn ==orderCol in the 12th line of code it will update when any column is edited, the problem is that it will also update even if Column A "Date Called" is edited, which I need to avoid. I need "Date Called" Updated when any cell in the row (except itself in row A) has edits to it

Upvotes: 0

Views: 157

Answers (1)

Wicket
Wicket

Reputation: 38160

Replace

&& editColumn ==orderCol

by

&& e.range.columnStart > 1

Also you might remove

var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();

and replace

  • actRng by e.range
  • rowIndex by e.range.rowStart

Upvotes: 1

Related Questions