Google Drive – Timestamp ‘Last Modified’

google sheetsgoogle-sheets-timestamp

I'm trying to get a cell in my Google sheet to reference the last modified timestamp from the Google Drive of the same file (not folder).

In other words, I'd like for a cell to update its timestamp for an entire Google sheet, whenever a change is made. It should reference every sheet within the file itself.

Best Answer

Short answer

IMHO the easiest way is to add =NOW() as a cell formula.

Explanation

The Google Sheets spreadsheet recalculation settings default value is "On change", that means that the result of NOW will be updated everytime a change is made on your spreadsheet.

References

Related Solutions

Google Sheets – How to Auto-Update Column with Last Modify Date

Ok, here is a modified version of the code in my previous answer:

function onEdit(e) {
  // Your sheet params
  var sheetName = "MySheet";
  var dateModifiedColumnIndex = 2;
  var dateModifiedColumnLetter = 'B';

  var range = e.range; // range just edited
  var sheet = range.getSheet();
  if (sheet.getName() !== sheetName) {
    return;
  }

  // If the column isn't our modified date column
  if (range.getColumn() != dateModifiedColumnIndex) { 
    var row = range.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm:ss");
    var dateModifiedRange = sheet.getRange(dateModifiedColumnLetter + row.toString());
    dateModifiedRange.setValue(time);
  };
 };

What this does is grabs the row of any cell that is changed and assigns a timestamp to the second column of that particular row.

To implement, all you need to do is go into the spreadsheet and click on Tools > Script Editor. In the resulting editor page, just paste this in there. Since this is an onEdit() function, it should work without having to add anything else to the cells of your spreadsheet, just paste it into the editor and save it.

For the timestamp, I set the format to MM/dd/yy and left the time. If you want to change it, you can just change the use of Utilities.formatDate.

Google-sheets – How to show “Last edited” date and user on Google Sheets

As Amit said in his answer, the onEdit() trigger will work for this, and you'll need to get values from the Edit event object. A complete solution for this use case would be something like:

function onEdit(e) {
    // Prevent errors if no object is passed.
    if (!e) return;
    // Get the active sheet.
    e.source.getActiveSheet()
        // Set the cell you want to update with the date.
        .getRange('M2')
        // Update the date.
        .setValue(new Date());
    // Get the active sheet.
    e.source.getActiveSheet()
        // Set the cell you want to update with the user.
        .getRange('M3')
        // Update the user (only email is available, and only if security settings allow).
        .setValue(e.user.getEmail() );
}

Then you just need to call the function from any random cell like so: =onEdit(), and then any cell you edit will update the cells you set to in your script.

Warning: The onEdit() trigger will screw up your undo/redo history. Each time it fires when you edit a cell, the action from the trigger gets added to the undo history, which means two things: 1. To undo the last thing you did, you have to undo twice in quick succession (once for onEdit(), once for your edit). 2. Since onEdit() also fires each time you undo something, it effectively gets rid of your redo history by immediately overwriting it.