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
CodePudding user response:
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
andB
respectively, and the sheet name is set toSheet1
. 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);
}
}