CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nikhil T
Nikhil T

Reputation: 1

Trying to build a function in google sheets for sending emails

I have been trying to create a google sheet formula for send emails through MailApp.

/**
*
*
*@customfunction
*/
function SendIssueMails(x) 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("IssueCourses");
  var email = sheet.getRange(x).getValue();
  var subject = "Issue updated that needs resolution";
  var body = "Please go to your issue tracker. You have been assigned an Issue that need resolution";
  MailApp.sendEmail(email, subject, body);
  return "sent";
}

This is the code i wrote such that when i put this formula in sheets with x as email ID, its saying i dont have permission. i gave permission in appscript by sending a manual email but it still show that permission required and i dont know where to give that permission.

Upvotes: 0

Views: 139

Answers (2)

yuri
yuri

Reputation: 3400

Diego is right, custom functions on spreadsheet can not request access to personal data, so you can only use the services from this list:

Supported services

Cache
HTML
JDBC
Language
Lock
Maps
Properties
Spreadsheet: Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()). URL Fetch
Utilities
XML

On which you'll not find GmailApp

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.

As the documentation says:

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.

If you are looking on how to call your function from Sheets, you should look at Custom menus:

https://developers.google.com/apps-script/guides/menus

Upvotes: 0

Diego
Diego

Reputation: 9571

From the 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.

You can't use a service like MailApp in a custom function. You'll need to trigger sending the email in a different way. Perhaps an installable trigger configured to run when a certain cell is edited?

Upvotes: 1

Related Questions