CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Thomas Perrin
Thomas Perrin

Reputation: 784

How to get all old values for a mulitple cell range with onEdit trigger

In google apps script, with onEdit trigger, how do I get the old values of all the cells if the edited range has multiple cells? In the Event object documentation https://developers.google.com/apps-script/guides/triggers/events it is specified that the oldValue attribute is "Only available if the edited range is a single cell". In my case, I use onEdit from the Event object to run a function (that needs oldValue and newValue) only when a specific column is edited. It works fine when the user selects only one cell in my specific column, but if the user selects a few cells or the entire row for example, only data from the first selected cell is retrieved but I need to access the oldValue of my specific column.

Upvotes: 1

Views: 1608

Answers (1)

Tanaike
Tanaike

Reputation: 201553

  • You want to retrieve old values when the multiple cells are edited.

If my understanding is correct, how about this answer?

Issue:

Unfortunately, in the current stage, when the multiple cells are edited, there are no methods for retrieving all old values from the event object.

Workaround:

So as the current workaround, how about this flow?

  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.

By above flow, the cycle for retrieving old values when the cells are edited can be created. When this flow is reflected to the script, it becomes as follows. Please think of this as just one of several answers.

Sample script:

When you use this script, please install the OnEdit event trigger to the function of onEditByTrigger() after copy and paste this script to the script editor of the container-bound script. By this, when the cells are edited, you can see the current and old values at the log.

var backupfilename = "backupfile";

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

// This is run only one time.
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 columnNumber = 1; // If you want to retrieve the old values when the column "A" is edited, it's 1.

  var source = e.source;
  var range = e.range;
  var dstid = PropertiesService.getScriptProperties().getProperty('backupfileid');
  if (!dstid) {
    dstid = init();
  }

  if (e.range.columnStart == columnNumber) {
    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);
}

Note:

  • This is a sample script for retrieving the old values when the multiple cells were edited. So if you use this script, please modify this to your situation.

References:

If this was not the direction you want, I apologize.

Upvotes: 0

Related Questions