3

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);
}
Alan Wells
  • 30,746
  • 15
  • 104
  • 152
user2728078
  • 53
  • 1
  • 5
  • The script does run, but not completely. It fails on line 21. The error states that it's a permissions problem calling a `sendEmail` function. Is the `sendEmail` function in the same project? We need to see some code. – Alan Wells Jan 27 '15 at 05:00
  • Code added above, not sure why some is in code format and the rest is not, sorry – user2728078 Jan 27 '15 at 05:32
  • 2
    as a complement to below answers, read the doc about simple trigger restrictions here : https://developers.google.com/apps-script/guides/triggers/ – Serge insas Jan 27 '15 at 13:31
  • Thanks everyone, I now have another fault, where I am getting and undefined error in the email. It seems the var name = sheet1.getRange(Customer,6); isn't picking up the value to feed the array? – user2728078 Jan 27 '15 at 21:10
  • Ok, thanks to all who have helped. I have solved the second problem using `sheet1.getSheetValues(Customerrow,2,1,1);` to pick the correct values and feed the array and email – user2728078 Jan 27 '15 at 23:07

2 Answers2

13

Like the other answer says you cant call sendMail from the normal onEdit.

To fix: rename onEdit to something else like "onEditTrigger" and manually install the trigger from the "Resources" menu.

That link also explains the difference, including the restriction you just found out, between the different types of triggers. In this case, onEdit is a simple trigger while my onEditTrigger is an installable trigger

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36
4

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

Amit Agarwal
  • 10,910
  • 1
  • 32
  • 43