Skip to content
Advertisement

Running an onEdit Script on selected sheets only

I am new to coding Scripts but have managed to copy and edit (bodge…) a script that will onEdit, put a custom timestamp into the cell next to the cell where the edit took place. The script targets x2 onEdit columns (3,6), timestamping the cell offset(-1).

The workbook has developed to include multiple sheets now, most of which this script is not appropriate to run on, but I cannot figure out how to specify which sheets it should run on.

The below is the script I have cobbled together which works across all sheets, which I would now like to restrict to specific sheets only.

function onEdit(e) {
 var colsToWatch = [3,6],
    offset = [-1,-1],
    ind = colsToWatch.indexOf(e.range.columnStart);
if (ind === -1 || e.range.rowStart === 1) return;
e.range.offset(0, offset[ind])
.setValue(!e.value ? null : Utilities.formatDate(new Date(), "GMT+1", "dd MMM yyyy     HH:mm.ss"))
}

Please can someone help me by providing the code to run the scripts on specific sheets only. I would also be grateful for any suggestions of a simpler script to achieve the same aim, especially if it eliminates the timestamp being overwritten if the onEdit cell is subsequently edited after the initial edit.

Many thanks!!

Advertisement

Answer

function onEdit(e) {
  const sh = e.range.getSheet();
  const shts = ['Sheet1','Sheet2'];
  const idx = shts.indexOf(sh.getName());
  if(~idx ) {
    //Whatever code you put here will only run on Sheet1 or Sheet2
  }
}

This works for me (on on Sheet1 and Sheet2):

function onEdit(e) {
  //e.source.toast('entry');
  const sh = e.range.getSheet();
  const shts = ['Sheet1','Sheet2'];
  const idx = shts.indexOf(sh.getName());
  if(~idx ) {
    let rg = sh.getRange(e.range.rowStart,9);
    if(rg.isBlank()) {
      rg.setValue(new Date());
    }
  }
}

JavaScript Reference

Advertisement