根据单元格值发送电子邮件
问题描述:
我正尝试每周向具有出色报告的经理发送电子邮件。在发送提醒时,代码应该考虑列“E”中的单元格的值。我使用的代码粘贴在互联网上的各种来源,它执行没有任何错误,但它不会发送任何电子邮件,即使条件已满足。我将不胜感激任何帮助。根据单元格值发送电子邮件
正在使用的数据出现在“区域管理器”表单上。
,这是我的代码尝试:
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);
})}};
}
答
你的代码是有点乱。您声明一些变量,你不use.Here是你的问题的根本解决办法:
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),如果您发送的邮件过多,则可能会被阻止。