根据单元格值发送电子邮件 [英] Sending emails based on cell values

问题描述

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

I am trying to send weekly emails to managers with outstanding reports. The code should just take into account the value of cells in column "E" when sending reminders. The code I am using is pasted together from various sources on the internet, it executes without any errors but it does not send any emails even when the conditions have been met. I would greatly appreciate any help.

正在使用的数据显示在区域经理"表中.

Data being used is present on the "Area Managers" sheet.

这是我尝试输入的代码:

And this is my attempt at the code:

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

推荐答案

您的代码有点混乱.您声明一些不使用的变量.这里只是您问题的简单解决方案:

Your code is a bit messy. You declare some variable that you don't use.Here is a simply solution of your problem:

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

    }
  } 
}

代码为注释，因此您可以了解过程

The code is comment so you can understand the process

PS:您应该注意GmailApp邮件发送的配额(参见此处)，如果您发送太多邮件，则可能会被屏蔽

PS: You should be careful about the quota with the GmailApp mail sending (see here), you may be blocked if you send too many mail

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