Home > Software engineering >  Timestamp in Cell when any cell in row is edited
Timestamp in Cell when any cell in row is edited

Time:03-02

I'm an absolute rookie - so I appreciate any insight/ suggestions on where I'm (obviously going wrong)

I have a sheet where I'm trying to get a date stamp in column B when the same row is edited (apart from column B) I would like the date stamp to be in the same row as the row that's being edited.

I've had a bash at the script (and failed)

I have 2 x onEdit functions, so the trigger would have to be myFunction etc

Below is the attempted script:

FYI - the data in the sheet is from column B11 to AQ

function myFunction(e)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Position Activity'); //
{
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(1)   1;
var orderCol = headers[0].indexOf(23)   1;
if (dateCol > 0 && rowIndex > 1 && e.range.columnStart > 1)
{
  sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "CET", 
"dd-mmm-yyyy"));
}
}

}

Thanks in advance

CodePudding user response:

You can use Simple Trigger onEdit() and use that event to get which range is edited and from which sheet, you can also get new value and old value.

Below is the sample code:

   function onEdit(e){       
      var editedRange = e.range; // Using Object e to get the range
      var r = editedRange.getRow() // row which is edited
      var c = editedRange.getColumn() // column which is edited
      var wsName = editedRange.getSheet()
      if(wsName.getName() === "Position Activity" && c !== 2) // skipping column B
     {       
        wsName.getRange(r, 2).setValue(Utilities.formatDate(new Date(), "CET", 
                                       "dd-mmm-yyyy")) // Adding Date in column b
    
      }
    }

Reference:

onEdit

Event Object

  • Related