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"
]
}
b
beforehttps://
makes it not valid JSON. Fix that typo and try again.