Google-sheets – Update a timestamp in a cell only when specific text is entered in another cell

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

Looking for a timestamp to be created in Google Sheets in cell B2 based on the text "Loading-Outside" being entered into cell A2. I know the below script will work for any edit made to the cell, but need the timestamp to ONLY be created when this specific text is entered. Furthermore, when the text is changed again to something different, I want the timestamp to remain unchanged. Is this possible?

function onEdit(e) {
  if (e.range.getColumn() == 1) {
    e.range.offset(0, 1).setValue(new Date());
  }
}

Best Answer

The onEdit event object has property "value" that can be used for this purpose. But it turns out that e.value is the empty object {} when the edit consists of copy-paste. So I use e.range.getValue() as a backup below, only when e.value does not deliver.

function onEdit(e) {
  var value = (typeof e.value == "object" ? e.range.getValue() : e.value);
  if (e.range.columnStart == 1 && value == "Loading-Outside") {
    e.range.offset(0, 1).setValue(new Date());
  }
}

The match is case-sensitive; "loading-outside" will not trigger the timestamp.

Note that only the script you have in the question puts a timestamp anywhere in column B when the corresponding cell of column A is changed. If you wanted this to happen only specifically in row 2, then add a check for row: && e.range.rowStart == 1.

Related Topic