3

I am trying to document in a certain column, for each row, the user info as it is shown when using the "show edit history" option (by right-clicking a cell).

More specifically- for a given row, if the cell in column G was last modified by me- the cell in column B should say "Yaniv A" or "yaniva@emaildomain.com". Of course this should apply the same way for any other editor. New information should replace any old in column B one anytime the coresponding cell in column G is modified.

Yaniv Aflalo
  • 239
  • 2
  • 11
  • 1
    I'm not sure about this but try reading the second comment under this answer: https://stackoverflow.com/a/10538280/7215091 – Cooper Oct 13 '19 at 15:41

1 Answers1

1

On Apps Script you can set up an onEdit trigger that retrieves the username of the editor and cell where the change was made; but you will have to keep in mind that there may be cases where the username is not retrievable (mainly if the user haven't accepted the script scopes), you can read more about those situations on getActiveUser docs.

You can achieve what you are requesting with the following function. It will use the event object to check if the change was made on the G column, and if it was, the email of the user (or Unknown if the scopes weren't accepted) will be written on the B column of the same row.

function onEdit(e) {
  var range = e.range;
  var editor = e.user.getEmail();
  if (editor == '' || editor == null) {
    editor = 'Unknown';
  }
  if (range.getA1Notation().search('G') != -1) {
    range.offset(0, -5).setValue(editor);
  };
}

After saving this function you will have to create a installable trigger. To do so, go to Edit Current project's triggers. After that, click on + Add Trigger and fill this settings:

  • Choose which function to run: onEdit
  • Choose which deployment should run: Head
  • Select event source: From spreadsheet
  • Select event type: On edit
  • Failure notification settings: as you wish

After saving the trigger you could test the function by yourself modifying a cell in the G column. Please, do not hesitate to ask for any clarification about my answer or the code itself.

Jacques-Guzel Heron
  • 2,480
  • 1
  • 7
  • 16
  • Thank you Jacques-Guzel, the code and suggested solution is clear. I do have a few questions: 1. The ss in question is a template, can the code be changed in such way the copies will have the same functionality, even after changing their names? 2. I hoped using the “show edit history” can somehow be a workaround to the getActiveUser restrictions.. Is there a way to ask for the editors approval to document their username/email? – Yaniv Aflalo Oct 14 '19 at 15:33
  • 1
    Hi there @YanivAflalo, I just updated this code so it can now work on any sheet of any spreadsheet. There is no workaround for grabbing the usernames without consent. As you can read in the links on the answer, Google intended this to work that way. Only if the users can run the script, they can show their usernames. – Jacques-Guzel Heron Oct 15 '19 at 09:44
  • Thank you! It is just frustrating that seeing who is doing changes live is possible, seeing who did changes in a comment view (“show edit history) is possible, seeing detailed revisions is possible, but logging it is not ‍♂️. – Yaniv Aflalo Oct 15 '19 at 19:31
  • You are welcome, @YanivAflalo! You can go to Issue Tracker [ https://developers.google.com/issue-tracker ] to file a request about this topic, and Google will take a look at it. – Jacques-Guzel Heron Oct 16 '19 at 07:47
  • Fantastic little piece of code, thanks for sharing! For my use case I modified the code a bit with a gate to check for existing contributors there and concatenate any extra that come on. Thus, building an array I notice that "Unknown" is output the first time I edit the cell, every time. Is there a reason for that? – Doug Sep 02 '21 at 21:09