CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Yaniv Aflalo
Yaniv Aflalo

Reputation: 249

GoogleSheets - Is there a way to extract user info from "show edit history" to a cell?

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 "[email protected]". 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.

Upvotes: 3

Views: 3407

Answers (1)

Jacques-Guzel Heron
Jacques-Guzel Heron

Reputation: 2598

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.

Upvotes: 2

Related Questions