How to send a conditional email based on the value of a cell from a form response?

Question

I have a formula that calculates a number based on the response from a google form. Depending on what this number I want to send an email using details from the from as well as a pre typed email in another cell.

In Col1 is a time stamp, in col14 is an employee start date. My formula in Col33 works out how many days they have been employed at the time of submitting the form.

I want to send an email to the person if the number of days is less than 182.

I have an email pre typed out and can place this anywhere. At the moment I have it in all cells in col36. The email address will be in column32.

I have tried a number of different codes and none of them are sending the email no matter what the trigger I have set up is. I have very basic knowledge on apps script so my current code might be completely wrong, but it should show roughly what I'm getting at.

function sendEmail() {
    var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
    for (i in values.length) {
        var data = values[i][33];
        var emailAddress = values[i][32];
        var message = values[i][36];
        if (data < 182); {
            MailApp.sendEmail(emailAddress, "Flexible Working Request", message);
        }
    }
}

---

The current results have just been deleting the data in col33, Col34 & Col36 on the new form response row only.

Sorry if this question has been answered elsewhere, any other answer I found to similar issues I could not get to work.

Answer 1

I got someone who is much better at google apps script at work to give me a hand

It is to do with google forms pushing down formulas to the side

So we had to move the formula calculating the number of days to another sheet and then used this formula which worked

function sendEmailv2() { 
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form responses 
 1'); 
 var scrip = Session.getScriptTimeZone(); 

 var date = sheet.getRange(sheet.getLastRow(),14).getValue();
 var sub = sheet.getRange(sheet.getLastRow(),1).getValue();
 Logger.log(date);
 var fortmat = Utilities.formatDate(new Date(date), scrip, "dd/MM/yyyy");

 var Subfortmat = Utilities.formatDate(new Date(sub), scrip, "dd/MM/yyyy");
 var emailAddress = sheet.getRange(sheet.getLastRow(),32).getValue();

 var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet4'); 
 var message = sheet2.getRange(1,1).getValue();
 var days = sheet2.getRange(sheet2.getLastRow(),2).getValue();


 if (days<182){

 MailApp.sendEmail(emailAddress, "Flexible Working Request", message,{noReply:true});
  }

}

Thanks!