用于多个条件的 For 循环和 if 语句 [英] For loop and if statement for multiple conditions

问题描述

我对 if 条件感到头疼.我会说清楚工作表应该如何工作.

I am having a head-scratching time with the if conditions. I will make it clear how the sheet is supposed to work.

当用户从 E 列、F 列或 G 列的下拉菜单中选择一个值时，H 列将更新一个值.

When users select a value from the drop-down menus in column E, column F, or column G. Column H will be updated will a value.

它应该如何工作:

团队 1 - E 列

团队 1 将从下拉列表中选择请求已发送".该脚本将执行 H 列中的设置值REQUEST_SENT".

Team 1 will select "Request Sent" from the drop-down list. The script will execute setvalue "REQUEST_SENT" in column H.

团队 2 - F 列

团队 2 将选择收到请求".该脚本将执行 H 列中的设置值REQUEST_RECEIVED".

Team 2 will select "Request Received". The script will execute setvalue "REQUEST_RECEIVED" in column H.

团队 2 将选择请求上传".该脚本将执行 H 列中的设置值REQUEST_Uploading".

Team 2 will select "Request Uploading". The script will execute setvalue "REQUEST_Uploading" in column H.

Team 2 将选择上传完成".该脚本将执行 H 列中的设置值UPLOAD_COMPLETED".

Team 2 will select "Upload Completed". The script will execute setvalue "UPLOAD_COMPLETED" in column H.

第 3 组 - G 列

团队 2 将选择请求转移".该脚本将执行 H 列中的设置值REQUEST_TRANSFERRING".

Team 2 will select "Request Transferring". The script will execute setvalue "REQUEST_TRANSFERRING" in column H.

第 2 小组将选择审查中的请求".该脚本将执行 H 列中的设置值REQUEST_IN_REVIEW".

Team 2 will select "Request in Review". The script will execute setvalue "REQUEST_IN_REVIEW" in column H.

团队 2 将选择请求审核已完成".该脚本将执行 H 列中的设置值REVIEW_COMPLETED".

Team 2 will select "Request Review Completed". The script will execute setvalue "REVIEW_COMPLETED" in column H.

团队 1 - E 列

团队 1 将选择请求已批准"或从下拉列表中选择请求已拒绝".该脚本将执行 H 列中的设置值REQUEST_APPROVED"或REQUEST_REJECTED".

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.

团队 2 - F 列

如果团队 1 选择了请求已批准".团队 2 将选择正在进行中的燃烧".该脚本将执行 H 列中的设置值BURN_IN_PROGRESS".

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 将选择Burn Completed".该脚本将执行 H 列中的设置值BURN_COMPLETED".

Team 2 will select "Burn Completed". The script will execute setvalue "BURN_COMPLETED" in column H.

团队 1 - E 列

Team 1 将选择Request Shipped"或从下拉列表中选择Burn Rejected".该脚本将执行 H 列中的设置值REQUEST_SHIPPED"或BURN_REJECTED".

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.

下面的脚本执行我想要的，但是当在 E、F 或 G 列中选择每个值时，H 列值会根据 E 列或 F 列更改为不同的值.

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.

这是 Google 电子表格

代码在这里

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];
 }

这里我需要放if语句和条件来更新H列

if (condition =="" && (condition !=)){
}

如果条件为真，下面的代码也会执行.

   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:
" + 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);

如果您需要更多信息或说明，请告诉我.任何帮助将非常感激.谢谢.

Please let me know if you need more information or clarification. Any help would be much appreciated. Thank you.

编辑 12/4/14 #1我想要发生的几件事.每当使用 E、F.G 和 H 列范围编辑单元格时，都会添加带有活动用户电子邮件地址和时间戳的注释.在我之前的代码中，我能够使用以下代码实现这一点.

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 user's email address and timestamp. 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:
" + 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) 脚本时，没有任何反应.关于我如何实现这一点的任何帮助.

comments1 set the note for the drop selections in column E, F, G and comments set the note for column H. When I place the above code in the current onEdit(e) script, nothing happens. Any help on how I can implement this.

编辑 12/4/14 #2同样在前面的代码中，我能够根据 E、F、G 列中的下拉菜单选项发送电子邮件.因此，如果用户选择请求已发送"，则会发送电子邮件.对 E、F、G 列中的所有下拉菜单选项重复此操作.有关如何为每个选项实施 MailApp 的任何帮助.

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 select Request Sent an 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);

推荐答案

对于 可安装的编辑触发器 功能.另请参阅 Google 表格事件.

This would be a good application for an Installable Edit Trigger function. See Google Sheets Events as well.

请注意，您必须使用 可安装 触发器，它将作为文档所有者运行，而不是作为运行的 简单 onEdit() 触发器匿名用户，因为发送电子邮件需要用户授权.

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屋！