4

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?

XLFer
  • 41
  • 1
  • 2

5 Answers5

8

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);
}
Michael B
  • 81
  • 1
  • 1
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.

just.a.guy
  • 192
  • 1
  • 2
  • 12
1

I don't think that's possible.

I imagine you could get that functionality by having a exact copy of your sheet on a second sheet that updates automatically when your 'onEdit' functions ends. Until that update, data on the second sheet will have the former value. A bit tricky but why not ?-)

EDIT : seems to be the 'question of the day', see this post and Henrique Abreu's pertinent comment.

Community
  • 1
  • 1
Serge insas
  • 45,904
  • 7
  • 105
  • 131
0

When you change the value of a cell diagrammatically, you can use the setComment method to store the original value as a comment in that cell.

Amit Agarwal
  • 10,910
  • 1
  • 32
  • 43
0

What you basically need to do is to create a shadow sheet (which you can protect and hide or even have it in a totally separate spreadsheet) and use the IMPORTRANGE function to get the values of the original sheet into the shadow sheet (This gives enough delay time to get the old value that was in the cell before it got edited).

=IMPORTRANGE("enter your original sheet's ID","enter the range you wish to get from the sheet")

Please note that using this code, when editing multiple cells at the same time, the function will only work on the first cell.

function onEdit(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var originalSheet = sheet.getSheetByName('Original');
  var shadowSheet = sheet.getSheetByName('Shadow');
  var editedCell = originalSheet.getActiveCell();
  var cellColumn = editedCell.getColumn();
  var cellRow = editedCell.getRow();
  var oldValue = shadowSheet.getRange(cellRow, cellColumn).getValue();
  var cellValue = editedCell.getValue();
  var editorMail = Session.getActiveUser().getEmail();    \\getting the editor email

if ("The condition you want to meet for the onEdit function to activate"){
    editedCell.setNote(editorMail + "\n" + new Date + "\n" + oldValue + " -> " + cellValue);
  }
}
Mostafa Ahmed
  • 1
  • 2