How do you get "e.oldValue" if cell contents were pasted from another cell in Sheet

Question

Lots of people have asked how to get e.oldValue and e.value. However, how do I get the old value of a cell if the contents were overwritten via a "paste" of contents from another cell on the sheet? Here is what I know:

Let's take cell A1 with contents of "foo" and cell A2 with contents of "bar".

If A1 is overwritten by someone entering "bar" into it (including pasting from an external source): e.value = "bar" and e.oldValue = "foo".
If A1 is cleared, e.value = {"oldValue":"foo"}, e.oldValue = "foo".
If A1 is overwritten by someone copying A2 and pasting it into A1: e.value = {} and e.oldValue = {}. So we have to use e.range.getValue() to get our "new value" of "bar". However, how do I get the "oldValue"??

Yes, I know I can get it from cell A2, but how do I know what was overwritten in cell A1? That's what I want to know.

Right now my code for getting the two values I want to know is:

var newValue = (typeof e.value == "object" ? e.range.getValue() : e.value); 
var oldVlaue = e.oldValue;

Answer 1

Please think of this as one of several workarounds. I thought that it might become a workaround by saving the data before editing. This sample script retrieves oldValue by using spreadsheet saved the data before editing. In order to use this sample script, please carry out as follows.

1. Please modify "backupfile" of var backupfilename = "backupfile"; to an unique name.
2. Run a function of init(). By this, the backup file is created and the sheet that you want to retrieve oldValue is copied to it.
3. Install onEdit(e) as an installable trigger. The install method is below.
   • On script editor
   • Edit -> Current project's triggers
   • Click here to add one now.
   • Set "Run" to onEdit
   • Set "Events" to From spreadsheet, On edit

After these, when you edit the spreadsheet, onEdit(e) carries out retrieving data from both current spreadsheet and backup spreadsheet and then, the data of current spreadsheet is copied to the backup spreadsheet. So you can retrieve oldValue and newValue for the edited range. Also users that the spreadsheet is shared can retrieve oldValue and newValue through this sample script.

Sample script :

var backupfilename = "backupfile"; // In this sample, this is a global variable.

function copyToo(srcrange, dstrange) {
    var dstSS = dstrange.getSheet().getParent();
    var copiedsheet = srcrange.getSheet().copyTo(dstSS);
    copiedsheet.getRange(srcrange.getA1Notation()).copyTo(dstrange);
    dstSS.deleteSheet(copiedsheet);
}

// At first, please run this function.
function init() {
  // Source
  var srcss = SpreadsheetApp.getActiveSheet();
  var range = srcss.getDataRange().getA1Notation();
  var srcrange = srcss.getRange(range);

  // Destination
  var backupfile = DriveApp.getFilesByName(backupfilename);
  var dstid = backupfile.hasNext()
    ? backupfile.next().getId()
    : SpreadsheetApp.create(backupfilename).getId();
  var dstrange = SpreadsheetApp.openById(dstid).getSheets()[0].getRange(range);

  copyToo(srcrange, dstrange);
  PropertiesService.getScriptProperties().setProperty('backupfileid', dstid);
}

function onEdit(e) {
  var newValue = e.range.getValues();
  var dstid = PropertiesService.getScriptProperties().getProperty('backupfileid');
  var oldValue = SpreadsheetApp
    .openById(dstid)
    .getSheets()[0]
    .getRange(e.range.getA1Notation())
    .getValues();

  Logger.log("newValue %s", newValue)
  Logger.log("oldValue %s", oldValue)

  // Update backup file
  var range = e.source.getDataRange().getA1Notation();
  var srcrange = e.source.getRange(range);
  var dstrange = SpreadsheetApp.openById(dstid).getSheets()[0].getRange(range);
  copyToo(srcrange, dstrange);
}

If I misunderstand your question, I'm sorry.