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

Question

Many people asked how to get e.oldValue and e.value. However, how do I get the old cell value if the content was overwritten by “pasting” the content from another cell on the sheet? Here is what I know:

Take cell A1 with the contents of "foo" and cell A2 with the contents of "bar".

If A1 is overwritten by someone who enters "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 = {}. Therefore, we must use e.range.getValue () to get our "new value" "bar". However, how do I get "oldValue" ??

Yes, I know that I can get it from cell A2, but how do I know what has been rewritten in cell A1? This is what I want to know.

Currently, 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;

Thank you for your time!

Answer 1

. , , . script oldValue, , . script, .

• "backupfile" var backupfilename = "backupfile"; .
• init(). , , oldValue, .
• onEdit(e) . .
  • script
  • →
  • , .
  • "" onEdit
  • "" " ", ". "

onEdit(e) , . , oldValue newValue . , , , oldValue newValue script.

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);
}

, .