1

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());
  }
}
1

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.

  • 1
    You can use e.range.rowStart and e.range.columnStart instead, to reduce the API calls. – Jacob Jan Tuinstra Jul 12 '17 at 17:42
  • Awesome, thank you. Problem now is the function only runs when Loading-Outside is keyed in directly, not if its copy/pasted or pulled from a drop down. Any fix for that? – Bowen Williams Jul 13 '17 at 18:10
  • @BowenWilliams It worked for me with the data validation dropdown, but you are right about pasting. I changed the way of getting the cell value so copy-paste should work too. – user135384 Jul 13 '17 at 19:50

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.