onEdit Event object access to previous value of a cell that has been modified?

Question

I cannot find any detailled documentation on the event.source object so I would like to know if it's possible to access the previous value of the modified range so I can compare the new values of the range with the old values of the range for validation.

Thanks for your help.

OnEdit(event)

function onEdit(event) 
/* Default onEdit Event function */
{
  var ssa = SpreadsheetApp.getActiveSpreadsheet();
  var ss = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
...

Answer 1

--edit it seems this is now possible, please take look at the links and comments bellow.

Detailed information is on documentation here.

But the previous value is not available on the event. There's an enhancement request opened regarding this. You should "star" it to keep track of updates and kind of vote for it.

Now, to "workarounds". All of them are based on the fact that you will have to save the original data yourself somewhere else. You can have a mirror spreadsheet or sheet and when any onEdit happens on the original you can go to the mirror and get the old value. This is a little more complicated than it seems, because you'd also have to update the mirror spreadsheet via the script, but not all events on spreadsheet triggers onEdit events, e.g. inserting rows. So your script has to be extra smart to keep up with those, which, depending on your usage, may not even be possible.

Answer 2

This is an old post but it's the first that came up when I was searching and this has since been implemented. You can find the details in the documentation https://developers.google.com/apps-script/guides/triggers/events#edit

[From Docs] It can be accessed with oldValue: Cell value prior to the edit, if any. Only available if the edited range is a single cell. Will be undefined if the cell had no previous content.

So for example you could log the value with the following script:

function onEdit(e){
  Logger.log(e.oldValue);
}

PS: You can see the logs by going to Executions from the left menu (see image below)

Answer 3

You could make use of ScriptDB to store the information onLoad() and then read it back onEdit(). Each time there is an edit you could then call onLoad() again to refresh the database of the cell values again or just replace the relevant information in the database.

Add notonEdit() to trigger on a FromSpreadsheet>onEdit() event in the Resources>All Your Triggers menu.

Here is the code:

function onLoad() {

  var db = ScriptDb.getMyDb()

  //get array of the sheet range that has something in it
  var sheet = SpreadsheetApp.getActiveSheet()
  var lastrow = sheet.getLastRow()
  var lastcolumn = sheet.getLastColumn()
  var subsheet = sheet.getRange(1, 1, lastrow, lastcolumn) 
  var values = subsheet.getValues() 

  //write that array into the ScriptDB of the project 
      for (i=1; i<=lastrow; i++){
        for (j=1; j<=lastcolumn; j++){
          var object = {type: "onEditfudge", row: i, column:j, value:values[i-1][j-1]}  
          db.save(object)
          Logger.log(object) //log it to check its correct..
        }
      }    
}

function BeforeonEdit(){

  db = ScriptDb.getMyDb()
  var newrange = SpreadsheetApp.getActiveRange()


  //get 'old value'
  var dbentry = db.query({type: "onEditfudge", row:newrange.getRow(),column:newrange.getColumn()}).next()
  var oldvalue = dbentry.value    

  //overwrite the 'old value' with the 'new value' for the next onEdit() event
 dbentry.value = newrange.getValue()
 db.save(dbentry)

 //return the old value to do something with in the calling function  
 return oldvalue  
}

function notonEdit(){

  //show new and old value    
  Browser.msgBox("Old value is: " + BeforeonEdit() + ". New Value is: " + SpreadsheetApp.getActiveRange().getValue()) 

}