How can I get the oldValue of a cell, when the user deletes cell content? e.oldValue is undefined in this case

Question

How can I get the oldValue of a cell, when the user deletes the content?

The purpose of this is to prevent accidental deletion / edit when novice users use a shared spreadsheet. Only empty cells should be updated without a warning.

It works when editing the content of the cell. But when deleting the cell content, oldValue is undefined - the cell had content.

PS. I know this only works for single cell edits - I had a check for range width / height == 1, but that is also not true for a value delete. (select cell, press delete)

My test code below:

function onEdit(e) {
  Logger.log("onEdit called") 
  Logger.log("e.oldValue: %s", e.oldValue) 
  Logger.log("e.value: %s", e.value)
  Logger.log(e) 
  if (!("value" in e) || (e.oldValue != undefined)) {
     if (shouldRevertChange(e.oldValue, e.range.getValue())) {
        e.range.setValue(e.oldValue);
     }
   }
}

function shouldRevertChange(oldValue, newValue) {
  var ui = SpreadsheetApp.getUi();

  var result = ui.alert(
     'Was this a mistake?',
    'You changed the contents of a cell. Old value: '+oldValue+', new value: '+newValue+'. Do you want to revert the change?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {
    // User clicked "Yes".
    return true;
  } else {
    // User clicked "No" or X in the title bar.
    return false;
  }
}

Here is my test sheet - editable: https://docs.google.com/spreadsheets/d/1BP0Zqbf8QndhI62AucT3nGNm9eUlgzxf0lTCy2UHe-0/edit?usp=sharing

Also test script: https://script.google.com/d/1IuTpeEDlsqjDyp3ADRy2JYKzDymR7zOpPkamIcEUHnHXUNdA0SRf7xyU/edit?usp=sharing

I need to see in the alert message, the old value from the deleted cell.

Message for editing value: Logs from editing value:

Message for deleting / clearing value:

Logs for deleting / clearing value (old cell value is nowhere):

Answer 1

There is a plugin that will accomplish this for you. It takes the route of creating a hidden spreadsheet that mirrors the real one.

https://gist.github.com/tanaikech/73edaed1268a6d07118aed538aa5608d

Answer 2

    function onEdit(e){
        e.range.setNote(e.oldValue);
        e.range.setValue(e.range.getNote());
        e.range.clearNote();
    }

The above function uses the 'Insert note' in cell feature of Google sheets as a temporary holding variable. A copy of the old value of the cell is copied as a note, resets the edited value of the cell and the note is deleted. This code does not specifically check if the user has hit the 'Delete' key. It replaces any edit made to the cell with e.oldValue.

To specifically check if the user has hit the 'Delete' key and reset the cell to e.oldValue the function can be changed to:

    function onEdit(e){
        if(e.value == null) 
        e.range.setValue(e.oldValue);
    }

Note: Sheets protected via Apps Script are susceptible to edits made via Google Sheets phone app. In such situations, cell formulae may need to be used. Sheet1 may be used to receive user input. Sheet2 is a mirror of Sheet1 and is locked for editing only by the owner. I use the below formula in cell D5 of Sheet2:

    =IF(LEN(Sheet1!D5)=0,"Please enter valid input",Sheet1!D5)

The user needs to check Sheet2 for the output generated based on input given.