I am trying to add the email address of the user in a cell immediately when they edit a cell in a certain column. The code below gives TypeError: Cannot read properties of undefined (reading 'range').

function onEdit(e) {
  const range = e.range;
  const rowIndex = range.getRow();
  const colIndex = range.getColumn();
  const sheet = range.getSheet();
  const SHEET_NAME = "28th Mangsir"; // Sheet name you want to check
  const EMAIL_COLUMN = 4; // 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);
  }
}

CodePudding user response：

The onEdit(e) function is a simple trigger that is designed to run automatically when you manually edit the spreadsheet. In that context, the event object e is properly populated. Do not run the code in the script editor. If you do, the event parameter e is not populated, causing the error you mention.

Simple triggers run in a restricted context where the identity of the user at the keyboard is usually not available. "They may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions." See Session.getActiveUser() and Session.getEffectiveUser().

CodePudding user response：

Try using an installable trigger:

function onMyEdit(e) {
  const sheet = e.range.getSheet();
  if (e.range.columnStart == 2 && e.range.rowStart > 1 && sheet.getName() == "28th Mangsir") {
    if (e.user.email) {
      sheet.getRange(e.range.rowStart, 4).setValue(e.user.email);
    }
  }
}

function createInstallableTriggerIfNoneExists() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "onMyEdit").length == 0) {
    ScriptApp.newTrigger("onMyEdit");
  }
}