I want the sheet "Form Responses 1" to show the date in column DATE B2:B. The date is being shown but it is being shown with minus 1 date of B2:B. I want the date not to be minus B2:B.
function sendEmails() {
var formResponsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses 1");
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var data1 = formResponsesSheet.getDataRange().getValues();
var emailRange = sheet2.getRange("A2:A");
var data2 = emailRange.getValues();
var today = new Date();
var subject = "RECEIVED POD";
var message = `
<!DOCTYPE html>
<html>
<head>
<title>Receiving POD Updates</title>
</head>
<body>
<div>
<h1>Hi,</h1>
<p>I'm informing you about the following updates for records with the current date:</p>
`;
for (var i = 1; i < data1.length; i++) {
var date = new Date(data1[i][0]); // Assuming Date is in the first column (A column)
// Check if the date in column A matches the current date
if (date.toDateString() === today.toDateString()) {
var formattedDate = Utilities.formatDate(new Date(data1[i][1]), "GMT+0", "dd-MM-yyyy"); // Format date as needed
message += `
<ul>
<li>Timestamp: ${data1[i][0]}</li>
<li>Date: ${formattedDate}</li>
<li>Courier Name: ${data1[i][2]}</li>
<li>Scan POD: ${data1[i][3]}</li>
<li>Excel POD: ${data1[i][4]}</li>
<li>Received QTY: ${data1[i][5]}</li>
<li>Short Qty: ${data1[i][6]}</li>
<li>Receiving Person: ${data1[i][7]}</li>
</ul>
`;
}
}
message += `
</div>
</body>
</html>
`;
for (var k = 0; k < data2.length; k++) {
var email = data2[k][0];
if (isValidEmail(email)) {
MailApp.sendEmail(email, subject, '', {
htmlBody: message
});
} else {
console.log("Invalid email address: " + email);
}
}
}
function isValidEmail(email) {
var emailRegex = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
return emailRegex.test(email);
}
LinkedIn
Twitter
