如何循环使用onEdit函数从Google表格中的多行发送电子邮件? [英] How to loop an onEdit function to send emails from multiple rows in Google Sheets?

问题描述

我对此非常陌生，我的工作是在Google工作表中包含FB的所有潜在客户信息，并且我正在尝试提供一个功能(onEdit)，该功能将根据以下信息向潜在客户发送电子邮件一个单元格的数据.例如，当A2包含"Follow Up"时，它将向D2中的地址发送一封电子邮件，并且正文文本来自J2("Follow Up"数据验证的罐头响应).现在，它适用于第2行，但是在其他任何行中输入"Follow Up"时，它都会向列表中的每一行发送一封电子邮件.例如，在A29中触发事件时，它会将J列中的正文发送到列表中的每封电子邮件.我正在尝试使事件一次仅发送一封电子邮件(基于符合验证要求的活动单元).

I'm very new to this and my job has a Google sheet that has all of our lead information from FB, and I'm trying to have a function (onEdit) that will send out an email to the lead based on one cell's data. For example, when A2 contains "Follow Up", then it sends out an email to the address in D2, and the body text comes from J2 (the canned response for the "Follow Up" data validation). Right now this works for row 2, however when inputting "Follow Up" in any other row it sends every row in the list an email. For example, when triggering the event in A29 it sends the body text in column J to every email in the list. I'm trying to make the event only send one email at a time (based on the active cell meeting validation requirements).

我已经尽力查看此处的其他文章，以使用for循环将这些代码拼凑在一起，以检查是否有新行(新行始终添加到工作表中).这三个if语句可能很草率，但是我不知道如何将其清理为一个奇异的if语句.我正在考虑添加一列，指出是否已经发送了电子邮件，并更新了for循环或if语句以不向这些行发送电子邮件.

I've done my best looking at other posts on here to piece this code together using a for loop to also check for new rows (new rows are consistently being added to the sheet). The three if statements are probably sloppy, but I don't know how to clean that up yet into one singular if statement. I was thinking of adding a column that states whether to the email has been sent already, and updating the for loop or if statement to not send an email to those rows.

  var sheetname = "Prometheus";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
  var ac = sheet.getActiveCell();
  var endRow = sheet.getLastRow();
  var startRow = 1;

for (var i = endRow; i >= startRow; i--) {

  var rangeToCheck = sheet.getRange(i, 1, 1, 21);

  if (ac.getValue() == "Appt. Set (appointment is setup)") {
    data = rangeToCheck.getValues();
    Logger.log(data)
    MailApp.sendEmail({
      to: data[0][4],
      subject: "Appt. Confirmation",
      body: data[0][10]
    }); break;
  } else if (ac.getValue() == "Lead") {
    data = rangeToCheck.getValues();
    Logger.log(data)
    MailApp.sendEmail({
      to: data[0][4],
      subject: "Welcome",
      body: data[0][11]
    }); break;
  } else if (ac.getValue() == "Follow Up") {
    data = rangeToCheck.getValues();
    Logger.log(data)
    MailApp.sendEmail({
      to: data[0][4],
      subject: "Follow Up",
      body: data[0][12]
    });
  }
 }
}

我希望当我的活动单元格满足if语句中的条件时，只会在同一行中将一封电子邮件发送给联系信息.现在，当一行中满足条件时，它将向工作表中的每个人发送电子邮件.

I expect when my active cell meets the condition in the if statement, for only one email to be sent to the contact info within the same row. Right now it's sending emails to everyone in the sheet when the condition is met in one row.

推荐答案

• 当活动单元格所在行的"B"列的值为"Appt.Set(设置约会)"，"Lead"和"Follow Up"时，您要使用以下值发送电子邮件行.
  • 您要通过"B"列的值更改邮件正文.
  • When the values of column "B" of at the row with the active cell are "Appt. Set (appointment is setup)", "Lead" and "Follow Up", you want to send an email using the values of the row.
    • You want to change the mail body by the value of column "B".

    如果我的理解是正确的，那么该修改如何?在此修改中，使用了可以通过OnEdit事件触发器检索的事件对象.请认为这只是几个答案之一.

    If my understanding is correct, how about this modification? In this modification, the event object which can be retrieved by the OnEdit event trigger was used. Please think of this as just one of several answers.

    在使用此脚本之前，请为修改后的脚本的RunByOnEdit()安装OnEdit事件触发器.

    Before you use this script, please install the OnEdit event trigger for RunByOnEdit() of the modified script.

    • 打开脚本编辑器.
      • 编辑->当前项目的触发器.
      • 点击添加触发器".
      • 将"RunByOnEdit"设置为选择要运行的功能".
      • 将来自电子表格"设置为选择事件源".
      • 将选择事件类型"设置为正在编辑".
      • Open the script editor.
        • Edit -> Current project's triggers.
        • Click "Add Trigger".
        • Set RunByOnEdit for "Choose which function to run".
        • Set "From spreadsheet" for "Select event source".
        • Set "On edit" for "Select event type".

        function RunByOnEdit(e) {
          var sheetname = "Prometheus";
          var sheet = e.range.getSheet();
          var range = e.range;
          if (sheet.getSheetName() == sheetname && range.columnStart == 2) {
            var data = sheet.getRange(range.getRow(), 1, 1, 21).getValues()[0];
            var object = {
              to: data[4] // Column "E"
            };
            if (e.value == "Appt. Set (appointment is setup)") {
              object.subject = "Appt. Confirmation";
              object.body = data[10]; // Column "K"
            } else if (e.value == "Lead") {
              object.subject = "Welcome";
              object.body = data[11]; // Column "L"
            } else if (e.value == "Follow Up") {
              object.subject = "Follow Up";
              object.body = data[12];  // Column "M"
            }
            if (object.subject) MailApp.sendEmail(object);
          }
        }
        

        参考文献:

        • 可安装触发器
        • 事件对象

        References:

        • Installable Triggers
        • Event Objects

        如果我误解了你的问题，而这不是你想要的结果，我深表歉意.那时，为了正确了解您的情况，您可以提供样本电子表格吗?当然，请删除您的个人信息.

        If I misunderstood your question and this was not the result you want, I apologize. At that time, in order to correctly understand your situation, can you provide a sample Spreadsheet? Of course, please remove your personal information.

        这篇关于如何循环使用onEdit函数从Google表格中的多行发送电子邮件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！