情况:
以下脚本将检查 N 列中的数据,如果从 N 列第 3 行开始的任何单元格有数据,该脚本将提取数据并通过电子邮件发送给我。
但是,如果从 N 列中的 ROW3 开始的任何单元格没有数据,则在电子邮件正文中显示"亲爱的团队,暂时有任何问题">
我该怎么做?如果没有,我会尝试使用,但我认为这不是正确的方法。
我的脚本如下:
function emailv1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Support = ss.getSheetByName("Shift");
var lastrow = Support.getLastRow();
var data1 = Support.getRange(3, 1, lastrow).getValues(); // XML ID
var data2 = Support.getRange(3, 2, lastrow).getValues(); // SEQ NAME
var data3 = Support.getRange(3, 14, lastrow).getValues(); // Notes
var date2 = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm"); // Funtion Date + Format
//var name = Browser.inputBox('Monitor', 'Ingrese el Horario Monitoreado', Browser.Buttons.OK_CANCEL);
/////////////////////// FOLLOWUP TABLE /////////////////////////
var report = " "
var supportTable ="";
for(var i in data3)
{
if(data3[i]!="")
{ supportTable += "<tr><td style='color:blue;padding:5px'>" + data1[i]
+ "</td><td style='padding:5px'>" + data2[i]
+ "</td><td style='padding:5px'>" + data3[i]
+ "</td></tr>";
report ="Dears: <br><br><b>The following sequences were reported with faults:</b><br><br><table style='border-collapse:collapse;'border = 1 cellpadding = 5; align='center'><tr><th style='background-color:black;color:white'>XML ID</th><th style='background-color:black;color:white'>SEQUENCER</th><th style='background-color:black;color:white'>Notes</tr>" + supportTable + "</table>";
}
}
/////////////////////////////////////////////////////////////
/////////////////////// URL SHIFT ////////////////////////////
var report4 = "<br><H1 align='center'><a href=https://docs.google.com/spreadsheets/d/XXXXXX>URL SHIFT</a></H1>"
/////////////////////////////////////////////////////////////
MailApp.sendEmail("XXXX@gmail.com",
"[CUSTOMER NAME] Shift Change - " + date2,
report,
{name: 'Automatic Emailer Script',
htmlBody: report +'<br><br><br><br>'+ report4});
Support.getRange('N3:N133').clearContent(); // I want to put all cell in column N but I don't know what but for that razon I put a range.
}
我将不胜感激任何帮助,我尝试以下
else if(data3[i]="") {report="text"]; //not working
else if(data3[i]=="") {report="text"]; //not working
else if(data3[i]==="") {report="text"]; //not working
从您的评论来看,您似乎想向代码添加新功能。当 N 列为空时,您会收到一封电子邮件,其中包含文本"亲爱的团队,暂时有任何问题"(不过不确定这意味着什么!
如果您在supportTable中有任何数据,我将代码修改为cehck,如下所示:
if (supportTable == ""){
report = "Dear Team, any problem for the moment"
} else {
report ="Dears: <br><br><b>The following sequences were reported with faults:</b><br><br><table style='border-collapse:collapse;'border = 1 cellpadding = 5; align='center'><tr><th style='background-color:black;color:white'>XML ID</th><th style='background-color:black;color:white'>SEQUENCER</th><th style='background-color:black;color:white'>Notes</tr>" + supportTable + "</table>";
}
如果你愿意,你甚至可以在这里@AlGoreRythm使用isEmpty(str(。如果该变量中没有数据,则将电子邮件的正文设置为上述字符串。编辑:我之前错过的是getValues提供了一个2D数据数组,要访问数据,您必须使用两个索引,而不是像以前那样使用一个索引(例如:data[i][0]而不是data[i](
function emailv1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Support = ss.getSheetByName("Shift");
var lastrow = Support.getLastRow();
Logger.log("LastRow: "+lastrow)
var data1 = Support.getRange(3, 1, lastrow-2).getValues(); // XML ID
var data2 = Support.getRange(3, 2, lastrow-2).getValues(); // SEQ NAME
var data3 = Support.getRange(3, 14, lastrow-2).getValues(); // Notes
var date2 = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm"); // Funtion Date + Format
//var name = Browser.inputBox('Monitor', 'Ingrese el Horario Monitoreado', Browser.Buttons.OK_CANCEL);
/////////////////////// FOLLOWUP TABLE /////////////////////////
var report = " "
var supportTable ="";
for(var i in data3)
{
if(data3[i][0]!="")
{ supportTable += "<tr><td style='color:blue;padding:5px'>" + data1[i][0]
+ "</td><td style='padding:5px'>" + data2[i][0]
+ "</td><td style='padding:5px'>" + data3[i][0]
+ "</td></tr>";
}
else {
Logger.log("Empty Row: " + i)
}
}
/////////////////////////////////////////////////////////////
/////////////////////// URL SHIFT ////////////////////////////
// Check to see if supoortTable has any data in it, if not set the text to "Dear Team, any problem for the moment"
if (supportTable == ""){
report = "Dear Team, any problem for the moment"
} else {
report ="Dears: <br><br><b>The following sequences were reported with faults:</b><br><br><table style='border-collapse:collapse;'border = 1 cellpadding = 5; align='center'><tr><th style='background-color:black;color:white'>XML ID</th><th style='background-color:black;color:white'>SEQUENCER</th><th style='background-color:black;color:white'>Notes</tr>" + supportTable + "</table>";
}
var report4 = "<br><H1 align='center'><a href=https://docs.google.com/spreadsheets/d/XXXXXX>URL SHIFT</a></H1>"
Logger.log(report)
/////////////////////////////////////////////////////////////
MailApp.sendEmail("xxxx@gmail.com",
"[CUSTOMER NAME] Shift Change - " + date2,
report,
{name: 'Automatic Emailer Script',
htmlBody: report +'<br><br><br><br>'+ report4});
Support.getRange('N3:N133').clearContent(); // I want to put all cell in column N but I don't know what but for that razon I put a range.
}
有这个函数
function isEmpty(str) {
return (!str || 0 === str.length);
}
这将检查是否有任何字符串为空。如果您的字符串具有" "
则此操作将失败。如果您需要检查字符串是否为空或不包含空格,您可以这样做:
function isEmptyBrute(str) {
str = str.replace(/s/g, '');
return (!str || 0 === str.length);
}
然后你可以简单地这样做:
//if it's empty it will return true
if(isEmpty(data3[i])) ...
工作示例:https://jsfiddle.net/dL0h93ew/