google-apps-script - 根据单元格值发送电子邮件

Question

我正在尝试每周向具有出色报告的经理发送电子邮件。发送提醒时，代码应该只考虑“E”列中单元格的值。我使用的代码是从 Internet 上的各种来源粘贴在一起的，它执行时没有任何错误，但即使满足条件也不会发送任何电子邮件。我将不胜感激任何帮助。

正在使用的数据显示在“区域经理”表中。

这是我对代码的尝试:

function sendEmails() {

var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Area Managers");
var range = sSheet.getDataRange();  
var column = 4;
var cValues = Number(sSheet.getRange(2, column, sSheet.getLastRow()).getValue()); 

  var startRow = 2;
  var numRows = sSheet.getLastRow()-1;
  var dataRange = sSheet.getRange(startRow, 1, numRows, 2)
  var data = dataRange.getValues();

  for (i in data) {
    var row = data[i];

    if(row[5] >= 0) {  
   range = range.offset(1, 0, range.getNumRows()-1);
   range.getValues().forEach( function( recipient, key, data ) {
    var body = "Good day "+ recipient[0] + " you have " + recipient[4] + " Internal Audit reports outstanding.";
    GmailApp.sendEmail(recipient[0] +"<"+recipient[1]+">","Outstanding Internal Audit Reports", body);
   })}}; 
}

Answer 1

你的代码有点乱。您声明了一些您​​不使用的变量。这是您问题的简单解决方案:

function sendEmails() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Area Managers"); //get the sheet "Area Managers"
  var data = sSheet.getRange(2, 1, sSheet.getLastRow(), sSheet.getLastColumn()).getValues(); //get the values of your table, without the header

  for (var i = 0; i < data.length; i++) { // For each element of your tab

    var row = data[i];

    if(row[5] > 0) {  // If the person have got at least 1 report outstanding (column E)

        var body = "Good day "+ row[0] + " you have " + row[5] + " Internal Audit reports outstanding.";
        GmailApp.sendEmail(row[1],"Outstanding Internal Audit Reports", body); // Sending a mail to alert

    }
  } 
}

代码是注释所以你可以理解这个过程

PS:GmailApp 邮件发送( see here)的配额请注意，发送过多邮件可能会被屏蔽