CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-forms
Ben Quigley
Ben Quigley

Reputation: 737

Emails sometimes not being sent (if first subroutine has to run, second subroutine is skipped)

My code is working reasonably well to send emails. It is supposed to email the user who submits a form.

The problem is that for certain rows, when it needs to run an automatic-approval subroutine (SECTION A in the code comments), it then fails to run the next subroutine (SECTION B in the code comments, which is the one that is supposed to actually send the email).

If I run the whole script twice, then it sends the email. Also, if you approve the rows manually, then it sends the email. However, if the script approves the row, then it does not send the email in the same run.

Google Apps Script code:

function sendEmails2() {
  //trying to adopt code from this tutorial https://developers.google.com/apps-script/articles/sending_emails
  //-believe there is an issue with where I am declaring getRange to work-
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var lastRow = 40;   // Last row of data to process
  // Fetch the range of cells A1:B10002
  var dataRange = sheet.getRange(startRow, 1, lastRow, 11)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var partTimeApproved = row[0]
    var userID = row[2]
    var emailAddress = row[7];  // sometimes this shit is like "invalid email address found" but it sends it anyway
    var isEmailSent = row[9];
    var partTimeApprovedAutomatically = row[10]
    var cc = 'list of CCs'
    //this next section should automatically approve rows submitted by approved users
//  SECTION A
    if (userID == "user 1" || userID == "user 2" || userID == "user 3") {
      if (partTimeApproved != 'Y') {
    sheet.getRange(startRow+parseInt(i, 10), 1).setValue('Y')
    sheet.getRange(startRow+parseInt(i, 10), 11).setValue('Y')
    }
      {}
    }
    {} 
// SECTION B
    var nowIsPartTimeApproved = row[0] // I wrote this in a vain attempt at getting the system to process section A and B at the same time
    if (nowIsPartTimeApproved == 'Y') { // prevents sending emails to non-approved rows. 
    if (isEmailSent == '') {  // Prevents sending duplicates and sending emails to non-approved rows
      var options = {
        cc: cc
      }
      var subject = "Email Subject Test";
      var message = "Email body test";    // Standard string OR sheet.getRange(1,13) ;
      MailApp.sendEmail(emailAddress, subject, message, options)
      sheet.getRange(startRow + parseInt(i, 10), 10).setValue('emailSent'); //trying this from Stackoverflow's epascarello
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
  }
}

Any thoughts?

Upvotes: 0

Views: 63

Answers (1)

KRR
KRR

Reputation: 4947

In section A, the "partTimeApproved" and in Section B, the "nowIsPartTimeApproved" is referring to same cell value. So it will only satisfy one of the if conditions. So if section A is executed, it will not execute Section B.

You have to either change the cell value(row[0]) to 'Y' in Section A or set the value for variable "nowIsPartTimeApproved" to 'Y' in Section A itself.

Hope that helps!

Upvotes: 0

Related Questions