I've got a Google sheet that is updated quite regularly by a few people.

I'd like to be able to view the doc, and at a glance—have a cell that shows a date (last modified) and another cell (user that made the modification).

Is this at all possible and how would I do this?

Something like this:

enter image description here


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.
        // Set the cell you want to update with the date.
        // Update the date.
        .setValue(new Date());
    // Get the active sheet.
        // Set the cell you want to update with the user.
        // 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.

  • 1
    only email is available, and only if security settings allow < any pointers as to how to allow this security setting for others users in a shared google sheet? Not using google apps for business. Just the free one. – slayernoah Jan 24 at 16:51
  • @slayernoah - It seems that getEmail is the only User Class. See: developers.google.com/apps-script/reference/base/user – Zlatty May 8 at 17:25

You can set up an onEdit trigger that will log the edit date and other details in another sheet inside the same spreadsheet.

function onEdit(e){
  var range = e.range;
  range.setNote('Last modified: ' + new Date());

I'm using Google Sheets lately (I use Excel too), and I'm not a professional, currently I'm learning, and a few days ago I needed to add to cells one for Date the spreadsheet was created and another one to log the latest date/time it was modified. And I manage to stumble upon and easy formula for each cell that so far are working pretty well. This is what I did so far i.e.:

Date Created: =DATE(2019, 5, 21) Last Modified: =NOW() Modified by: (need formula for this one)

If there's a better formula or way to do this feel free to correct me, I'll gladly appreciate it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.