在我上一篇文章之后,如何删除以前由Google表格中简单触发器onEdit()的事件对象(e)设置的单元格值?我发现粘贴到空白单元格B2中时无法打印到A2中。
再次是下面的代码:
function onEdit(e) {
var rg = e.range,
row = rg.rowStart,
col = rg.columnStart,
sht = rg.getSheet();
//exit code
if (col !== 2 || sht.getName() !== 'Sheet1' || row === 1) return;
// When the empty cell is edited, this becomes true.
if ((e.value != null) && (e.oldValue == null)){
//Calculate max value and add 1
rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
rg
.offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
.getValues()
.reduce(function(acc, curr) {//get max of colA
return Math.max(acc, Number(curr[0]));
}, 0) + 1
);
}
// When the value of cell with a value is removed, this becomes true.
else if ((e.value == null) && (e.oldValue == null)) {
rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
rg
.offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
.getValues()
.reduce(function(acc, curr) {//get max of colA
return Math.max(acc, Number(curr[0]));
}, 0)
).clearContent(); // If we delete cell
}
}
我试图理解为什么在这种情况下
((e.value != null) && (e.oldValue == null))
代码在B2中键入值时有效,但在将值粘贴到空白B2中时无效。
如果这是负责任的区别,那么编辑/键入和编辑/粘贴之间的根本区别是什么?
这是一个现场演示:
https://i.imgur.com/03Uoh7F.gif
复制并粘贴到空白B2中时,要在序号中打印到A2的代码中缺少什么?
在这里,带有@TheMaster和@Tanaike的演示已完成:
编辑(新正确的gif):
https://i.imgur.com/mFKUnTL.gif
结果相似(粘贴到非空白单元格时B2中也没有值):
function onEdit(e) {
var rg = e.range,
row = rg.rowStart,
col = rg.columnStart,
sht = rg.getSheet();
//exit code
if (col !== 2 || sht.getName() !== 'Sheet1' || row === 1) return;
// When the empty cell is edited, this becomes true.
if ((e.value != null) && (e.oldValue == null)){
//Calculate max value and add 1
rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
rg
.offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
.getValues()
.reduce(function(acc, curr) {//get max of colA
return Math.max(acc, Number(curr[0]));
}, 0) + 1
);
}
//COMPLETED PIECE
// When the cell with a value is overwritten by a value, this becomes true.
else if(e.oldValue!=undefined) {
//Calculate max value and add 1
rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
rg
.offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
.getValues()
.reduce(function(acc, curr) {//get max of colA
return Math.max(acc, Number(curr[0]));
}, 0) + 1
);
}
//COMPLETED PIECE
// When the value of cell with a value is removed, this becomes true.
else if ((e.value == null) && (e.oldValue == null)) {
rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
rg
.offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
.getValues()
.reduce(function(acc, curr) {//get max of colA
return Math.max(acc, Number(curr[0]));
}, 0)
).clearContent(); // If we delete cell
}
}
复制并粘贴到非空白B2时,将序号值打印到A2的代码中缺少什么?
为了更好地理解这个问题,今天我测试了16培训相关onEdit
使用时的情况e.oldValue
,并e.Value
与逻辑运算的测试!==
,==
和&&
。
这是结果(根据Sheet2示例(在Sheet2(和Sheet1)的gif文件中编号):
1。
(e.oldValue == undefined) && (e.Value == undefined)
https://i.imgur.com/JHD1mpS.gifv
2。
(e.oldValue !== undefined) && (e.Value == undefined)
https://i.imgur.com/zlCYJ1N.gifv
3。
(e.oldValue == null) && (e.Value == undefined)
https://i.imgur.com/Zp2uutV.gifv
4。
(e.oldValue !== null) && (e.Value == undefined)
https://i.imgur.com/XtT1MAt.gifv
9。
(e.oldValue == undefined) && (e.Value == null)
https://i.imgur.com/AEDWkHf.gifv
11。
(e.oldValue == null) && (e.Value == null)
https://i.imgur.com/5BE5vGd.gifv
12
(e.oldValue !== null) && (e.Value == null)
https://i.imgur.com/ygqfmKe.gifv
13
(e.oldValue == undefined) && (e.Value !== null)
https://i.imgur.com/clTkSRx.gifv
15
(e.oldValue == null) && (e.Value !== null)
https://i.imgur.com/Whc2fcW.gifv
16。
(e.oldValue !== null) && (e.Value !== null)
https://i.imgur.com/R1xiI8M.gifv
乍一看与逻辑上的期望相反,
它返回1、2、3、4、9、11、12中的值。
根据逻辑,它仅返回13、15和16中的值。
5,
(e.oldValue == undefined) && (e.Value !== undefined)
https://i.imgur.com/MGL0gVk.gifv
6。
(e.oldValue !== undefined) && (e.Value !== undefined)
https://i.imgur.com/jA7dcbf.gifv
7。
(e.oldValue == null) && (e.Value !== undefined)
https://i.imgur.com/bvczvde.gifv
8。
(e.oldValue !== null) && (e.Value !== undefined)
https://i.imgur.com/Ou9zuhb.gifv
10。
(e.oldValue !== undefined) && (e.Value == null)
https://i.imgur.com/1O4jQE3.gifv
14。
(e.oldValue !== undefined) && (e.Value !== null)
https://i.imgur.com/hS6baxV.gifv
乍一看与逻辑上的期望相反,
它在5、7、8、14中不返回任何值。
按照逻辑,它仅不返回6和10中的值。
这里所有的Gif:
这是今天的代码:
function onEdit(e) {
var rg = e.range,
row = rg.rowStart,
col = rg.columnStart,
sht = rg.getSheet();
//exit code
if (col !== 2 || sht.getName() !== 'Sheet1' || row === 1) return;
// When the empty cell is edited, this becomes true.
else if (EDITS HERE) {
//Calculate max value and add 1
rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
rg
.offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
.getValues()
.reduce(function(acc, curr) {//get max of colA
return Math.max(acc, Number(curr[0]));
}, 0) + 1
)
}
}
我明天将看到它与添加的.clearContents()
动作结合后的行为。
感谢@theMaster敏锐的眼睛,我意识到我以前的测试(于2019年6月5日完成)确实存在缺陷,因为我使用了e。V
用alue代替e。v
好的
下面是我之前16项测试的更正视频下方:
1。
(e.oldValue == undefined) && (e.value == undefined)
2。
(e.oldValue !== undefined) && (e.value == undefined)
3。
(e.oldValue == null) && (e.value == undefined)
4。
(e.oldValue !== null) && (e.value == undefined)
5,
(e.oldValue == undefined) && (e.value !== undefined)
6。
(e.oldValue !== undefined) && (e.value !== undefined)
7。
(e.oldValue == null) && (e.value !== undefined)
8。
(e.oldValue !== null) && (e.value !== undefined)
9。
(e.oldValue == undefined) && (e.value == null)
10。
(e.oldValue !== undefined) && (e.value == null)
11。
(e.oldValue == null) && (e.value == null)
12
(e.oldValue !== null) && (e.value == null)
13
(e.oldValue == undefined) && (e.value !== null)
14。
(e.oldValue !== undefined) && (e.value !== null)
15
(e.oldValue == null) && (e.value !== null)
16。
(e.oldValue !== null) && (e.value !== null)
结果记录在前面的表格中:
我明天将报告逻辑对结果的综合。
再次感谢@TheMaster提供的有益观察!
如果我的理解是正确的,那么这个答案呢?
当剪贴板的值被粘贴到一个细胞,两者e.value
和e.oldValue
的e
所述OnEdit事件对象的成为null
。在这种情况下,值与删除带有值的单元格的值时的情况相同。但是有一个不同的单元格值。这是单元格是否有值。在这种情况下,可以使用。
在使用以下示例脚本之前,请安装OnEdit触发器以将其sampleEvent()
用作可安装触发器。
Logger.log()
当剪贴板的值直接粘贴到单元格时,这是用于运行的示例脚本。
function sampleEvent(e) {
if ((e.value == null) && (e.oldValue == null) && (e.range.getValue() != "")) {
// When the value of the clipboard is directly pasted to a cell, this script is run.
Logger.log("Value of the clipboard was pasted to a cell.")
}
}
e.value
,e.oldValue
和e.range.getValue()
是null
,null
和""
分别。e.value
,e.oldValue
和e.range.getValue()
是null
,null
和"### pasted value ###"
分别。如果要在以下情况下分隔脚本,该示例脚本如何?
在此示例脚本中,当触发OnEdit事件触发器时,将打开一个对话框。
function sampleEvent(e) {
var prompt = "";
var cellValue = e.range.getValue();
if ((e.value != null) && (e.oldValue == null)) { // When the empty cell is edited, this becomes true.
prompt = "Empty cell was edited.";
} else if(e.oldValue != undefined) { // When the cell with a value is overwritten by a value, this becomes true.
prompt = "Cell with a value was overwritten by a value.";
} else if((e.value == null) && (e.oldValue == null) && (cellValue == "")) { // When the value of cell with a value is removed, this becomes true.
prompt = "Value of cell with a value was removed.";
} else if((e.value == null) && (e.oldValue == null) && (cellValue != "")) { // When the value of the clipboard is directly pasted to a cell, this becomes true.
prompt = "Value of the clipboard was directly pasted to a cell.";
}
SpreadsheetApp.getUi().alert(prompt);
}
如果要将剪贴板的值直接粘贴到“ B”列的单元格时将“序号”放入“ A”列,该示例脚本如何?我认为该示例脚本可能接近您的目标。
function sampleEvent(e) {
var prompt = "";
var cellValue = e.range.getValue();
if ((e.value != null) && (e.oldValue == null)) { // When the empty cell is edited, this becomes true.
prompt = "Empty cell was edited.";
putOrdinalNumber(e); // Put the Ordinal Number
} else if(e.oldValue != undefined) { // When the cell with a value is overwritten by a value, this becomes true.
prompt = "Cell with a value was overwritten by a value.";
} else if((e.value == null) && (e.oldValue == null) && (cellValue == "")) { // When the value of cell with a value is removed, this becomes true.
prompt = "Value of cell with a value was removed.";
} else if((e.value == null) && (e.oldValue == null) && (cellValue != "")) { // When the value of the clipboard is directly pasted to a cell, this becomes true.
prompt = "Value of the clipboard was directly pasted to a cell.";
putOrdinalNumber(e); // Put the Ordinal Number
}
SpreadsheetApp.getUi().alert(prompt);
}
function putOrdinalNumber(e) {
var range = e.range;
var sheet = range.getSheet();
if (range.getColumn() == 2 && sheet.getName() == 'Sheet1' && range.getRow() != 1 && range.offset(0, -1).getValue() == "") {
var n = sheet.getRange("A2:A" + sheet.getLastRow()).getValues().filter(function(e) {return e[0] > 0}).length;
range.offset(0, -1).setValue(n + 1);
}
}
如果我误解了您的问题,而这不是您想要的结果,我深表歉意。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句