function reminder() {
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var data = sh.getDataRange().getValues()
var d = new Date().getTime();
for (var i=1;i<data.length;i++){
if (data[i][4]<=new Date(d+7*24*60*60*1000) && data[i][4]>=new Date(d+5*24*60*60*1000) && data[i][6]==''){
MailApp.sendEmail({to:data[i][3],
subject: 'Reminder Process Update Required in 1 week',
htmlBody: 'Hello '+data[i][1]+' The page for '+data[i][0]+' is due to review on '+data[i][4]+' Please review the content and contact the team before its due date if amendments are required.'
})
sh.getRange(i+1,7).setValue('sent')
}
else if (data[i][4]<=new Date(d+30*24*60*60*1000) && data[i][4]>=new Date(d+28*24*60*60*1000) && data[i][5]==''){
MailApp.sendEmail({to:data[i][3],
subject: 'Reminder Process Update Required in 1 month',
htmlBody: 'Hello '+data[i][1]+' The page for '+data[i][0]+' is due to review on '+data[i][4]+' Please review the content and contact the team before its due date if amendments are required.'
})
sh.getRange(i+1,6).setValue('sent')
}
}
}
How can I change the date formatting in my script, It is currently generating an automated email however the date is being formatted and showing the Time as well as (Eastern Standard Time) I would like it to only show the date without the time included.
You can create a format function like
function formatDate(d) {
return `${String(d.getDate()).padStart(2, '0')}/${String(d.getMonth() + 1).padStart(2, '0')}/${d.getFullYear()}`;
}
Example:
function formatDate(d) {
return `${String(d.getDate()).padStart(2, '0')}/${String(d.getMonth() + 1).padStart(2, '0')}/${d.getFullYear()}`;
}
const i = 0;
const data = [['Client', 'Recipient',,, new Date()]];
console.log('Hello '+data[i][1]+' The page for '+data[i][0]+' is due to review on '+formatDate(data[i][4])+' Please review the content and contact the team before its due date if amendments are required.');call this function where you need formatted date.
function formatDate(date) {
var d = new Date(date),
month = '' + (d.getMonth() + 1),
day = '' + d.getDate(),
year = d.getFullYear();
if (month.length < 2)
month = '0' + month;
if (day.length < 2)
day = '0' + day;
return [ day, month, year].join('/');
}
While there are other ways to format a date, a more direct, Apps Script-based way to do this is using Utilities.formatDate:
const formattedDate = Utilities.formatDate(data[i][4], Session.getTimeZone(), "dd/MM/yyyy");
Just add this line right before sending the email and replace data[i][4] with formattedDate in the htmlBody.