Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
851 views
in Technique[技术] by (71.8m points)

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

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...