Google Apps Script send email through spreadsheet not working

Question

I have a problem with some Google Script stuff. Basically, my goal is to have the script check to see if a client's case was resolved and then send an email to them that the issue has been resolved. I've gotten the logic done on when to send an email, but every time I try and implement it into the spreadsheet, I get the error:

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

I've got a simple function to test the functionality of it, and when run in the script editor it works fine, but not on the spreadsheet. Here is my sample function:

function myFunction(row) {
var sheet = SpreadsheetApp.getActiveSheet();
var rng = sheet.getRange(row, 1, 1, 2);
var ara = rng.getValues();
var email = ara[0][0];
MailApp.sendEmail(email, "TEST", "This is a test of sendEmail().");
return "Email sent.";}

Answer 1

According to the Apps Script Custom Functions documentation:

If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

Method 1

Basically, you can replicate the wanted behavior of the two functions above with this:

function SendEmail() {
   var message = "This is your response";
   var subject = "You have feed back in the parking lot";
   var ss = SpreadsheetApp.getActiveSheet();
   var textrange = ss.getRange("F2");
   var emailAddress = ss.getRange("B2").getValue();
   if (textrange.isBlank() == false)
      MailApp.sendEmail(emailAddress, subject, message);

}

And in order to trigger the execution of this function, you can make use of Apps Script triggers and choose one which is the most convenient for your use-case.

Method 2

You can also create a custom menu and with the option of triggering the above function. You only need to add this:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("My Menu")
      .addItem("Send Email", "SendEmail")
      .addToUi();
}

And this is how it will look like on the Spreadsheet:

Reference

• Apps Script Custom Functions;

• Apps Script Range Class - isBlank();

• Apps Script Custom Menus;

• Apps Script Triggers.