Enhanced onEdit(e) using Google Apps Script

submit.md

Enhanced onEdit(e) using Google Apps Script

onEdit(e) which is used for the Edit event on Spreadsheet has the old value as e.oldValue. The specifications for this are as follows.

1. When an user edited a single "A1" cell, e of onEdit(e) shows hoge for e.oldValue and fuga for e.value.
2. When an user edited the "A1:A2" multiple cells, e.oldValue and e.value of onEdit(e) are not shown anything.
3. When an user copied and pasted from other cell, e.oldValue and e.value of onEdit(e) are not shown anything.

This sample script was created to retrieve both the edited values and the old values for the range of edited cells. This is the modified e.oldValue.

Preparation:

In order to use this sample script, please do the following flow.

1. Please modify "backupfile" of var backupfilename = "backupfile"; to an unique name.
   • In this script, the backup file is created to the root folder. You can move this to the specific folder.
2. Install onEditByTrigger(e) as an installable trigger. The install method is below.

After these, when you edit the cells of the active Spreadsheet, onEditByTrigger(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 currentValue for the edited range. Also users that the spreadsheet is shared can retrieve oldValue and currentValue through this sample script.

Flow of this script:

1. Copy the active Spreadsheet.
   • This is run only one time.
2. When the cells are edited, the old values are retrieved by comparing the active Spreadsheet and copied Spreadsheet.
3. Update the copied Spreadsheet.

Script :

var backupfilename = "backupfile";

// This function is from https://github.com/tanaikech/getSpreadsheetByRange
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);
  var srcsheetname = srcss.getName();

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

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

function onEditByTrigger(e) {
  var source = e.source;
  var range = e.range;
  var dstid = PropertiesService.getScriptProperties().getProperty('backupfileid');
  if (!dstid) {
    dstid = init();
  }
  
  var range = source.getSheetName() + "!" + range.getA1Notation();
  
  var fields = "sheets(data(rowData(values(formattedValue,userEnteredFormat,userEnteredValue))))";
  var currentValue = source.getRange(range).getValues();
  var oldValue = SpreadsheetApp.openById(dstid).getRange(range).getValues();

  Logger.log("currentValue %s", currentValue)
  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);
}

• When getValues() is modified to getFormulas(), the formula can be retrieved.

Result:

In above sample script, you can see currentValue and oldValue at the log.

References:

• Event Objects
• Installable Triggers