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

问题描述

我一直在试图找到一种方法，将单行数据发送到我在我的域中使用的live / running电子表格中的特定电子邮件地址，以跟踪卡车司机和他们的接机号码。我设法使用表中提供的Google Apps脚本编辑器将这些代码拼凑在一起：

  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 ，所以我不用如果我从脚本编辑器里面运行脚本，并且硬编码电子邮件地址，脚本运行的很好，但是如果我尝试运行它电子表格本身我得到的消息 - >错误：您没有权限调用sendEmail（第19行，文件CPS_sendEmail.gs）。

任何帮助这是非常感谢！这将有助于防止我的用户运行回MS Excel和Outlook。
当我得到这部分工作时，我真的很喜欢它，如果我可以让该函数显示一个用户可以选择的地址列表，或者让用户不必记住和键入的其他方式司机的地址和承运人，但我希望能够按预期工作。

该脚本只能在Google Domain中运行与我的用户，我第一次执行它 - 我确定我经历了授权过程，但不真正理解这一部分内容。

他们目前正在使用MS Excel / Outlook，他们将该行复制并粘贴到电子邮件中，以便他们可以使用他们的联系人进行选择。如果我不能使用谷歌联系人的功能，我正在考虑创建一个带有查找功能的驱动程序表 - 当联系人在那里时必须保留一个列表是一种痛苦 - 但是你要做什么你必须做的！ 阅读权限和自定义功能。由于自定义函数（从电子表格函数调用的脚本）对电子表格的任何用户开放，因此不允许使用任何需要验证的服务。这就是为什么你无法发送邮件。

这与授权脚本访问服务的过程不同。这确实会让错误信息混淆，但请放心，这正是您调用脚本的方式。

虽然没有问题，因为自定义函数是一个不好的方法无论如何都要执行这种类型的操作，因为每次电子表格发生更改时都会重新评估该功能，并发送比您想要的更多的电子邮件。

我建议您为此操作创建一个菜单项目。 （如果你创建了一个新的电子表格脚本，请参阅编辑器中提供的示例代码。）工作流程将把光标移动到你想要处理的行上，然后使用菜单Make It So来调用你的脚本。

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();  
}

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!

解决方案

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屋！