如果编辑的单元格为空,如何清空相邻单元格? [英] How to empty adjacent cell if edited cell is blank?
问题描述
我需要做的是删除时间戳,如果用户删除了一个时间戳记值。例如。如果他们完成了任务列表中的某个任务,他们会在第2列中放置一个X,但如果他们删除了X,表示他们没有完成任务,那么它旁边应该没有时间戳记。
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:sa))
}
这是最后一次尝试I (看起来不起作用):
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:sa))
}
这是编辑事件对象的未记录行为,我会考虑一个错误。对于可安装的on-edit触发器和简单的onEdit触发器,对于用户清除单元格,它的行为不同。
- 可安装的触发器:
e.oldValue
具有旧值e.value
是未定义的(事件对象没有值
属性)。
- 简单的触发器:
e.oldValue
具有旧值,e.value
包含对象{oldValue:无论它是什么}
这就是为什么你的条件语句!e.value
在两种情况下表现不同:未定义是虚假的,但对象是真实的。
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"))
}
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.
- Installable trigger:
e.oldValue
has the old value,e.value
is undefined (the event object has novalue
property). - 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.
这篇关于如果编辑的单元格为空,如何清空相邻单元格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!