CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
PyNext
PyNext

Reputation: 15

Google spreadsheet auto timestamp: I don't want timestamp when I delete cell content

When I try to delete the content of the cell that the script check, the script paste a new time stamp. I need to only use the script when I write a new value, not when I delete the value.

function onEdit() {
  
  var COLUMNTOCHECK = 1;
  var DATETIMELOCATION = [0,1];
  var SHEETNAME = 'STOCKOUT' 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();
    if( selectedCell.getColumn() == COLUMNTOCHECK) { 
      var balance = sheet.getRange("i1").getValue();
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue([balance]);
      }
  }

Here's an example:

When I update column A and C, and I try to delete A, B, C, I, script it will run and paste a new value on column B Google Spreadsheet Example

Upvotes: 1

Views: 469

Answers (2)

Tanaike
Tanaike

Reputation: 201358

How about this modification? When a value is deleted from a cell, the event object e of onEdit(e) has e.value.oldValue. Using this, your script can be written as follows.

Modified script:

function onEdit(e) { // Modified
  if (!e.value.oldValue) { // Added
    var COLUMNTOCHECK = 1;
    var DATETIMELOCATION = [0,1];
    var SHEETNAME = 'STOCKOUT' 
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();

    if( sheet.getSheetName() == SHEETNAME ) { 
      var selectedCell = ss.getActiveCell();
        if( selectedCell.getColumn() == COLUMNTOCHECK) { 
        var balance = sheet.getRange("i1").getValue();
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
        dateTimeCell.setValue([balance]);
      }
    }
  }
}

Reference:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

I thought 3 situations for editing a cell.

  1. A value is put to the empty cell.

    • e.value is included in the event object.
      • oldValue is not included in e.value.
    • e.oldValue is not included in the event object.

  2. The existing value of cell is edited.

    • e.value and e.oldValue are included in the event object.
      • oldValue is not included in e.value.

  3. The value is deleted from a cell.

    • e.value and e.oldValue are included in the event object.
      • oldValue is included in e.value.

I thought that the pattern 3 can be used for OP's situation.

Upvotes: 1

Cooper
Cooper

Reputation: 64040

Try this:

function onEdit(e) {  
  if( e.range.getSheet().getName()=='STOCKOUT' && e.range.columnStart==1) {
      e.range.offset(0,1).setValue((e.value)?e.range.getSheet().getRange("I1").getValue():'');
  }else{
    return;
  }
}

Upvotes: 0

Related Questions