1

I'm currently using the below script to timestamp when an edit is made to column 7 on a sheet named "Sheet 1".

function onEdit(e) {
   if ([7].indexOf(e.range.columnStart) != -1) {
   e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");
   }  
}  

I have a second sheet "Sheet 2" which I need to run the same script on but against column 5.

function onEdit(e) {
   if ([5].indexOf(e.range.columnStart) != -1) {
   e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");
   }  
}  

I've tried .getSheetByName("Sheet1") but just cant seem to get it working correctly.

0

Please try the following piece of code.

Code

function onEdit(e) {
  var col = e.range.columnStart;
  if(col === 7 || col === 5) {
    var name = e.source.getActiveSheet().getName();
    if((name === 'Sheet1' && col === 7) || (name === 'Sheet2' && col === 5)) {
      e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");
    }
  }
}

Explained

The code reads as follows:

  • First check whether we're in column 5 or 7.
  • If true, only then retrieve the sheet name
  • If the sheet name and the column match, then execute the command

I think onEdit functions deserve some extra attention to avoid un-necessary calculations. If you were to include the sheet name, if would check the sheet name every time you change a cell. By restricting that to the columns only, you avoid that.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.