Google Sheets Timestamp onEdit Range Issue

1
votes

I've been working on a Google spreadsheet that contains multiple sheets and my current goal is to be able to have an onEdit timestamp functioning on both sheets (and any future sheets that I may add) that will appear in the column (eg. if edited cell is A1, then timestamp appears in B1) next to the cell. I've had varying degrees of success by shoving scripts I found on here together in hopes that they will play nice. Currently I have it sorta working the way I want...

BUT If I delete the newly created timestamp in B1 then it will produce a new one in C1 and if I delete C1 then it appears in D1 and so on and so forth. Is there a way to prevent the timestamp from being produced up deletion of a cells contents?

function onEdit(e) {
    var s = SpreadsheetApp.getActiveSheet();
    var cols = [1, 3, 5]
    if (s.getName() == "Sheet1")
    s.getRange(e.range.rowStart, e.range.columnStart + 1)
    .setValue(new Date());

    if (s.getName() == "Sheet2")
    s.getRange(e.range.rowStart, e.range.columnStart + 1)
    .setValue(new Date());
}
2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

It's being propagated because you have written e.range.coloumnStart + 1. So as soon as you delete value of any cell, onEdit() will be fired. So that cell will be counted as the first column and it will update the value of next cell relatively. To stop being propagated to next cell when deletion happen, you should not update the next cell value on deletion.

To do this, you can do in following way, (I don't know what is your exact requirement, but this code will work for your current requirement)

function onEdit(e) {
    var s = SpreadsheetApp.getActiveSheet();
    var cols = [1, 3, 5];
    if (s.getName() == "Sheet3" && e.range.getValue() != '')
      s.getRange(e.range.rowStart, e.range.columnStart+1).setValue(new Date());


    if (s.getName() == "Sheet4" && e.range.getValue() != '')
      s.getRange(e.range.rowStart, e.range.columnStart+1).setValue(new Date());
}
1
votes

Alternatively, you can also try:

function onEdit(e) {
var s = e.source.getActiveSheet().getName();
var cols = [1, 3, 5];
if (s !== 'Sheet1' && s !== 'Sheet2' || cols.indexOf(e.range.columnStart) ==-1 || !e.value) return;
e.range.offset(0,1).setValue(new Date());
}

I assumed you wanted to limit the stamping to columns 2, 4 and 6 (so for edits being done in columns 1, 3 and 5 ?) If not you can delete the 'var cols' and 'cols.indexOf..'-part