google apps script - For loop and if statement for multiple conditions

Question

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

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.

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:
" + 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.

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 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.

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);

Answer 1

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"
  });
}