How do I get this script to only run when a specific column is edited?

Question

Based purely on the execution log it appears this script is running EVERY time anyone edits the sheet, but I only need it to run when column 3 or column 7 are edited. What do I add into the script to tell it to do this? I'm not super script literate so any help is greatly appreciated!!

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "FORD" ) {
    var r = s.getActiveCell();
    if( r.getColumn() == 3) {
     
      var nextCell = r.offset(0,19);
      if( nextCell.getValue() === '' )
        nextCell.setValue(new Date()).setNumberFormat('MM/dd/yyyy HH:mm:ss');
    }
    
    if( r.getColumn() == 7) {
      var nextCell = r.offset(0,16);
      if( nextCell.getValue() === '' )
        nextCell.setValue(new Date()).setNumberFormat('MM/dd/yyyy HH:mm:ss');
    }
  }
}

user_16118686 · Answer

Is it weird that your code actually works on mine? If I understood your issue correctly, you want your script to be ran when columns 3 and 7. I don't see any issues on your script but I think this approach below should be better. We will use the e object to get your data.

Code:

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

  if( s.getName() == "FORD" ) {
    var r = e.range;

    if( r.getColumn() == 3) {
      var nextCell = r.offset(0,19);
      if( nextCell.getValue() === '' )
        nextCell.setValue(new Date()).setNumberFormat('MM/dd/yyyy HH:mm:ss');
    }
    
    if( r.getColumn() == 7) {
      var nextCell = r.offset(0,16);
      if( nextCell.getValue() === '' )
        nextCell.setValue(new Date()).setNumberFormat('MM/dd/yyyy HH:mm:ss');
    }
  }
}

Practically the same logic your script does, but now, we do not get the data directly onto the sheet but the onEdit event object.

If you meant like the onEdit function should only be TRIGGERED during edits on column 3 and 7, that won't do.

onEdit function is triggered every time the sheet is edited manually, whatever range it is. We can only restrict the function on doing things by adding conditions but we can't practically stop triggering the function when a manual edit has been done.

It will always run on every manual edit, it is just a matter of restriction where we limit the function to do things based on what conditions we added. (Conditions such as running certain commands only if the sheet name is FORD and columns 3 or 7 are edited)

If I misunderstood your question, I sincerely apologize.

Edit:

I'm looking for other sources and it seems you can use switch-case instead of classic if-else statement as it is faster than the latter in large data. I've tested it and it seems it does have some significant difference in single tests. It will be significantly more faster in large data.

if-else:

switch-case:

Test:

The tests were the same on both if-else and switch-case.
First 3 tests are edits on column 3, second 3 tests were on column 7 and last 3 were non-3/7 edits
Run times are read bottom-up
This actually varies per test but pasted these results as it seems to be close to the average.
There are quotas that are in place and should be checked. See link for more details. You might need a paid subscription if you want more.