Return to 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 already added oauthoscope ["https://www.googleapis.com/auth/spreadsheets",b"https://www.googleapis.com/auth/script.send_mail"] to the JSON file but the problem to send email persists.

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 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"
  ]
}

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 chat gpt.

I have authorized the gmail for send email. and it works on another test email function.function MailApp.sendEmail( )this works and send email to 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: kadam.nepal@jdnenergy.com
Jun 28, 2024, 9:36:18 AM
Info

I have already added oauthoscope ["https://www.googleapis.com/auth/spreadsheets",b"https://www.googleapis.com/auth/script.send_mail"]

to json file but the problem to send email persists.

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: kadam.nepal@example.com
Jun 28, 2024, 9:36:18 AM
Info

I have already added oauthoscope ["https://www.googleapis.com/auth/spreadsheets",b"https://www.googleapis.com/auth/script.send_mail"] to the JSON file but the problem to send email persists.

I have authorized the gmail for send email. and it works on another test email function.function MailApp.sendEmail( )this works and send email to 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 oauthoscope ["https://www.googleapis.com/auth/spreadsheets",b"https://www.googleapis.com/auth/script.send_mail"]to json file but the problem to send email persists.

I have authorized the gmail for send email. and it works on another test email function.function MailApp.sendEmail( )this works and send email to 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 oauthoscope ["https://www.googleapis.com/auth/spreadsheets",b"https://www.googleapis.com/auth/script.send_mail"]

to json file but the problem to send email persists.