Send mail function is not working, it gives no authorization to send mail. But the authorization is there and it sends email on test email function

Question

function onEdit(e) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    
    if (sheet.getName() === "To Do List" && e.range.getColumn() == 9) {
      var row = e.range.getRow();
      if (row >= 3) {
        var isChecked = e.range.getValue();
        if (isChecked) {
          var ui = SpreadsheetApp.getUi();
          var today = new Date();
          var formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), 'MM/dd/yyyy');
          
          var values = sheet.getRange(row, 1, 1, 8).getValues()[0];
          var assignedTo = values[3];
          var completionDate = formattedDate;
          var status = "Completed";

          var response = ui.alert('Completion Confirmation', 'Date completed: ' + formattedDate +  `your text`'\nClick OK to confirm.', ui.ButtonSet.OK_CANCEL);
          
          if (response == ui.Button.OK) {
            var completedSheet = ss.getSheetByName("Completed Work List");
            values.splice(2, 0, formattedDate);
            completedSheet.appendRow(values);
            Logger.log('About to send email');
            sendCompletionEmail(assignedTo, values, completionDate, status);
          } else {
            e.range.setValue(false);
          }
        }
      }
    }
  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }
}

function sendCompletionEmail(assignedTo, values, completionDate, status) {
    var emails = {
        xxxxxxx
    };
  
    var assignedEmail = emails[assignedTo];
    var subject = values[4] + ': ' + values[2];
    var message = 'The following task has been completed:\n\n' +
    'Date: ' + values[0] + '\n' +
    'Company: ' + values[1] + '\n' +
    'Work: ' + values[2] + '\n' +
    'Assigned to: ' + values[3] + '\n' +
    'Priority: ' + values[4] + '\n' +
    'Date to be done in: ' + values[5] + '\n' +
    'Work Description: ' + values[6] + '\n' +
    'Completion Date: ' + completionDate + '\n' +
    'Status: ' + status;
  
     Logger.log('Sending email to: ' + assignedEmail);
     MailApp.sendEmail({
         to: assignedEmail,
         cc: xxxxxx,
         subject: subject,
         body: message
     });
}

I have removed the email list because this is an open source. This is my code where if a checkbox is clicked on to do list, there is a box to ask if the task is completed. If user says okay it should send email. I am new to programming and am developing this with the help of ChatGPT.

I have authorized the gmail for send email and it works on another test email function.function MailApp.sendEmail( ) this works and sends email but on execution of onedit function it gives:

Jun 28, 2024, 9:36:18 AM
Info
About to send email
Jun 28, 2024, 9:36:18 AM
Info
Sending email to: [email protected]
Jun 28, 2024, 9:36:18 AM
Info

Error: Exception: You do not have permission to call MailApp.sendEmail. Required permissions: https://www.googleapis.com/auth/script.send_mail

I have already added "https://www.googleapis.com/auth/script.send_mail" to oauthScopes in the JSON file but the problem to send email persists.

{
  "timeZone": "Asia/Kathmandu",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://googleapis.com/auth/spreadsheets",
    "https://googleapis.com/auth/script.send_mail"
  ]
}

Answer 1

this script will need to use an installable onEdit trigger. A simple onEdit trigger will not be able to send the email (as this requires authorization).

The users that will run the code will need to authorize the script in order to give the permission for the app script and then they will be able to send the emails correctly.

(A SIMPLE trigger "OnEdit") cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization that why you can run it perfect whit another test email but not when used from the On Edit function on your app script code.

You will need to set up an installable trigger in the app script project to be able to send the request.

To manually create an installable trigger through a dialog in the script editor, follow these steps:

1. From the script editor, choose Edit > Current project's triggers.
2. Click the link that says: No triggers set up. Click here to add one now.
3. Under Run, select the name of function you want to trigger.
4. Under Events, select either Time-driven or the Google App that the script is bound to (for example, From spreadsheet).
5. Select and configure the type of trigger you want to create: onEdit
6. Optionally, click Notifications to configure how and when you are contacted by email if your triggered function fails.
7. Click Save.

You can check more information about the installable triggers here: https://developers.google.com/apps-script/guides/triggers/installable.

Here is the documentation for simple trigger restrictions: Google Documentation - Triggers - Restrictions.

Hope that works. If it doesn't, please share a copy of your spreadsheet so we have some data examples to work with.