I am new to the scripting. I have a written a function to check the difference between the present time and the time recorded in the spreadsheet earlier. If the difference is more than 4 hours, it needs to send a mail to the concerned person.
When I try to execute this code, it only runs the first if condition successfully and ends overs there. The second nested if statement is not executed at all. All the help and necessary changes required in the code would be highly appreciated.
function warningemail() {
var check_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Request Form Responses");
var findColJ = check_sheet.getRange('J:J').getValues();
var findColL = check_sheet.getRange('L:L').getValues();
var findColE = check_sheet.getRange('E:E').getValues();
var findColI = check_sheet.getRange('I:I').getValues();
for (var y = 0, lengths = findColJ.length; y < lengths; y++) {
if (findColJ[y][0] == "In Progress") {
var dt1 = new Date();
var time1 = new Date(dt1.getTime());
var prTime = Utilities.formatDate(time1, "GMT+0530", "MM/dd/yyyy HH:mm:ss");
var dt2 = new Date(findColL[y][0]);
var time2 = new Date(dt2.getTime());
var etaTime = Utilities.formatDate(time2, "GMT+0530", "MM/dd/yyyy HH:mm:ss");
Logger.log(prTime); //05/13/2017 12:44:09
Logger.log(etaTime); //05/13/2017 02:26:00
if (prTime-etaTime > 3600000*4) { // Need to check the diff between current time and prTime
var subject = "Peer Review Request Unattended| PCID: " + findColE[y][0];
var body = "";
var htmlmail = "<p>Hi,</p>";
htmlmail += "<p>*******************This is a system generated email. Do not reply to this mail.*******************</p>";
GmailApp.sendEmail(findColI, subject,body,{ htmlBody:htmlmail });
}
}
}
}