电子表格电子邮件触发器 [英] Spreadsheet Email Trigger

问题描述

当电子表格被修改或添加任何数据时，我在Google Spreadsheet上有书面脚本发送电子邮件。电子邮件触发器正在工作，但每当在下一行中输入任何数据时，它也会将电子邮件发送到以前的电子邮件地址。

请建议解决方案

下面是脚本：

  onEdit（e）{
 var sheet = SpreadsheetApp。 getActiveSheet（）; 
 var startRow = 2; //处理
的第一行数据var numRows = 1; //要处理的行数
 var dataRange = sheet.getRange（startRow，1，numRows，3）//获取单元格区域A2：B3 
 
 //获取每个单元格的值在范围内排。 
 var data = dataRange.getValues（）; 
 for（i in data）{
 var row = data [i]; 
 var emailAddress = row [2]; //第一列
 var message = row [0] +requested+ row [1]; //第二列
 var subject =从电子表格发送电子邮件; 
 MailApp.sendEmail（emailAddress，subject，message）; 
} 
} 
  

解决方案

它看起来您正在使用共享电子表格收集添加用户请求，并信任请求者填写信息。在您分享的详细文档中，进一步显示请求已被添加，但未被编辑。 （这是一个重要的简化区分。）

我建议您真正需要的是使用表单来接收输入。使用表单将在您的电子表格中创建一个数据表，这是您不能混淆的一组列。 （您可以编辑内容，添加和删除行，但不能添加或删除列）。但是，您可以在此表格外添加列，这为您提供了一个方便的地方来存储有关个人状态的状态信息请求。

此外，您可以触发处理在表单提交上运行，而不是简单的onEdit - 这可以避免ScampMichael指出的问题。或者，您可以使用可安装的编辑触发器，如在此答案中所述。

请尝试此表，此表单。保存一份副本，进入脚本并删除阻止发送电子邮件的评论，然后尝试一下。电子表格中有一个菜单项可以启动处理;只需清除请求状态列即可重新运行它。你可以打开表单（并找到它的URL），并添加更多的条目进行实验。

这是我编写的一个类似系统的核心，它包含一个谨慎的状态机来处理请求。我的系统在多个电子表格中有大量非常复杂的数据，因此它经常被抢占，然后需要再次运行。 （我为此使用了一个定时触发器。）这就是为什么请求通过状态来处理的原因。如果您发现太复杂，请只提取您需要的零件。

I have Written Script on Google Spreadsheet to send Email when spreadsheet is modified or any Data is added. Email Trigger is working but whenever any data is entered in next Row it send Email to previous email address also.

Please suggest solution

The below is written script :

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1 , numRows,3) // Fetch the range of cells A2:B3

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
for (i in data) {
    var row = data[i];
    var emailAddress = row[2];  // First column
    var message = row[0] + "requested" + row [1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

解决方案

It appears that you're using a shared spreadsheet to collect the add-user-requests, and trusting the requesters to fill in the information. In the detail document you shared, it further appears that requests are ADDED, but not EDITED. (That's an important simplifying distinction.)

I suggest that what you really need is to use a form for receiving that input. Using a form will create a "data table" within your spreadsheet, a set of columns that you must not mess with. (You can edit the contents, add and delete rows, but must not add or remove columns.) However, you CAN add columns to the spreadsheet outside of this table, which gives you a handy place to store state information about the status of individual requests.

Further, you can trigger your processing to run on form submit, rather than a simple "onEdit" - this gets away from the problem that ScampMichael pointed out. Alternatively, you can use an installable edit trigger, as described in this answer.

Try this sheet, and this form. Save yourself a copy, go into the script and remove the comments that are stopping emails from being sent, and try it out. There's a menu item in the spreadsheet that can kick off processing; just clear the "Request State" column to re-run it. You can open the form (and find its URL), and add more entries to experiment.

It's the core of a similar system that I've written, and contains a discreet state machine for processing the requests. My system has large amounts of very complex data in multiple spreadsheets, so it often gets pre-empted, then needs to run again. (I use a timed trigger for that.) That's why requests are handled through states. If you find that too complex, pull out only the parts you need.

这篇关于电子表格电子邮件触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！