Following my previous post additional problem of deleting values previously preset by the (e) event object here How to automatically add the ordinal number of an entry based on timstamp to a row when non-contiguous new data is added to it in Google Sheets?
How to reach the result of the code below using a variable or a simpler method in the context of the current situation?
With the great help of @TheMaster and some more from @Tanaike here google script detect empty cell after edit I've found a workaround to delete values previously preset by the (e) event object.
Here's what I found:
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
}
}
Here the live result:
https://i.imgur.com/jWd4Erz.gif
What clearer and simpler way to reach the result would you recommend?
The result does what is expected. But is there a way to use a variable to reach the same result in the context the original code by @TheMaster below ?
The original code by @TheMaster link:
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;
//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
);
}
Thanks a lot for your help and ideas!