var id = "id dự án";
try {
var ss = SpreadsheetApp.openById(id);
}
catch(err) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
};
var sheet = ss.getSheetId(652145055); //ID trang tính
var trigger1 = 'sendmail1';
function createTimeDrivenTriggers() {
// Trigger every 7 giờ sáng thứ 2, 3, 4, 5, 6, 7, CN
var n = trigger1;
var h = 7;
clearTrigger(n);
createTriggerEveryDay(h, ScriptApp.WeekDay.MONDAY, n);
createTriggerEveryDay(h, ScriptApp.WeekDay.TUESDAY, n);
createTriggerEveryDay(h, ScriptApp.WeekDay.WEDNESDAY, n);
createTriggerEveryDay(h, ScriptApp.WeekDay.THURSDAY, n);
createTriggerEveryDay(h, ScriptApp.WeekDay.FRIDAY, n);
createTriggerEveryDay(h, ScriptApp.WeekDay.SATURDAY, n);
createTriggerEveryDay(h, ScriptApp.WeekDay.SUNDAY, n);
}
//====================================================================
function createTriggerEveryDay(time, wd, trigger) {
ScriptApp.newTrigger(trigger).timeBased().onWeekDay(wd).atHour(time).create();
}
//====================================================================
function clearTrigger(){
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
var hf = triggers[i].getHandlerFunction();
if (hf == trigger1)ScriptApp.deleteTrigger(triggers[i]);
}
}
//====================================================================
function clearAllTrigger(){
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
}
//====================================================================
//====================================================================
function ResetTriggers(){
clearAllTrigger();
createTimeDrivenTriggers();
newTrigger();
}
//====================================================================
function isValidDate(d) {
if ( Object.prototype.toString.call(d) !== "[object Date]" )
return false;
return !isNaN(d.getTime());
}
//====================================================================
// Test if value is a date and if so format
// otherwise, reflect input variable back as-is.
function isDate(sDate) {
if (isValidDate(sDate)) {
sDate = Utilities.formatDate(new Date(sDate), "Asia/Saigon", "MM/dd/yyyy");
}
return sDate;
}
function sendEmails() {
var lr = sheet.getLastRow();
var cells = sheet.getRange("A3:J" + lr.toString());
var data = cells.getValues();
var now = new Date();
for (var i = 1; i < lr; i++) {
(function(i) {
var emailAddress = data[i][9];
var date = data[i][6];
if (emailAddress != null){
if (isValidDate(date)){
var b30 = date.setMonth(date.getMonth()+1);
if (date >= now && b30 <= now){
var name = data[i][7];
var message = 'Gửi ' + name + '\n\n';
for (var c = 1; c < cells.getNumColumns - 1; c++) {
message = message + data[0][c] + ': ' + data[i][c] + '\n';
}
var subject = 'Nhắc việc ngày: ' + now.toString();
MailApp.sendEmail(emailAddress, subject, message);
}
}
}
})(i);
}
}
