有没有一种方法可以获取表的某个期望范围，以根据列的值发送电子邮件? [英] Is there a way to get some desired range of a table to send email based on the value of a column?
问题描述
我日常工作的一部分是将一系列的Google表格单元格发送给供应商，以要求提供材料样本来制作背包.
Part of my daily work is to send a range of cells of google sheet to the suppliers to request material samples to make a backpack.
我写了一些非常有用的脚本.但是有了这个，我真的不知道显示一些尝试过的代码".请在下面查看我的描述.
I've written some scripts that are really helpful. But with this one, I really have no clue to "show some tried code". Please see my description below.
-
我很想拥有一个通过VENDOR和STATUS列运行的脚本，用于根据[项目名称(i，1):UNIT(i) ，5)]
I'd love to have a script that runs thru column VENDOR and STATUS to decide what VENDOR to send email to MAIL(i,6) by information from [ ITEM NAME (i, 1): UNIT(i,5) ]
我只想将电子邮件发送到STATUS值= false(未选中)的行，并且在发送电子邮件之后，我将让脚本将单元格值从false→true更改(因此，如果我下次再次运行该脚本，它不会将信息复制到接收方)
I'd love to send email only to rows that have STATUS value = false ( unchecked ), and after sending an email I will let the script change the cell value from false → true (so next time if I run the script again, it won't duplicate the information to the receiver)
对我来说，最难的部分是我不知道如何通过VENDOR名称收集信息.因此，在上面的Google工作表中，我希望按订单发送3封电子邮件:
And the hard part to me is I do not know how to collect information by VENDOR name. So with the google sheet table above, I would love to send 3 emails by order:
a. SupplierC@gmail.com(一星)→发送第21 + row24 + row26行(仅从A到E +列，然后检查单元格)
a. supplierC@gmail.com ( ONE STAR ) → send row 21+row24+row26 ( only column A to E + then check the cell )
b. SupplierB@gmail.com(YKK)→发送第22 + row25行(仅将A列发送至E +，然后检查单元格)
b. supplierB@gmail.com ( YKK ) → send row 22+row25 ( only column A to E + then check the cell )
c. SupplierA@gmail.com(DUCKSAN)→仅发送第27行(仅将A列发送到E +然后检查该单元格)(因为第23行已选中-表示我已经发送或我现在不想发送)
c. supplierA@gmail.com ( DUCKSAN ) → send row 27 only ( only column A to E + then check the cell ) ( because row 23 is checked - means I've already sent or I do not want to send now )
照片:
表格:
a :
b :
c :
问题更新 有了我第一个问题的信息，Tedinoz的以下代码对我来说非常有用.
QUESTION UPDATE With my first information for the question, the Tedinoz's codes belows works great to me.
但是，如果在"HTS"工作表中，电子邮件"列替换为材料代码"列，然后我将在"dev"工作表中包含所有供应商信息，我将使用这些信息每天监控所有品牌或通过创建一张用于监控供应商的表(请对此提供建议).请再次检查stackoverflow电子表格，我包括了"dev"表作为参考(向下滚动到第42行，其中供应商信息行开始)
But what if in "HTS" sheet, the email column is replaced by the material code column, and then I will include all the suppliers information in the "dev" sheet which I use to monitor all the brands everyday or by creating a sheet just for monitor the suppliers ( please advise on this ). Please check the stackoverflow spreadsheet again, I included the "dev" sheet for reference ( scroll down to Row 42 where the suppliers information row starts)
推荐答案
在某种程度上，OP的场景是独特的，因为它需要向供应商分批发送电子邮件，并为与每个供应商相关的项目编译HTML电子邮件(基于复选框的值.
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.
此代码:
- 从产品组工作表中获取数据(工作表名称是变量，因此代码可以进一步自动化)，
- 创建供应商的临时列表，
- 一次遍历供应商
- 浏览数据并捕获任何与供应商名称匹配的项目. AND 未选中此复选框(false).
- 将供应商项目数据逐步写入数组，完成后将数组写入临时表(尽管也许可以对其进行微调)
- 根据临时工作表数据创建html消息.
- 使用Gmail.sendEmail将邮件发送给供应商.
- 从临时输出表中清除临时收货信息
- 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
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); }这篇关于有没有一种方法可以获取表的某个期望范围，以根据列的值发送电子邮件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
