当单元格为特定颜色时触发电子邮件吗? [英] Trigger an email when a cell is a certain colour?
问题描述
对此我的知识非常有限.太少了,我什至都不应该将其称为知识"
I have very VERY limited knowledge on this. So little I probably shouldn't even refer to it as "knowledge"
这就是我想要做的: 我有一列提到发送日期的日期.如果日期已经过去,则该单元格将变为红色(#a33838).如果该列中的单元格变成红色,我想发送一封电子邮件,以便我知道它尚未发送,需要处理. 我还希望电子邮件保存该单元格所在行的信息
This is what I'm trying to do: I have a column with dates referring to send dates. If the date has already passed, the cell turns red (#a33838). I want to send an email if a cell in that column turns red, so that I know it has not been sent, and needs doing. I also want the email to hold the information of the row that cell is in
我一直在浏览论坛,我认为我需要混合使用这两种不同的帖子:
I have been looking through the forums and I think I need a mixture of these two different posts:
如果有人知道这样做的方法,那将真的是一个救命稻草! 非常感谢.
If anyone knows a way to do this, it would really be a lifesaver! Thanks so much.
推荐答案
此代码将为您提供所需的帮助
This code will help you in what you need
var EMAIL_ADDRESS = "email@domain";
var SUBJECT = "Passed dates";
var message = "";
function alertDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // get the sheet where values are
var date_col = sheet.getRange(1, 1, sheet.getLastRow()); // get the column with the dates
var dates = date_col.getValues();
var row = 0;
for(var i = 0; i < dates.length; i++){ // iterate over all dates
if( dates[i] < Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy")){ // check if date already passed
date_col.getCell(i + 1, 1).setBackground("#A33838"); // change cell´s color
row = i + 1;
message += "This date " + dates[i] + " on row " + row.toString() + " has already passed\n"; // build message to send
}
}
MailApp.sendEmail(EMAIL_ADDRESS, SUBJECT, message); // send email
}
注意:
我在示例中使用的日期格式为 "MM/dd/yyyy" ,因此,如果您在工作表中使用其他格式,则需要改变这个:
Notice:
I´m using the format "MM/dd/yyyy" for the dates in my example, so if you use another format in your sheet, you need to change this:
Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy")
这篇关于当单元格为特定颜色时触发电子邮件吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!