I'm using a script on Google Sheets that makes a timestamp on a cell when the value of the cell next to it is set. It clears the date if the value of the cell next to it is deleted.

However, it's affecting the entire sheet. I only want this code to function starting at row 18.

I'm very new to coding. I've done some research but still can't figure it out. I have a variable defined called StartRow and tried using that in my if statement and then my loop instead of "e.range.rowStart" but it triggered the date several times upward.

Can someone please give me feedback on what I may need to change or add? Thanks in advance!

Here's the code I'm using:

function onEdit(e) {
  var sheet = ["Sheet1"];
  var ss = SpreadsheetApp.getActiveSheet();

  var StartRow = 18; //(defined but not sure where or if I should use it)
  var InputCol = 6;
  var OutputCol = 7;

  if (sheet.indexOf(ss.getName()) == -1 || e.range.columnStart != InputCol) return;

  var val = ss.getRange(e.range.rowStart, e.range.columnStart).getValue();
  if (val) {
    var date = new Date();
    for (var i = e.range.rowStart; i <= e.range.getLastRow(); i  )
      ss.getRange(i, OutputCol).setValue(date);
  } else
    ss.getRange(e.range.rowStart, OutputCol, e.range.getLastRow() - e.range.rowStart   1).clearContent();
}

CodePudding user response：

Try this:

function onEdit(e) {
  //e.source.toast('entry');
  const sh = e.range.getSheet();
  if (sh.getName() == 'Sheet1' && e.range.rowStart > 18 && e.range.columnStart == 6) {
    if (e.value) {
      e.range.offset(0, 1).setValue(new Date());
    } else {
      e.range.offset(0, 1).setValue('');
    }
  }
}