javascript - 根据列的值发送电子邮件?
问题描述
我有两张纸 1. SS & 2. MessageOutput。
第一次运行时,电子邮件已成功发送。但是第二次运行,我们得到错误:
例外:范围内的行数必须至少为 1
这是上线:var messageRange = messagesheet.getRange(1, 1, mailMessage.length, 5);
这是代码:
function sendEmail() {
//setup spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var datasheetname = "SS";
var datasheet = ss.getSheetByName(datasheetname);
var messagesheetname = "MessageOutput";
var messagesheet = ss.getSheetByName(messagesheetname);
var templatesheetname = "Email Template";
var templatesheet = ss.getSheetByName(templatesheetname);
messagesheet.clear();
// get the number of rows on the data sheet
var Avals = datasheet.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;
//Logger.log("Alast = "+Alast);
var sslast = datasheet.getLastRow();
// get the supplier column
var supplierRange = datasheet.getRange(3,7,Alast-2,1);
//get the supplier data
var supplierData = supplierRange.getValues();
//get the status column
var statusRange = datasheet.getRange(3,9,Alast-2,1);
// get the status data
var statusData = statusRange.getValues();
var transCount = supplierData.length;
var supplierList = [];
var transData = datasheet.getDataRange().getValues();
// supplierList contains the unique supplier list
supplierData.forEach(function(x){
if(supplierList.indexOf(x[0]) === -1 && x[0]!="" ){
supplierList.push(x[0]);
}
});
var supplierCount = supplierList.length;
var itemCount = 0;
var mailMessage = [];
var mailItem = [];
//build the mail item header
var mailItemHeader = [];
mailItemHeader.push(transData[0][0]);
mailItemHeader.push(transData[0][1]);
mailItemHeader.push(transData[0][2]);
mailItemHeader.push(transData[0][3]);
mailItemHeader.push(transData[0][4]);
//mailItemHeader.push(transData[0][6]);
// loop through the data, once for every supplier
for (supplier = 0; supplier<supplierCount; supplier++){
mailMessage=[];
itemCount = 0;
// now loop through the data
// start i = 2 to allow for header
for (var i = 2; i < transCount+2; i++) {
mailItem=[];
// the suplier matches and if the checkbox is false
if (supplierList[supplier] == transData[i][6] && transData[i][8] == false){
// this this is the first item then push the mail header
if (itemCount ==0){
mailMessage.push(mailItemHeader);
// get the email address
var emailAddress = transData[i][5];
var subject = "Purchase order";
}
// this is a match
var emailAddress = transData[i][5];
// push the transation values for this row onto the mailitem array
mailItem.push(transData[i][0]);
mailItem.push(transData[i][1]);
mailItem.push(transData[i][2]);
mailItem.push(transData[i][3]);
mailItem.push(transData[i][4]);
//mailItem.push(transData[i][6]);
// push the row onto the rest of the mail message data
mailMessage.push(mailItem);
itemCount=itemCount+1
//update the status value to true
statusData[i-2] = [true];
}
else
{
//Logger.log("no match");
}
} // end of the transaction loop for this supplier
// define the temporary output range
var messageRange = messagesheet.getRange(1, 1, mailMessage.length, 5);
// paste the items details to the temporary output range
var messageupdate = messageRange.setValues(mailMessage);
// get the values for the items only (no header)
var messagedata = messagesheet.getRange(2, 1, mailMessage.length-1, 5).getValues();
var messageitemcount = messagedata.length;
// create a subject
var emailSubject = "Purchase Order: StackOverflow Test";
// get the email address
var emailaddress = emailAddress;
// message
var messagePrefix = "Attention: "+supplierList[supplier];
// start the build of the html message
var columns = 5;
var columncount=1;
var message = 'Please supply the following products:<br><br><table style="border-collapse:collapse;" border = 1 cellpadding = 5>';
// get the headers
for (h=0; h<columns;h++){
if (columncount ==1){
var header = '<tr>';
}
header+='<th style="background-color:#ffeb3b">'+mailItemHeader[h]+'</th>';
if (columncount ==5){
header+='</tr>';
}
columncount=columncount+1
}
// add the header to the mesage
message+=header;
// loop through the items on the temporary output and get the item values
for(c=0;c<messageitemcount;c++){
// increment message
message+='<tr><td>'+messagedata[c][0]+'</td>'+'<td>'+messagedata[c][1]+'</td>'+'<td>'+messagedata[c][2]+'</td>'+'<td>'+messagedata[c][3]+'</td>'+'<td>'+messagedata[c][4]+'</td></tr>';
}
// finalise the message
message+='</table>';
// send the email
GmailApp.sendEmail(emailaddress, emailSubject, messagePrefix, {htmlBody: message, });
// clear the state from the temporary outsheet sheet
messagesheet.clear();
}
//update the status range - return all to ticked (true)
statusRange.setValues(statusData);
}
解决方案
例外:范围内的行数必须至少为 1
表示
mailMessage.length
在
var messageRange = messagesheet.getRange(1, 1, mailMessage.length, 5);
是0
。
if
您可以使用语句捕获这种情况,例如:
if(mailMessage.length>0){
var messageRange = messagesheet.getRange(1, 1, mailMessage.length, 5);
// paste the items details to the temporary output range
var messageupdate = messageRange.setValues(mailMessage);
...
//the rest of your code that should be run only if mailMessage is not empty
GmailApp.sendEmail(emailaddress, emailSubject, messagePrefix, {htmlBody: message, });
...
}
...
请注意,您可能想查看您的代码并了解为什么mailMessage.length
为 0。似乎您只将值分配给mailMessage
, 时
if (supplierList[supplier] == transData[i][6] && transData[i][8] == false)
,
因此您可以将其余的消息发送相关代码放在同一个语句中。
推荐阅读
- javascript - Application.js 找不到@rails
- microservices - 我不懂事件溯源
- reactjs - 如何始终覆盖具有不确定后缀的 JSS 样式?
- python - discord.py-rewrite 'VoiceClient' 对象没有属性 'ws'
- python - 使用 pip 安装 Python 包时出错:
- javascript - 在 Javascript 中,什么被认为是“计算成本高”?
- reactjs - 为什么 React 状态不会与受控组件的输入更改同时更改
- php - PHP将键值对添加到数组中,其中值是数组,键是ID
- pandas - 在 MatPlotLib 中绘制不同时基上的两个并行测量之间的差异
- django - DJANGO - 类 Meta / varibales db_table