The OP's scenario was, to an extent, unique because it required batching of email to vendors, and compiling an HTML email for items relating to each vendor (based on the value of a checkbox.
This code:
- Takes data from a product group sheet (the sheet name is a variable, so the code can be further automated),
- Creates a temporary list of vendors,
- Loops through the vendors, one at a time
- Loops through the data and captures any item where the vendor name is a match AND the checkbox is un-ticked (false).
- the vendor item data is progressively written to an array, and when complete the array is written to a temporary sheet (though perhaps this can be further fine-tuned)
- An html message is created from the temporary sheet data.
- The message is sent to the vendor using Gmail.sendEmail.
- Clears the temporary take info from the temporary output sheet
- Re-sets the unticked checkboxes back to "checked"
function so5582181508() {
//setup spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var datasheetname = "HTS";
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 htslast = datasheet.getLastRow();
//Logger.log("htslast = "+htslast);
// get the supplier column
var supplierRange = datasheet.getRange(3,7,Alast-2,1);
//Logger.log("the supplier range = "+supplierRange.getA1Notation());
//get the supplier data
var supplierData = supplierRange.getValues();
//get the status column
var statusRange = datasheet.getRange(3,9,Alast-2,1);
//Logger.log("the status range = "+statusRange.getA1Notation());
// 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]);
//Logger.log("length of new array = "+supplierCount);
//Logger.log("Number of items in table = "+transCount);
// loop through the data, once for every supplier
for (supplier = 0; supplier<supplierCount; supplier++){
mailMessage=[];
itemCount = 0;
//Logger.log("supplier = "+supplier);
//Logger.log("supplier = "+supplierList[supplier]);
// now loop through the data
// start i = 2 to allow for header
for (var i = 2; i < transCount+2; i++) {
mailItem=[];
//Logger.log("i = "+i+", SupplierList: "+supplierList[supplier]+", supplier: "+transData[i][6]+", status:"+transData[i][8])
// 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];
//Logger.log("send email to "+supplierList[supplier]+", at "+transData[i][5]);
//Logger.log("Item: "+transData[i][0]+", Spec: "+transData[i][1]+", color: "+transData[i][3]+", quantity: "+transData[i][4]+", Unit: "+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();
//Logger.log("ROW#1 col1="+messagedata[0][0]+", column 2: "+messagedata[0][1]);
//Logger.log("ROW#1 col1="+messagedata[1][0]+", column 2: "+messagedata[1][1]);
//Logger.log("message data length"+messagedata.length);
var messageitemcount = messagedata.length;
//Logger.log("send email to "+supplierList[supplier]+", at "+emailAddress+", message: "+mailMessage);
// 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
}
//Logger.log("header:"+header);
// 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>';
// Logger.log("DEBUG: message: "+message);//DEBUG
// 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);
}
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
