Is there a way to get the original value of a cell in onEdit()?

Question

I'd like to know if there's a way to get the original value of the cell from within the onEdit() event.

For example:

• Cell A1's current value is "hello"
• I edit A1 it by changing it to "world"
• Right now, when I try to get the value from the active range, I'd get "world"

But I would also like to have the possibility to get the original value, i.e. "hello". Is that currently possible?

Answer 1

You can use onEdit(e) and e.oldValue if you're dealing with a single cell. The example below adds the original value to A1 when any cell is edited. 'undefined' if the cell was blank.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var origValue = e.oldValue
 sheet.getRange('A1').setValue(origValue);
}

Answer 2

The only code that would come close to this is to use an onOpen exit to save the initial value. The initial value could be saved in several ways. There are "user properties". I don't know how they work, yet. I am new to this.

You could also use a "hidden" sheet to save the value, in the "onOpen" exit. There may be other ways, that I don't know about.

The problem becomes more difficult with an increasing number of cells for which you want to save the original value.

By-the-way, you should understand that the "onOpen" routine fires at the time the spreadsheet is opened. It so happens, that the end-user also has access and can change cell values before the onOpen handler finishes its execution. You may not capture all of your initial values.

One final thing you should know. The onEdit trigger is NOT fired when an UNDO or REDO event occurs. The cell's contents could change and you will not know it.

I don't know how a validation routine works. If the routine rejects a value, will the spreadsheet restore the original value? If it does, then this might get around the onOpen problem. If it only tells the user the value is invalid, it will not be of much help.

A really round about way that may work, but is very complicated is to save the image before the spreadsheet closes. You post all the "after" images to a second spreadsheet. Then in your onEdit handler you look at the corresponding cell in the other spreadsheet. You then decide to restore the previous image or allow the new image to proceed.

Lastly a wild idea of using a data table in place of the second spreadsheet.

I am just learning about all of these concepts, so don't ask me how to implement them. I just understand that they MIGHT be possible. For coding and support purposes they may not be the best options. But since the current script service does not provide before image access, it is about the best I could do. You have to understand that this google interface is a client-server application. Your scripts run on the server. The data changes occur in the "clients" (end-users) browser.

One final note: the onEdit trigger does not fire for an UNDO or REDO change to a cell. So the cell could change and your script is not aware of it.