For循环和if语句的多个条件 [英] For loop and if statement for multiple conditions
问题描述
你好，我有一个头部划痕的时间，如果条件。
当用户从列E，列F或列G的下拉菜单中选择一个值时，我将清楚该工作表是如何工作的。 H列将被更新为一个值。
它应该如何工作：
团队1 - E栏
团队1将从下拉列表中选择请求已发送。该脚本将在H列执行setvalueREQUEST_SENT。
第2组 - 列F
小组2将选择收到请求。该脚本将在H列执行setvalueREQUEST_RECEIVED。
团队2将选择请求上传。该脚本将在H列中执行setvalueREQUEST_Uploading。
第2组将选择Upload Completed。该脚本将在H列中执行setvalueUPLOAD_COMPLETED。
第3队 - G列
<第二小组将选择请求传送。该脚本将在H列中执行setvalueREQUEST_TRANSFERRING。
第2组将选择请求评论。该脚本将在H列中执行setvalueREQUEST_IN_REVIEW。
第2组将选择请求审阅完成。该脚本将在H列执行setvalueREVIEW_COMPLETED。
第1队 - E列
团队1将从下拉列表中选择请求已批准或选择请求已拒绝。该脚本将在H列中执行setvalueREQUEST_APPROVED或REQUEST_REJECTED。
第2组 - F列
如果第1组选择了请求已批准。团队2将选择刻录进行中。该脚本将在H列中执行setvalueBURN_IN_PROGRESS。
团队2将选择Burn Completed。该脚本将在H列中执行setvalueBURN_COMPLETED。
第1队 - E列
团队1将选择请求已发货或从下拉列表中选择刻录已拒绝。该脚本将在H列中执行setvalueREQUEST_SHIPPED或BURN_REJECTED。
下面的脚本执行我想要的操作，但是如果在列E中选择了每个值， F或G，列H值根据列E或列F改变为不同的值。
想要根据最后一列的选定值设置值。我曾想过为每个团队创建更新列，但是这样会使表格变得更大。尽量保持工作表紧凑。
这里的代码
团队1
var REQUEST_SENT =REQUEST_SENT;
var REQUEST_APPROVED =REQUEST_APPROVED;
var REQUEST_REJECTED =REQUEST_REJECTED;
var REQUEST_SHIPPED =REQUEST_SHIPPED;
var BURN_REJECTED =BURN_REJECTED;
var REQUEST_CANCELLED =REQUEST_CANCELLED;
//团队2
var REQUEST_RECEIVED =REQUEST_RECEIVED;
var REQUEST_TRANSFERRING =REQUEST_TRANSFERRING;
var REQUEST_COMPLETED =REQUEST_COMPLETED;
//团队3
var REQUEST_IN_REVIEW =REQUEST_IN_REVIEW;
var PM_REVIEW =PM_REVIEW;
var REQUEST_TRANSFERRING =REQUEST_TRANSFERRING;
var BURN_IN_PROGRESS =BURN_IN_PROGRES;
var BURN_COMPLETED =BURN_COMPLETED;
函数pmSendEmails（）{
var ss1 = SpreadsheetApp.getActiveSpreadsheet（）。getSheets（）[0];
var ss2 = SpreadsheetApp.getActiveSpreadsheet（）。getSheets（）[1];
Logger.log（'Sheet1：'+ ss1.getSheetName（））;
Logger.log（'Sheet2：'+ ss2.getSheetName（））;
var startRow = 2;
var lastRow1 = ss1.getLastRow（） - 1;
var lastRow2 = ss2.getLastRow（） - 1;
var range1 = ss1.getRange（startRow，1，lastRow1，8）;
var range2 = ss2.getRange（startRow，1，lastRow2，3）;
var data1 = range1.getValues（）;
var data2 = range2.getValues（）;
var addresses = [];
for（var i = 0; i< data2.length; ++ i）{
var row2 = data2 [i];
var cc = row2 [1];
var replyTo = row2 [2];
addresses.push（[cc，replyTo]）
}
var html;
for（var i = 0; i< data1.length; ++ i）{
var row1 = data1 [i];
var projectName = row1 [0];
var projectID = row1 [1];
var projectManager = row1 [2];
var dueDate = Utilities.formatDate（new Date（row1 [3]），America / New_York，MMMM dd，yyyy）;
var team1 = row1 [4];
var team2 = row1 [5];
var team3 = row1 [6];
var status = row1 [7];
这里我需要把if语句和条件更新列H
if（condition ==&&（condition！=））{
}
如果条件为真，下面的代码也会执行。
var subject =新DVD请求+ projectName +（+ projectID +）;
var user = Session.getActiveUser（）。getEmail（）;
var timestamp = Utilities.formatDate（new Date（），America / New_York，MMMM dd，yyyy HH：mm）;
var comment1 = ss1.getRange（startRow + i，5）.getNote（）;
comments1 = comments1 +请求发送：\\\
+ user +\\\
+ timestamp +\\\
;
var comments = ss1.getRange（startRow + i，8）.getNote（）;
comments = comments +EMAIL_SENT：\\\
+ timestamp +\\\
;
html = projectName ++ projectID ++ projectManager ++ dueDate;
var setdata = ss1.getRange（startRow + i，8）.setValue（EMAIL_SENT）.setNote（comments）;
var team1Update = ss1.getRange（startRow + i，5）.setNote（comments1）;
var optAdvancedArgs = {replyTo：replyTo，cc：user，name：Venue Client Services};
SpreadsheetApp.flush（）;
//MailApp.sendEmail(projectManager，subject，html，optAdvancedArgs）;
请让我知道您是否需要更多信息或说明。任何帮助将非常感激。谢谢。
编辑12/4/14＃1
我想要发生的一些事情。每当使用E，F和G列的范围编辑单元格时，便会在活动用户电子邮件地址和时间戳中添加一个注释。在我之前的代码中，我可以通过下面的代码实现这一点。
var user = Session.getActiveUser（）。getEmail （）;
var timestamp = Utilities.formatDate（new Date（），America / New_York，MMMM dd，yyyy HH：mm）;
var comment1 = ss1.getRange（startRow + i，5）.getNote（）;
comments1 = comments1 +请求发送：\\\
+ user +\\\
+ timestamp +\\\
;
var comments = ss1.getRange（startRow + i，8）.getNote（）;
comments = comments +EMAIL_SENT：\\\
+ timestamp +\\\
;
var setdata = ss1.getRange（startRow + i，8）.setValue（EMAIL_SENT）.setNote（comments）;
var team1Update = ss1.getRange（startRow + i，5）.setNote（comments1）;
comments1为E，F，G列中的放置选择设置注释，并将注释设置为列H.当我将上面的代码放在当前的onEdit（e）脚本中时，没有任何反应。任何帮助我如何实现这一点。
编辑12/4/14＃2
在前面的代码中，能够根据E，F，G列中的下拉菜单选择发送电子邮件。因此，如果用户选择请求已发送，则发送电子邮件。对于E，F，G列中的所有下拉菜单选择，都会重复此操作。关于如何为每个选择实现MailApp的帮助。
var html = projectName ++ projectID ++ projectManager ++ dueDate;
var optAdvancedArgs = {replyTo：replyTo，cc：user，name：Venue Client Services};
MailApp.sendEmail（projectManager，subject，html，optAdvancedArgs）;
这对于可安装编辑触发器功能。另请参阅 Google表格事件。
请注意，您必须使用可作为文档所有者运行的可安装触发器，而不是简单的 onEdit（） 触发器以匿名用户身份运行，因为发送电子邮件需要用户授权。
类似这样（已测试）：
function installableOnEdit（e）{
if（！e）throw new Error（Need event parameter 。）; //请参阅http://stackoverflow.com/a/16089067
var changedCell = e.range;
var row = e.range.getRow（）;
var col = e.range.getColumn（）;
var sheet = e.range.getSheet（）;
var header = sheet.getDataRange（）。getValues（）[0]; //获取标题行1
var statusCol = headers.indexOf（'Reminder Email Status'）+ 1;
//创建一组我们关心的行。如果标题更改，则需要更新
var teamCols = [headers.indexOf（'Team 1（PM）'）+ 1，
headers.indexOf（'Team 2（Colombo）'）+ 1，
headers.indexOf（'Team 3（VCS）'）+ 1];
if（teamCols.indexOf（col）== -1）return; //如果队列外的单元格退出
var status = e.value.toUpperCase（）。replace（/ / g，_）; //将请求已发送更改为REQUEST_SENT
sheet.getRange（row，statusCol）.setValue（status）;
// ---------把你的电子邮件代码放在这里，或者调用一个函数
}
//读取用户的当前单元格，作为事件提供给installableOnEdit（）。
//见http://stackoverflow.com/a/16089067
函数test_installableOnEdit（）{
installableOnEdit（{
user：Session.getActiveUser（）。getEmail（），
source：SpreadsheetApp.getActiveSpreadsheet（），
range：SpreadsheetApp.getActiveSpreadsheet（）。getActiveCell（），
value：SpreadsheetApp.getActiveSpreadsheet（）。getActiveCell（）。getValue（），
authMode：LIMITED
}）;
}
Hello I am having a head scratching time with the if conditions. I will make it clear how the sheet is suppose to work.
When a users select a value from the drop down menus in column E, column F, or column G. Column H will be update will a value.
How it should work:
Team 1 - Column E
Team 1 will select "Request Sent" from the drop down list. The script will execute setvalue "REQUEST_SENT" in column H.
Team 2 - Column F
Team 2 will select "Request Received". The script will execute setvalue "REQUEST_RECEIVED" in column H.
Team 2 will select "Request Uploading". The script will execute setvalue "REQUEST_Uploading" in column H.
Team 2 will select "Upload Completed". The script will execute setvalue "UPLOAD_COMPLETED" in column H.
Team 3 - Column G
Team 2 will select "Request Transferring". The script will execute setvalue "REQUEST_TRANSFERRING" in column H.
Team 2 will select "Request in Review". The script will execute setvalue "REQUEST_IN_REVIEW" in column H.
Team 2 will select "Request Review Completed". The script will execute setvalue "REVIEW_COMPLETED" in column H.
Team 1 - Column E
Team 1 will select "Request Approved" or select "Request Rejected" from the drop down list. The script will execute setvalue "REQUEST_APPROVED" or "REQUEST_REJECTED" in column H.
Team 2 - Column F
If Team 1 selected "Request Approved". Team 2 will select "Burn in Progress". The script will execute setvalue "BURN_IN_PROGRESS" in column H.
Team 2 will select "Burn Completed". The script will execute setvalue "BURN_COMPLETED" in column H.
Team 1 - Column E
Team 1 will select "Request Shipped" or select "Burn Rejected" from the drop down list. The script will execute setvalue "REQUEST_SHIPPED" or "BURN_REJECTED" in column H.
The script below executes what I want, but when every a value is selected in column E, F or G, column H value changes to a different value based on column E or column F.
Would like the value to be set based on the last column's selected value. I have thought about creating update columns for each team, but the will make the sheet even larger. Try to keep the sheet compact.
Here is the Google Spreadsheet
Here the code
Team 1
var REQUEST_SENT = "REQUEST_SENT";
var REQUEST_APPROVED = "REQUEST_APPROVED";
var REQUEST_REJECTED = "REQUEST_REJECTED";
var REQUEST_SHIPPED = "REQUEST_SHIPPED";
var BURN_REJECTED = "BURN_REJECTED";
var REQUEST_CANCELLED = "REQUEST_CANCELLED";
//Team 2
var REQUEST_RECEIVED = "REQUEST_RECEIVED";
var REQUEST_TRANSFERRING = "REQUEST_TRANSFERRING";
var REQUEST_COMPLETED = "REQUEST_COMPLETED";
//Team 3
var REQUEST_IN_REVIEW = "REQUEST_IN_REVIEW";
var PM_REVIEW = "PM_REVIEW";
var REQUEST_TRANSFERRING = "REQUEST_TRANSFERRING";
var BURN_IN_PROGRESS = "BURN_IN_PROGRES";
var BURN_COMPLETED = "BURN_COMPLETED";
function pmSendEmails() {
var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var ss2 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
Logger.log('Sheet1: '+ss1.getSheetName());
Logger.log('Sheet2: '+ss2.getSheetName());
var startRow = 2;
var lastRow1 = ss1.getLastRow()-1;
var lastRow2 = ss2.getLastRow()-1;
var range1 = ss1.getRange(startRow, 1, lastRow1, 8);
var range2 = ss2.getRange(startRow, 1, lastRow2, 3);
var data1 = range1.getValues();
var data2 = range2.getValues();
var addresses = [];
for (var i = 0; i < data2.length; ++i){
var row2 = data2[i];
var cc = row2[1];
var replyTo = row2[2];
addresses.push([cc,replyTo])
}
var html;
for (var i = 0; i < data1.length; ++i){
var row1 = data1[i];
var projectName = row1[0];
var projectID = row1[1];
var projectManager = row1[2];
var dueDate = Utilities.formatDate(new Date(row1[3]), "America/New_York", "MMMM dd, yyyy");
var team1 = row1[4];
var team2 = row1[5];
var team3 = row1[6];
var status = row1[7];
}
Here I need to put the if statement and conditions to update column H
if (condition =="" && (condition !=)){
}
Also below code will execute if the conditions are true.
var subject = "New DVD Request" + projectName + " ("+projectID+")";
var user = Session.getActiveUser().getEmail();
var timestamp = Utilities.formatDate(new Date(), "America/New_York", "MMMM dd, yyyy HH:mm");
var comments1 = ss1.getRange(startRow + i, 5).getNote();
comments1 = comments1 + "Request Sent:\n" + user + "\n" + timestamp + "\n";
var comments = ss1.getRange(startRow + i, 8).getNote();
comments = comments + "EMAIL_SENT:\n" + timestamp + "\n";
html = projectName+" "+projectID+" "+projectManager+" "+dueDate;
var setdata = ss1.getRange(startRow + i, 8).setValue(EMAIL_SENT).setNote(comments);
var team1Update = ss1.getRange(startRow + i, 5).setNote(comments1);
var optAdvancedArgs = {replyTo: replyTo, cc: user, name: "Venue Client Services"};
SpreadsheetApp.flush();
//MailApp.sendEmail(projectManager, subject, html, optAdvancedArgs);
Please let me know if you need more information or clarification. Any help would be much appreciated. Thank you.
Edit 12/4/14 #1 Couple of things I want to happen. Whenever a cell is edited with the range of columns E, F. G and H, a note is added with the active users email address and time stamp. In my previous code, I was able to achieve this with the below code.
var user = Session.getActiveUser().getEmail();
var timestamp = Utilities.formatDate(new Date(), "America/New_York", "MMMM dd, yyyy HH:mm");
var comments1 = ss1.getRange(startRow + i, 5).getNote();
comments1 = comments1 + "Request Sent:\n" + user + "\n" + timestamp + "\n";
var comments = ss1.getRange(startRow + i, 8).getNote();
comments = comments + "EMAIL_SENT:\n" + timestamp + "\n";
var setdata = ss1.getRange(startRow + i, 8).setValue(EMAIL_SENT).setNote(comments);
var team1Update = ss1.getRange(startRow + i, 5).setNote(comments1);
comments1 set the note for the drop selections in column E, F, G and comments sets the note for column H. When I place the above code in the current onEdit(e) script, nothing happens. Any help how I can implement this.
Edit 12/4/14 #2 Also on the previous code, I was able to send emails based on the drop down menu selections in columns E, F, G. So if users selects Request Sent a email is sent. This is repeated for all the drop down menu selections in columns E, F, G. Any help on how I can implement the MailApp for each selection.
var html = projectName+" "+projectID+" "+projectManager+" "+dueDate;
var optAdvancedArgs = {replyTo: replyTo, cc: user, name: "Venue Client Services"};
MailApp.sendEmail(projectManager, subject, html, optAdvancedArgs);
This would be a good application for an Installable Edit Trigger function. See Google Sheets Events as well.
Note that you must use an installable trigger which will run as the document owner, not the simple onEdit() trigger that runs as an anonymous user, because sending an email requires user authorization.
Something like this (tested):
function installableOnEdit( e ) {
if (!e) throw new Error( "Need event parameter." ); // see http://stackoverflow.com/a/16089067
var changedCell = e.range;
var row = e.range.getRow();
var col = e.range.getColumn();
var sheet = e.range.getSheet();
var headers = sheet.getDataRange().getValues()[0]; // Get header row 1
var statusCol = headers.indexOf('Reminder Email Status')+1;
// Build an array of "rows we care about". If the headers change, this needs to be updated
var teamCols = [ headers.indexOf('Team 1 (PM)')+1,
headers.indexOf('Team 2 (Colombo)')+1,
headers.indexOf('Team 3 (VCS)')+1 ];
if (teamCols.indexOf(col) == -1) return; // Exit if cell outside of the team columns
var status = e.value.toUpperCase().replace(/ /g,"_"); // Change "Request Sent" to "REQUEST_SENT"
sheet.getRange(row,statusCol).setValue(status);
//--------- Put your emailing code here, or call a function
}
// Read user's current cell, and feed to installableOnEdit() as an event.
// see http://stackoverflow.com/a/16089067
function test_installableOnEdit() {
installableOnEdit({
user : Session.getActiveUser().getEmail(),
source : SpreadsheetApp.getActiveSpreadsheet(),
range : SpreadsheetApp.getActiveSpreadsheet().getActiveCell(),
value : SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue(),
authMode : "LIMITED"
});
}
这篇关于For循环和if语句的多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
