CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
redpandasuit
redpandasuit

Reputation: 63

Google Sheets Timestamp onEdit Range Issue

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());
}

Upvotes: 1

Views: 1314

Answers (2)

JPV
JPV

Reputation: 27262

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

Upvotes: 1

rpm
rpm

Reputation: 583

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());
}

Upvotes: 2

Related Questions