user2728078 user2728078 - 28 days ago 15
Javascript Question

Google-apps-script will not send email inside an onEdit function

I have a script that sends an email when the spreadsheet is edited. The script runs, but the MailApp statement does not work.

Here is the execution log:


[15-01-27 14:15:17:951 EST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[15-01-27 14:15:17:951 EST] Spreadsheet.getActiveRange() [0 seconds]
[15-01-27 14:15:17:952 EST] Range.getRow() [0 seconds]
[15-01-27 14:15:17:952 EST] Range.getLastRow() [0 seconds]
[15-01-27 14:15:17:952 EST] Range.getColumn() [0 seconds]
[15-01-27 14:15:17:952 EST] Range.getLastColumn() [0 seconds]
[15-01-27 14:15:17:955 EST] Starting execution
[15-01-27 14:15:17:969 EST] Logger.log([{"range":{"rowStart":79,"rowEnd":79,"columnEnd":6,"columnStart":6},"source":{},"value":"Xxxx","user":{}},
[]]) [0 seconds]
[15-01-27 14:15:17:969 EST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[15-01-27 14:15:18:058 EST] Spreadsheet.getSheets() [0.088 seconds]
[15-01-27 14:15:18:145 EST] Spreadsheet.getSheets() [0.086 seconds]
[15-01-27 14:15:18:145 EST] Logger.log([Customer row = 79, []]) [0 seconds]
[15-01-27 14:15:18:145 EST] Sheet.getRange([79, 6]) [0 seconds]
[15-01-27 14:15:18:233 EST] Sheet.getDataRange() [0.087 seconds]
[15-01-27 14:15:18:324 EST] Range.getValues() [0.091 seconds]
[15-01-27 14:15:18:327 EST] Execution failed: You do not have permission to call sendEmail (line 21, file "Code") [0.368 seconds
total runtime]


I have used the mailApp.sendEmail on other scripts and it works well. This is the second script attached to the spreadsheet/form, the first also sends an email from formSubmit. I have allowed all triggers and permissions.

Any help appreciated
code below:

function onEdit(e) {
Logger.log(JSON.stringify(e));
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheets()[0]
var sheet2 = ss.getSheets()[4];

var column = Number(e.range.columnStart);

if(column != 6){return};
var Customer = Number(e.range.rowStart);
Logger.log('Customer row = '+Customer);

var name = sheet1.getRange(Customer,6);
var emailArray = sheet2.getDataRange().getValues();
for (i=0;i < emailArray.length;i++){

if (name == emailArray[i][0]) {
var email_address = emailArray[i][1];
}
}

var subject = "Job assignment";
var body = "X";

MailApp.sendEmail(email_address,subject,body);
}

Answer

You are getting this error because you are calling sendMail inside the onEdit() trigger. You cannot call URLFetch or MailApp methods inside onEdit.