How to empty adjacent cell if edited cell is blank

2019-07-27 13:36发布

My Apps Script code timestamps an adjacent cell based on the watchCols when a cell is edited.

What I need to do is remove the timestamp if the user removes a value. E.g. they put an X in column 2 in any row if they complete one of a list of tasks, but if they remove the X meaning they didn't finish the task, there should be no timestamp beside it.

function onEdit(e) 
{
    var s = e.source.getActiveSheet(),
    watchCols = [2, 5, 8, 11, 14, 17],
    offsetCol = [1, 1, 1, 1, 1, 1],
    ind = watchCols.indexOf(e.range.columnStart);
    if (s.getName() !== "Sheet1" || ind === -1) return;
    e.range.offset(0, offsetCol[ind])
    .setValue(!e.value ? null : Utilities.formatDate(new Date(), "GMT-4", "YYYY-MMM-dd hh:m:s a"))
}

This is the last attempt I made which doesn't seem to work:

    if(ISBLANK(e.range.columnStart) && ISBLANK(e.range.rowStart)) {
    e.range.offset(0, offsetCol[ind]).setValue("");
    } else {
    e.range.offset(0, offsetCol[ind]).setValue(!e.value ? null : Utilities.formatDate(new Date(), "GMT-4", "YYYY-MMM-dd hh:m:s a"))
    }

1条回答
走好不送
2楼-- · 2019-07-27 14:13

This is an undocumented behavior of the edit event object, which I'd consider a bug. It behaves differently for installable on-edit triggers and the simple onEdit trigger, with regard to a user clearing a cell.

  1. Installable trigger: e.oldValue has the old value, e.value is undefined (the event object has no value property).
  2. Simple trigger: e.oldValue has the old value, e.value holds the object {"oldValue":"whatever it was"}

This is why your conditional statement with !e.value behaves differently in two cases: undefined is falsy, but an object is truthy.

查看更多
登录 后发表回答