How do I create a function that returns the last time a cell was modified?

0
votes

I'm looking to write a function for Google Sheets Script that accepts a cell reference and outputs the date that cell was last modified. I'm not sure how to get started.

1
google-apps-scriptgoogle-sheets-custom-function
related stackoverflow.com/questions/57485065/… – Kos
cross-site related webapps.stackexchange.com/questions/87620/… – Kos

1 Answers

1
votes

There's no direct approach to get the exact date of a cell's edit.

Method 1

Retrieve the modifiedTime using the Drive API's Files:get which will return, according to the documentation:

The last time the file was modified by anyone (RFC 3339 date-time).

Method 2

What you can do instead is to make use of the onEdit(e) trigger and monitor each change from the particular cell.

So for example, considering that the cell you want to check is A1, you can use this snippet:

Snippet

function onEdit(e) {
  if (e.range.getRow() == 1 && e.range.getColumn() == 1) {
    var date = new Date();
    console.log(date);
  }
}

Explanation

The above function is an onEdit trigger which will fire every time there's an edit made on the sheet. Since you want to check this for a particular cell, the event object e has been used in order to get the row and the column of the cell which has been edited. Therefore, the if condition in this situation checks if the row is equal to 1 and the column is equal to 1 (range corresponding to the A1 cell) and based on that, it returns the current date which corresponds in this situation to the edit that has been made.

To track all the edits for a cell, you can store the date variables resulted from the edit to an array and eventually use it afterwards to your liking.

Note

Taking these into account, you won't be able to get the time of a cell which has been edited before having the trigger into place.

Reference