不允许从自定义函数执行 sendEmail()，但在脚本编辑器中可以 [英] Not allowed to execute sendEmail() from custom function, but OK in script editor

问题描述

我一直在尝试找到一种方法，将单行数据发送到我在域内使用的实时/运行"电子表格中的特定电子邮件地址，以跟踪卡车司机及其取货号码.我设法使用表格中提供的 Google Apps 脚本编辑器拼凑了这一点代码:

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 = [e-mail];
   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();  
}

我可以通过任何方式从电子表格中的单元格调用该函数，例如 =sendEmail() 并且它应该只发送来自活动行的信息，然后将该行标记为 EMAIL_SENT 所以我不会不小心再做一次.

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:

错误:您无权调用 sendEmail(第 19 行，文件CPS_sendEmail.gs").

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

非常感谢您对此的任何帮助！这将有助于防止我的用户跑回 MS Excel 和 Outlook.当我让它的这部分工作时，我真的很喜欢它，如果我可以让该功能显示用户可以从中选择的地址列表，或者以其他方式让用户不必记住和输入驱动程序地址和承运人，但我很想让它按预期工作.

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.

该脚本将/应该只允许在我的 Google 域中与我的用户一起运行，并且在我第一次执行它时 - 我确定我已经完成了授权过程，但并不真正理解那部分内容.

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.

他们目前正在使用 MS Excel/Outlook，他们将该行复制并粘贴到电子邮件中，他们可以使用他们的联系人从中进行选择.如果我无法获得使用 Google 通讯录的功能，我正在考虑创建一张带有查找功能或其他功能的驱动程序表 - 当联系人就在那里时必须保留列表有点痛苦 - 但你做你必须做的！

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!

推荐答案

阅读权限和自定义函数.由于自定义函数(从电子表格函数调用的脚本)对电子表格的任何用户开放，因此不允许他们使用任何需要身份验证的服务.这就是为什么你不能从一个发送邮件的原因.

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.

这篇关于不允许从自定义函数执行 sendEmail()，但在脚本编辑器中可以的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！