9

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

0

2 Answers 2

4

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.

3
  • 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, 2019 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, 2019 at 17:25
  • unknown function OnEdit
    – serge
    Oct 1, 2019 at 15:19
1

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());
}

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