function sendBirthdayAnniversaryEmails() {
try {
const sheet = SpreadsheetApp.getActive().getSheetByName("Data");
if (!sheet) {
console.error("Sheet 'Data' tidak ditemukan");
return;
}
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
console.log("Tidak ada data yang perlu diproses");
return;
}
const data = sheet.getRange("A2:G" + lastRow).getValues();
const today = new Date();
const todayStr = Utilities.formatDate(today, "GMT+7", "dd/MM");
console.log("Memproses tanggal:", todayStr);
console.log("Total data:", data.length);
data.forEach((row, index) => {
try {
const rowNum = index + 2;
const [nama, email, tglLahir, tglMulaiKerja, jenisUcapan, terakhirDikirim, status] = row;
console.log(`\nMemproses baris ${rowNum}: ${nama} | ${email} | ${jenisUcapan}`);
console.log(`Tanggal Lahir: ${tglLahir} | Mulai Kerja: ${tglMulaiKerja}`);
// Validasi dasar
if (!email || !email.includes("@")) {
console.log(`⛔ Email tidak valid untuk ${nama}`);
return;
}
// Konversi tanggal jika berupa string
const birthDate = convertToDate(tglLahir);
const workDate = convertToDate(tglMulaiKerja);
// Proses Ulang Tahun
if ((jenisUcapan.includes("Birthday") || jenisUcapan.includes("Ulang Tahun")) && birthDate) {
const birthDateStr = Utilities.formatDate(birthDate, "GMT+7", "dd/MM");
console.log(`Ulang Tahun: ${birthDateStr}`);
if (birthDateStr === todayStr) {
console.log(`🎉 Mengirim ucapan ulang tahun ke ${nama}`);
sendEmail(nama, email, "birthday");
updateLog(sheet, rowNum, today, "Ulang Tahun Terkirim");
}
}
// Proses Anniversary
if (jenisUcapan.includes("Anniversary") && workDate) {
const annivDateStr = Utilities.formatDate(workDate, "GMT+7", "dd/MM");
console.log(`Anniversary: ${annivDateStr}`);
if (annivDateStr === todayStr) {
const years = today.getFullYear() - workDate.getFullYear();
console.log(`✨ Mengirim ucapan anniversary ke ${nama} (${years} tahun)`);
sendEmail(nama, email, "anniversary", years);
updateLog(sheet, rowNum, today, "Anniversary Terkirim");
}
}
} catch (error) {
console.error(`❌ Error pada baris ${index + 2}:`, error);
}
});
} catch (error) {
console.error("❌ Error utama:", error);
}
}
// Fungsi pembantu untuk konversi tanggal
function convertToDate(dateValue) {
if (dateValue instanceof Date) {
return dateValue;
}
if (typeof dateValue === 'string') {
// Coba parsing format dd/MM/yyyy
const parts = dateValue.split('/');
if (parts.length === 3) {
return new Date(parts[2], parts[1] - 1, parts[0]);
}
}
console.log("Format tanggal tidak valid:", dateValue);
return null;
}
function sendEmail(nama, email, type, years = null) {
try {
if (!nama || !email || !type) {
throw new Error("Parameter tidak lengkap");
}
const subject = type === "birthday"
? `Selamat Ulang Tahun, ${nama}! 🎉`
: `Selamat ${years} Tahun Bergabung, ${nama}! ✨`;
const body = `
<div style="font-family: Arial, sans-serif; padding: 20px; max-width: 600px; margin: 0 auto; border: 1px solid #e0e0e0; border-radius: 10px;">
<h2 style="color: #4285F4;">${subject}</h2>
<p style="font-size: 16px;">
${type === "birthday"
? `Dear ${nama},<br><br>Semoga hari ulang tahunmu dipenuhi kebahagiaan dan kesuksesan! 🎂`
: `Dear ${nama},<br><br>Selamat atas ${years} tahun perjalanan bersama kami! ✨`}
</p>
<p style="font-size: 14px; color: #666;">Best regards,<br>Tim HR</p>
</div>
`;
Utilities.sleep(1500);
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: body,
noReply: true,
name: "HR Department"
});
console.log(`✅ Email berhasil dikirim ke ${email}`);
} catch (error) {
console.error(`❌ GAGAL mengirim email ke ${email}:`, error.toString());
}
}
function updateLog(sheet, row, date, statusText) { try {
sheet.getRange(row, 6).setValue(date); // Kolom Terakhir Dikirim
sheet.getRange(row, 7).setValue(statusText); // Kolom Status
SpreadsheetApp.flush();
console.log(`📝 Log updated untuk baris ${row}`);
} catch (error) {
console.error("❌ Gagal update log:", error);
}
}
function testSystem() {
console.log("🛠️ Testing sistem...");
console.log("Kuota email tersedia:", MailApp.getRemainingDailyQuota());
const testDate = new Date();
console.log("Test format date:", Utilities.formatDate(testDate, "GMT+7", "dd/MM"));
sendEmail("TEST", Session.getActiveUser().getEmail(), "birthday");
}
function setupDailyTrigger() {
// Hapus trigger lama jika ada
ScriptApp.getProjectTriggers().forEach(trigger => {
if (trigger.getHandlerFunction() === "sendBirthdayAnniversaryEmails") {
ScriptApp.deleteTrigger(trigger);
}
});
// Buat trigger baru
ScriptApp.newTrigger("sendBirthdayAnniversaryEmails")
.timeBased()
.atHour(9) // Jam 9 pagi
.everyDays(1) // Setiap hari
.inTimezone("Asia/Jakarta") // Sesuaikan timezone
.create();
console.log("✅ Trigger harian berhasil dipasang");
}
Post a Comment