Function for last modified date of cell

Question

I have a function written in VBA that does what I want it to (see below. note: I did not write this function myself)

What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.

Ultimately I want a function written in google sheets that does the same thing, but I dont really understand how this function works.

Can anyone do one or both: 1) Explain whats happening in the function below, 2) Write a function in googlescripts (js) that does the same thing?

Public Function Lastmodified(c As Range)

Lastmodified = Now()

End Function

Answer 1

1) Explain whats happening in the function

Spreadsheet cell contents are recalculated only when the content of their references change. When you think about it for a bit, this makes sense for efficiency: it would be a waste of computer resources to calculate a new value for a cell when none of the referenced values (aka parameters) has changed... we'd just end up with the same value.

The magic for the VBA function you've provided is that the cell containing it gets recalculated only when the referenced cell is changed. That is under the control of the spreadsheet, and is completely independent of the script itself.

The same behavior is exhibited in Google Sheets. And in both cases, it's not important to the function what the monitored cell is - the function just returns the current time whenever it is called, which should be at times when the cell's value changes.

However, there are other reasons for the function to be called. In Excel, you can manually trigger spreadsheet recalculation, and that would update this function's output anywhere it appeared in the spreadsheet, with the current time. Likewise, in Google Sheets, any time you load the spreadsheet you could trigger a recalculation.

2) Write a function in googlescripts (js) that does the same thing

/**
 * Return the time that the referenced cell or range was last changed.
 * Initial use shows current time.
 *
 * @param {Sheet3!B32}  reference  Cell or range to monitor.
 * @returns                        The time the reference was last changed.
 * @customfunction
 */
function lastModified( reference ) {
  // Surprise - we don't actually care what has changed!
  return( new Date() );
}

Answer 2

In this snippet, if A1 cell is modified, the cell A2 is automatically updated with the modification date.

function onEdit(e) {

  if (e.range.getA1Notation() === "A1") {
    e.source.getRange("A2").setValue(new Date());
  }

}