Why does the trigger execution log for a sendEmail function of my google script project says it's completed, and yet no actual emails are being sent?

Question

I am trying to create a function in google script that attempts to automatically send emails to a user once they entered an email into the sheet. I have set up a trigger for this and according to the execution log, the attempts that I made were all successful, however, no emails are actually being sent to the test emails that i entered. Below is my code:

function sendEmail(e) {
var esheete = SpreadsheetApp.getActiveSpreadsheet();
  if (!e || !e.range) {
    console.error("Event object or range is missing.");
    return;
  }

  const dissheete = e.range.getSheet();


  if (e.range.columnStart == 6 && e.range.rowStart > 1) {
    
    const rowr = e.range.getRow();
    const emailAddress = dissheete.getRange(rowr, 7).getValue();
    const nonEmptyValues = getLastNonEmptyCellValues(dissheete, [2, 3, 4]);
        const teacherName = nonEmptyValues[0];
        const meetingDetails = nonEmptyValues[1];
        const meetTime = nonEmptyValues[2];
    const queueSlots = sheete.getRange(rowr, 5).getValue();
    const queueEntryName = e.value;
    const subject = "Academic Advising Schedule: Slot Confirmation";
    const message = "Greetings, " + queueEntryName + "!\n\n" +
                    "You have been added to the meeting queue. Please take note of the meeting's details. \n\n" +
                    "Meeting with: Mr/Ma'am " + teacherName + "\n" +
                    "Meeting Details: " + meetingDetails + "\n" +
                    "Meeting Time: " + meetTime + "\n" +
                    "Queue Slot: " + queueSlots + "\n\n" +
                    "Please keep a copy of this message, and take note of the time when you received this. In case someone replaces your name in the Queueing Sheet, please present this confirmation email to your adviser." +
                    "Please make sure to attend the said schedule. Thank you!";
    
    GmailApp.sendEmail(emailAddress, subject, message);
  }
}

function getLastNonEmptyCellValues(dissheete, columns) {
  var lastRow = dissheete.getLastRow();
  var nonEmptyValues = [];

  // Iterate over each column in the array
  columns.forEach(function(column) {
    for (var i = lastRow; i > 0; i--) {
      var cellValue = dissheete.getRange(i, column).getValue();
      if (cellValue !== "") {
        nonEmptyValues.push(cellValue); // Store non-empty value
        break; // Move to the next column
      }
    }
  });

  return nonEmptyValues;
}

I already tried deleting and restarting the trigger, but nothing happened.

Answer 1

Although I'm not sure about your actual Spreadsheet, if your showing script is your whole script, I think that sheete of const queueSlots = sheete.getRange(rowr, 5).getValue(); is not declared. I guessed that this might be the reason for your current issue.

In your situation, sheete is dissheete? I think that esheete is the same with dissheete. If my understanding is correct, please modify it as follows and test it again.

From:

const queueSlots = sheete.getRange(rowr, 5).getValue();

To:

const queueSlots = dissheete.getRange(rowr, 5).getValue();

Added:

From the following reply,

I tried to do what you just have said, I deleted the other functions and kept only the sendEmail function, the trigger was completed but no email has been sent still.

When I saw your sample Spreadsheet, in your specification, it seems that it is required to set the email address to column "G". From your reply, I'm worried that this might not be done. And also, I'm worried about other errors. So, I proposed the following modified script.

Please test the following script. In this case, after sendEmail was installed as a OnEdit trigger, when you edit the column "F" and an error occurs, the error message is shown in a dialog. You can see the error message at the dialog. If an error like Failed to send email: no recipient at sendEmail occurs, please set the email address to column "G" and edit column "F" again. When an email is sent, a toast is shown.

In this case, getLastNonEmptyCellValues is not modified.

function sendEmail(e) {
  try {

    // var esheete = SpreadsheetApp.getActiveSpreadsheet(); // This is not used.
    if (!e || !e.range) {
      console.error("Event object or range is missing.");
      return;
    }
    const dissheete = e.range.getSheet();
    if (e.range.columnStart == 6 && e.range.rowStart > 1) {
      const rowr = e.range.getRow();
      const emailAddress = dissheete.getRange(rowr, 7).getValue();
      const nonEmptyValues = getLastNonEmptyCellValues(dissheete, [2, 3, 4]);
      const teacherName = nonEmptyValues[0];
      const meetingDetails = nonEmptyValues[1];
      const meetTime = nonEmptyValues[2];
      const queueSlots = dissheete.getRange(rowr, 5).getValue(); // Modified
      const queueEntryName = e.value;
      const subject = "Academic Advising Schedule: Slot Confirmation";
      const message = "Greetings, " + queueEntryName + "!\n\n" +
        "You have been added to the meeting queue. Please take note of the meeting's details. \n\n" +
        "Meeting with: Mr/Ma'am " + teacherName + "\n" +
        "Meeting Details: " + meetingDetails + "\n" +
        "Meeting Time: " + meetTime + "\n" +
        "Queue Slot: " + queueSlots + "\n\n" +
        "Please keep a copy of this message, and take note of the time when you received this. In case someone replaces your name in the Queueing Sheet, please present this confirmation email to your adviser." +
        "Please make sure to attend the said schedule. Thank you!";
      GmailApp.sendEmail(emailAddress, subject, message);
      e.source.toast("Done");
    }
  } catch ({ stack }) {
    Browser.msgBox(stack);
  }
}

function getLastNonEmptyCellValues(dissheete, columns) {
  var lastRow = dissheete.getLastRow();
  var nonEmptyValues = [];

  // Iterate over each column in the array
  columns.forEach(function(column) {
    for (var i = lastRow; i > 0; i--) {
      var cellValue = dissheete.getRange(i, column).getValue();
      if (cellValue !== "") {
        nonEmptyValues.push(cellValue); // Store non-empty value
        break; // Move to the next column
      }
    }
  });

  return nonEmptyValues;
}