google sheets - Not allowed to execute sendEmail() from custom function, but OK in script editor

Question

I have been trying to find a way to send single row of data to a specific email address in a "live/running" spreadsheet that I am using within my domain, to keep track of truck drivers and their pick up numbers. I managed to piece together this little bit of code using the Google Apps Script Editor available in sheets:

function sendEmail()
{
   var sheet = SpreadsheetApp.getActiveSheet();
   var activeRow = sheet.getActiveCell().getRow();
   var cellID = "H" + activeRow;    
   var dataRange = sheet.getRange(activeRow, 1, 1, 6);
   var EMAIL_SENT = "EMAIL_SENT";
   var data = dataRange.getValues();
   Logger.log(data[0][0]);

   var emailAddress = [email protected];
   var message = data;
   var subject = "CG-PU#";
   var emailSent = sheet.getRange(cellID).getValue();

   if (emailSent != "EMAIL_SENT")
   {
     MailApp.sendEmail(emailAddress, subject, message);
     var cell = sheet.getRange(cellID);
     cell.setValue(EMAIL_SENT);
   }
  }
SpreadsheetApp.flush();  
}

Any way I can call the function from a cell in my spreadsheet like =sendEmail() and it should send the information from only the active row, and then flag that row as EMAIL_SENT so I don't accidentally do it again.

The script runs perfect if I run it from inside script editor, and hard code the email address, but if I try and run it from the spread sheet itself I am getting the message:

error: You do not have permission to call sendEmail (line 19, file "CPS_sendEmail.gs").

Any assistance with this is greatly appreciated! It would help to keep my user from running back to MS Excel and Outlook. When I get this part of it working, I would really like it if I could have the function display a list of addresses the user can select from, or some other way of keeping the user from having to remember and type in the drivers address and carrier, but I would love to just have this much of it working as expected.

The script would/should only be allowed to run in my Google Domain with my users, and the first time I executed it - I was sure I went through the authorization process, but don't truly understand that part of things.

They currently are using MS Excel/Outlook and they copy and paste the row into an email that they can use their contacts to select from. If I cannot get the function to use Googles Contacts, I was considering creating a sheet of drivers with a look up function or something - kind of a pain to have to keep a list when contacts are right there - but ya do what ya gotta do!

Answer 1

Read over Permissions and Custom Functions. Since custom functions (scripts called from spreadsheet functions) are open to any user of a spreadsheet, they aren't allowed to use any service that requires authentication. That's why you can't send mail from one.

This is different than the process of Authorizing a script to access your services. That does make the error message confusing, but rest assured that was just about the way you were invoking the script.

No problem though, because a custom function is a bad way to perform this type of action anyway, because the function will be re-evaluated every time there is a change in the spreadsheet, sending many more emails than you want.

I recommend that you create a menu item for this operation instead. (See the sample code provided in the editor if you create a new Spreadsheet script.) The workflow would be to move the cursor to the row you want processed, then use the menu to "Make It So", which would invoke your script.