Google Apps Script send email through spreadsheet not working

1
votes

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.";}
2
javascriptgoogle-apps-scriptgoogle-sheets
"implement it into the spreadsheet" means what, using it as a function from a spreadsheet cell? Read about "Apps Script Custom Functions" to learn why. The error is correct. Consider also what it means if that function actually could be called as a custom function --- how many emails would get sent? when would they get sent? (PS: look at what the function's arguments actually are, inside the function, by using console or Logger, and compare that to you what you expected) – tehhowch
Yeah, so how I will call it would basically on a cell do: =myFunction(ROW()). And then in my full code it would have certain checks so that it wouldn't send an email every time you refresh the page. – Colton
Look into timed triggers or adding a menu. Custom functions don't have authority to send mail. – TheMaster
Possible duplicate of Google Script - DriveApp.getFilesByName - Error (No Permission) – TheMaster

2 Answers

1
votes

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:

custom menu

Reference

1
votes

I encountered the same problem today "You do not have permission to call MailApp.sendEmail".

I solved this by doing the next steps:

  • open "Tools" -> "Script editor"
  • in "Script editor" click on "View" -> "Show manifest file"
  • open the "appscript.json" file that appeared in the left section of your screen and add "https://www.googleapis.com/auth/script.send_mail" to the oauthScopes, like this:
{
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/script.send_mail"],
}

PS: I assigned the script to an image, which basically acts like a button.