Get email user value when update row

user_17154675 0
Viewed 129

I've spreadsheet with my team, how can I get email user value (automatically) when they update row(based on which account they use)?

so every time they update column "task", column "email" automatically update. like the table below spreadsheet

google-sheets-formula spreadsheet google-sheets
1 Answers

If users are from your same organization (so security policies allow access to the user's identity), you can just set up an Apps Script onEdit trigger that will use getActiveUser or getEffectiveUser to retrieve the user email.

For that, open the script editor via Tools > Script editor and add an onEdit function like the one below, which would do the following:

  • Use the event object to get information about the edited cell (sheet, row, column.
  • Check whether the cell corresponds to the task column, is the right sheet and is not the header row.
  • If it corresponds to the task column, get the user email with getActiveUser and use Range.setValue to write the email address to the email column.
  • Note: In the sample below the email and task columns are set to A and B respectively, and the sheet name is set to Sheet1. Please change these according to your needs.

Code sample:

function onEdit(e) {
  const range = e.range;
  const rowIndex = range.getRow();
  const colIndex = range.getColumn();
  const sheet = range.getSheet();
  const SHEET_NAME = "Sheet1"; // Sheet name you want to check
  const EMAIL_COLUMN = 1; // Column index where emails are written
  const TASK_COLUMN = 2; // Column index where tasks are written
  if (colIndex === TASK_COLUMN && rowIndex > 1 && sheet.getName() === SHEET_NAME) {
    const userEmail = Session.getActiveUser().getEmail();
    sheet.getRange(rowIndex, EMAIL_COLUMN).setValue(userEmail);
  }
}
user_106120110
Related