google-apps-script - 符合条件时发送电子邮件的 GOOGLE 脚本

Question

嗨，我在谷歌脚本中做一些代码，但我期望的输出没有发生。

这是我想出的代码希望你能帮助我解决这个问题，

function sendEmail() {

var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var lr = activeSheet.getLastRow();

var dRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("March Cycle").getRange("I6")

var rData = dRange.getValue();

var templateTxt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1,1).getValue();


for (var i = 6;i<=lr;i++) {

if(rData = "Touch Course Completed") {

var frstname = activeSheet.getRange(i,4).getValue();
var lstname = activeSheet.getRange(i,3).getValue();
var gradelvl = activeSheet.getRange(i,5).getValue();

var msgbody = templateTxt.replace("{name of student}",(frstname + " " + lstname)).replace("{Gr Lvl}",gradelvl);

MailApp.sendEmail("email add","Test Email",templateTxt);
}

Logger.log(msgbody);
}
}'

下面是我想自动化的图片数据。

在此处输入图片描述 谢谢

标签： google-apps-script

Answer 1

您的代码有几个错误。让我在我对此工作代码的评论中向您展示。

代码：

function sendEmail() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var templateTxt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
  var lr = activeSheet.getLastRow();
  // Compile list of students with "Touch Course Completed" in array before sending
  var msgbody = [];

  // You need to get the range of all data on column I for it to be optimized
  var dRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("March Cycle").getRange("I6:I"+lr);
  var rData = dRange.getValues();
  // rData is equal to [[Touch Course Completed],[Touch Course Completed],[Not completed]]

  // Loop all column I data
  rData.forEach(function (data, i){
    // Since rData is 2D array, the cell should be accessed by data[0]
    if (data[0] == "Touch Course Completed") {
      // i is the index of the rData array per loop, so 0 is equivalent to row 6
      // We need to offset it to get the correct row
      var frstname = activeSheet.getRange(i + 6, 4).getValue();
      var lstname = activeSheet.getRange(i + 6, 3).getValue();
      var gradelvl = activeSheet.getRange(i + 6, 5).getValue();

      // My idea here is optional, but I prefer sending it on 1 email instead of separate per student
      // That way, we do it faster and more efficient (note that there are quota/limits on sending mail thus doing this is better)
      // But if you need it separate, then do what you did in your script
      // I push all message first, then send as bulk outside the loop with join
      msgbody.push(templateTxt.replace("{name of student}", (frstname + " " + lstname)).replace("{Gr Lvl}", gradelvl));
    }
  });
  // Send an email only IF there is a "Touch Course Complete" student
  if(msgbody)
    MailApp.sendEmail("email", "Test Email", msgbody.join("\n"));
} 

三月周期：

模板：

电子邮件：

笔记：

• 请注意，早期的测试确实单独发送了电子邮件。如果您需要将它们分开，则在每个循环中发送它（不需要数组）。但如果不是，那么上面的代码应该会更好。

编辑：

要仅发送最后一行，可以想到两种方法：

1. 如果数据已经发送，则添加一列用于标识数据
   • 您将需要添加一列。
   • 如果新数据不包含“Touch Course Completed”，则不会发送任何内容。
   • 如果您添加了包含“Touch Course Completed”的多行，则将全部发送。
2. 获取最后一行msgbody
   • 您不需要添加列。
   • 如果新数据不包含“Touch Course Completed”，它将发送之前已经发送的带有“Touch Course Completed”的最后一行。
   • 如果您添加了超过 1 个“触摸课程已完成”，这将不会发送多行

第一种方法：

function sendEmail() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var templateTxt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
  var lr = activeSheet.getLastRow();
  var msgbody = [];
  // Get all headers, check if there is "Already Sent" header
  var lc = activeSheet.getLastColumn();
  var headers = activeSheet.getRange(1, 1, 1, lc).getValues();
  var sentColumn;
  if(!headers[0].includes("Already Sent")){
    // If not found, add header "Already Sent" right to the last column
    sentColumn = lc + 1;
    activeSheet.getRange(1, sentColumn).setValue("Already Sent");
  }
  else {
    // If found, get column number of existing "Already Sent" header
    sentColumn = headers[0].indexOf("Already Sent") + 1;
  }

  var dRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("March Cycle").getRange("I6:I"+lr);
  var rData = dRange.getValues();
  rData.forEach(function (data, i){
    if (data[0] == "Touch Course Completed") {
      var frstname = activeSheet.getRange(i + 6, 4).getValue();
      var lstname = activeSheet.getRange(i + 6, 3).getValue();
      var gradelvl = activeSheet.getRange(i + 6, 5).getValue();
      // Check if column is already populated with "Y"
      var isSent = activeSheet.getRange(i + 6, sentColumn).getValue();
      if(isSent != "Y"){
        // If column value is not "Y", then add row to the email to be sent, also put "Y" on the column after
        msgbody.push(templateTxt.replace("{name of student}", (frstname + " " + lstname)).replace("{Gr Lvl}", gradelvl));
        activeSheet.getRange(i + 6, sentColumn).setValue("Y");
      }
    }
  });
  // Modified condition for checking array
  if(msgbody.length > 0)
    MailApp.sendEmail("email", "Test Email", msgbody.join("\n"));
} 

样本数据：

输出：

电子邮件：

笔记：

• 该脚本将自动添加/定位标题，因此无需手动调整您的工作表。
• 但是您仍然可以通过在列的第一行写入标题名称并将“Y”写入您不想再发送的那些行来将列初始化为“已发送”。