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: .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.";}
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./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.";}
-
1
"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
consoleorLogger, and pare that to you what you expected) – tehhowch Commented Oct 4, 2018 at 21:56 - 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 Commented Oct 4, 2018 at 22:42
- Look into timed triggers or adding a menu. Custom functions don't have authority to send mail. – TheMaster Commented Oct 4, 2018 at 22:51
- 2 Possible duplicate of Google Script - DriveApp.getFilesByName - Error (No Permission) – TheMaster Commented Oct 5, 2018 at 0:03
2 Answers
Reset to default 3According 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.
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./auth/script.send_mail" to the oauthScopes, like this:
{
"oauthScopes": ["https://www.googleapis./auth/spreadsheets", "https://www.googleapis./auth/script.send_mail"],
}
PS: I assigned the script to an image, which basically acts like a button.