How to date stamp a change in two columns in googl

Question

I have a sheet with 2 columns (A and B) that have values that are derived from another sheet and are linked. I would like to date stamp the sheet based on when the columns were last edited. In other words, when the source sheet changes a data point in A or B or both, a date stamp is placed in C4 as an example. The next time it changes, be it a day or a week, the date changes in C4 to the date it changes. Might as well be date and time since we are at it.

Answer 1

Given the separation layer between your data and the column you want timestamped, you're going to probably need something beyond a "simple" on edit trigger function. The on edit trigger functions are great when the value changes are due to user actions, but otherwise they are limited (script executions will not activate them).

One possible solution is to use a simple on edit trigger to stamp when your source sheet inputs are altered, and on your report sheet, reference the relevant maximum value of the corresponding date cells for each of the inputs to the given report.

Another possible solution is to implement a comparison method that is activated by whatever system changes data on the source sheets, and compares stored values for A & B with current values for A & B, and if one changes, then updates the timestamp and the backup of the report.

An example of this comparison & backup function:

var reportSheetName = "Report", timeStampCell = "C4", numReportHeaders = 1;
function stampAndBackup() {
  // Called by data insertion method / some "on edit" triggered function
  // that may have resulted in data updates and changes in A & B on the report sheet.
  var wb = SpreadsheetApp.getActive();
  var backup = wb.getSheetByName(reportSheetName + "_Backup");
  var report = wb.getSheetByName(reportSheetName);
  if (!report || !backup) throw new Error("Missing required sheet(s).");

  // Flush pending writes (e.g. formula recalculations).
  SpreadsheetApp.flush();

  // Load data from report & backup of report. (Assumption: both sheets have data.)
  var db = backup.getDataRange().getValues();
  // Assumption: cols A & B are the columns with most data on the report sheet.
  var data = report.getDataRange().getValues();
  if(numReportHeaders > 0) data.splice(0, numReportHeaders);

  // Assumption: db & data are valid arrays.
  if (!hasDifferentElements_(db, data))
    return;

  backup.clearContents();
  SpreadsheetApp.flush();

  backup.getRange(1, 1, data.length, data[0].length).setValues(data);
  report.getRange(timeStampCell).setValue(new Date());
}
function hasDifferentElements_(a, b) {
  if (a.length !== b.length)
    return true;

  // Element-by-element comparison is needed.
  var comparedColumns = Math.min(2, Math.min(a[0].length, b[0].length));
  for (var r = 0; r < a.length; ++r)
    for (var c = 0; c < comparedColumns; ++c)
      if (a[r][c] !== b[r][c])
        return true;

  return false;
}

I leave writing the code which calls stampAndBackup to the reader, since it is heavily implementation-dependent.