Tự Động Gửi Email Nhắc Nhở Thanh Toán Từ Google Sheets [Apps Script]

Tại Sao Cần Tự Động Nhắc Nhở Thanh Toán?

Quản lý công nợ là một trong những bài toán đau đầu nhất của mọi doanh nghiệp. Theo thống kê, trung bình 30-40% hóa đơn bị thanh toán trễ hạn, không phải vì khách hàng không muốn trả, mà đơn giản vì họ... quên. Một hệ thống nhắc nhở tự động có thể giúp bạn tăng tỷ lệ thu hồi công nợ lên 30-50% mà không cần gọi điện hay gửi email thủ công.

Bước 1: Chuẩn Bị Google Sheets Theo Dõi Công Nợ

Trước khi viết script, bạn cần thiết lập bảng tính với cấu trúc chuẩn. Tạo một Google Sheets mới với 2 sheet: "Công nợ" (dữ liệu chính) và "Log" (lịch sử gửi email).

Sheet "Công nợ" - Cấu trúc bảng

Sheet "Log" - Lịch sử gửi email

Bước 2: Script Cơ Bản - Gửi Email Text Đơn Giản

Chúng ta sẽ bắt đầu với phiên bản đơn giản nhất: quét danh sách công nợ, tìm hóa đơn quá hạn, và gửi email nhắc nhở dạng text thuần. Sau đó sẽ nâng cấp dần.

Mở Apps Script Editor

/**
 * Script cơ bản: Gửi email nhắc thanh toán cho hóa đơn quá hạn
 * Sheet: "Công nợ" - Cột A:J
 */
function sendBasicReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Công nợ");
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0); // Reset về đầu ngày

  var sentCount = 0;
  var errorCount = 0;

  // Bắt đầu từ dòng 2 (bỏ header)
  for (var i = 1; i < data.length; i++) {
    var maKH = data[i][0];        // Cột A: Mã KH
    var tenKH = data[i][1];       // Cột B: Tên khách hàng
    var email = data[i][2];       // Cột C: Email
    var soHD = data[i][3];        // Cột D: Số hóa đơn
    var soTien = data[i][4];      // Cột E: Số tiền
    var ngayDaoHan = new Date(data[i][5]); // Cột F: Ngày đáo hạn
    var trangThai = data[i][6];   // Cột G: Trạng thái

    // Bỏ qua nếu đã thanh toán hoặc không có email
    if (trangThai === "Đã thanh toán" || !email) continue;

    // Tính số ngày chênh lệch
    ngayDaoHan.setHours(0, 0, 0, 0);
    var diffDays = Math.floor((ngayDaoHan - today) / (1000 * 60 * 60 * 24));

    // Chỉ gửi nếu quá hạn (diffDays < 0)
    if (diffDays < 0) {
      try {
        var subject = "Nhắc nhở thanh toán hóa đơn " + soHD;
        var body = "Kính gửi " + tenKH + ",\n\n" +
          "Chúng tôi xin nhắc nhở về hóa đơn " + soHD +
          " với số tiền " + formatCurrency(soTien) +
          " đã quá hạn thanh toán " + Math.abs(diffDays) + " ngày.\n\n" +
          "Vui lòng thanh toán sớm nhất có thể.\n\n" +
          "Trân trọng,\n" +
          "Phòng Kế toán";

        GmailApp.sendEmail(email, subject, body);

        // Cập nhật lần nhắc cuối và số lần nhắc
        sheet.getRange(i + 1, 8).setValue(new Date()); // Cột H
        var soLanNhac = data[i][8] || 0;
        sheet.getRange(i + 1, 9).setValue(soLanNhac + 1); // Cột I

        sentCount++;
      } catch (e) {
        Logger.log("Lỗi gửi email cho " + tenKH + ": " + e.message);
        errorCount++;
      }
    }
  }

  Logger.log("Đã gửi: " + sentCount + " email. Lỗi: " + errorCount);
}

// Hàm format số tiền VNĐ
function formatCurrency(amount) {
  return new Intl.NumberFormat('vi-VN').format(amount) + " VNĐ";
}

Bước 3: Script Nâng Cao - 3 Mức Nhắc Nhở Thông Minh

Phiên bản nâng cao sẽ chia thành 3 mức nhắc nhở với tone giọng và nội dung khác nhau, phù hợp với từng tình huống. Đây là cách các công ty lớn xử lý thu hồi công nợ:

/**
 * Script nâng cao: 3 mức nhắc nhở thanh toán
 * - Mức 1: Trước hạn 3 ngày (nhẹ nhàng)
 * - Mức 2: Đúng ngày đáo hạn (chuyên nghiệp)
 * - Mức 3: Quá hạn (nghiêm túc + CC manager)
 */

// ===== CẤU HÌNH =====
var CONFIG = {
  SHEET_NAME: "Công nợ",
  LOG_SHEET: "Log",
  COMPANY_NAME: "Công Ty TNHH ABC",
  COMPANY_PHONE: "028 1234 5678",
  COMPANY_EMAIL: "ketoan@congtyabc.vn",
  MANAGER_EMAIL: "manager@congtyabc.vn",  // CC khi quá hạn
  DAYS_BEFORE: 3,      // Nhắc trước bao nhiêu ngày
  DAYS_OVERDUE_CC: 0,  // Quá hạn bao nhiêu ngày thì CC manager
  BANK_NAME: "Vietcombank",
  BANK_ACCOUNT: "001 100 1234567",
  BANK_HOLDER: "CONG TY TNHH ABC"
};

function sendAdvancedReminders() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  var logSheet = ss.getSheetByName(CONFIG.LOG_SHEET);

  // Tạo sheet Log nếu chưa có
  if (!logSheet) {
    logSheet = ss.insertSheet(CONFIG.LOG_SHEET);
    logSheet.appendRow([
      "Thời gian gửi", "Mã KH", "Email",
      "Số hóa đơn", "Mức nhắc", "Trạng thái gửi"
    ]);
    // Format header
    logSheet.getRange(1, 1, 1, 6).setFontWeight("bold")
      .setBackground("#E8EAF6");
  }

  var data = sheet.getDataRange().getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  var stats = { sent: 0, skipped: 0, errors: 0 };

  for (var i = 1; i < data.length; i++) {
    var row = {
      maKH: data[i][0],
      tenKH: data[i][1],
      email: data[i][2],
      soHD: data[i][3],
      soTien: data[i][4],
      ngayDaoHan: new Date(data[i][5]),
      trangThai: data[i][6],
      lanNhacCuoi: data[i][7],
      soLanNhac: data[i][8] || 0
    };

    // Bỏ qua: đã thanh toán, không có email, hoặc thiếu dữ liệu
    if (row.trangThai === "Đã thanh toán" || !row.email || !row.soHD) {
      stats.skipped++;
      continue;
    }

    // Tránh gửi trùng: nếu đã nhắc hôm nay thì bỏ qua
    if (row.lanNhacCuoi) {
      var lastRemind = new Date(row.lanNhacCuoi);
      lastRemind.setHours(0, 0, 0, 0);
      if (lastRemind.getTime() === today.getTime()) {
        stats.skipped++;
        continue;
      }
    }

    row.ngayDaoHan.setHours(0, 0, 0, 0);
    var diffDays = Math.floor(
      (row.ngayDaoHan - today) / (1000 * 60 * 60 * 24)
    );

    var reminderLevel = getReminderLevel(diffDays);
    if (!reminderLevel) {
      stats.skipped++;
      continue;
    }

    // Gửi email
    var result = sendReminderEmail(row, reminderLevel, diffDays);

    // Ghi log
    logSheet.appendRow([
      new Date(),
      row.maKH,
      row.email,
      row.soHD,
      reminderLevel.name,
      result.success ? "Thành công" : "Lỗi: " + result.error
    ]);

    // Cập nhật sheet chính
    if (result.success) {
      sheet.getRange(i + 1, 8).setValue(new Date());
      sheet.getRange(i + 1, 9).setValue(row.soLanNhac + 1);
      if (diffDays < 0) {
        sheet.getRange(i + 1, 7).setValue("Quá hạn");
      }
      stats.sent++;
    } else {
      stats.errors++;
    }
  }

  // Log tổng kết
  Logger.log("=== KẾT QUẢ GỬI EMAIL ===");
  Logger.log("Đã gửi: " + stats.sent);
  Logger.log("Bỏ qua: " + stats.skipped);
  Logger.log("Lỗi: " + stats.errors);
}

/**
 * Xác định mức nhắc nhở dựa trên số ngày
 */
function getReminderLevel(diffDays) {
  if (diffDays > 0 && diffDays <= CONFIG.DAYS_BEFORE) {
    return {
      level: 1,
      name: "Trước hạn",
      color: "#059669",       // Xanh lá
      bgColor: "#ECFDF5",
      subject: "Nhắc nhở: Hóa đơn sắp đến hạn thanh toán",
      tone: "friendly"
    };
  } else if (diffDays === 0) {
    return {
      level: 2,
      name: "Đúng hạn",
      color: "#2563EB",       // Xanh dương
      bgColor: "#EFF6FF",
      subject: "Hóa đơn đến hạn thanh toán hôm nay",
      tone: "professional"
    };
  } else if (diffDays < 0) {
    return {
      level: 3,
      name: "Quá hạn",
      color: "#DC2626",       // Đỏ
      bgColor: "#FEF2F2",
      subject: "KHẨN: Hóa đơn quá hạn thanh toán",
      tone: "serious"
    };
  }
  return null; // Chưa đến thời điểm nhắc
}

/**
 * Gửi email nhắc nhở với HTML template
 */
function sendReminderEmail(row, level, diffDays) {
  try {
    var htmlBody = buildEmailHTML(row, level, diffDays);
    var options = {
      htmlBody: htmlBody,
      name: CONFIG.COMPANY_NAME + " - Phòng Kế toán"
    };

    // CC manager nếu quá hạn
    if (level.level === 3 && Math.abs(diffDays) >= CONFIG.DAYS_OVERDUE_CC) {
      options.cc = CONFIG.MANAGER_EMAIL;
    }

    GmailApp.sendEmail(
      row.email,
      level.subject + " - " + row.soHD,
      "Vui lòng xem email này trên trình duyệt hỗ trợ HTML.",
      options
    );

    return { success: true };
  } catch (e) {
    Logger.log("Lỗi gửi email " + row.email + ": " + e.message);
    return { success: false, error: e.message };
  }
}

Bước 4: Template Email HTML Chuyên Nghiệp

Email text thuần trông rất thiếu chuyên nghiệp. Chúng ta sẽ tạo 3 template HTML tương ứng với 3 mức nhắc, với thiết kế đẹp mắt và đầy đủ thông tin thanh toán.

Hàm tạo email HTML

/**
 * Tạo nội dung email HTML dựa trên mức nhắc nhở
 */
function buildEmailHTML(row, level, diffDays) {
  var dueDate = Utilities.formatDate(
    row.ngayDaoHan, "Asia/Ho_Chi_Minh", "dd/MM/yyyy"
  );
  var amount = formatCurrency(row.soTien);
  var greeting = getGreeting(level, row.tenKH);
  var message = getMessage(level, diffDays, row.soHD, amount, dueDate);
  var footer = getFooterMessage(level);

  var html = '<!DOCTYPE html>' +
    '<html><head><meta charset="utf-8"></head>' +
    '<body style="margin:0;padding:0;font-family:Arial,sans-serif;' +
    'background-color:#f5f5f5;">' +
    '<table width="100%" cellpadding="0" cellspacing="0" ' +
    'style="max-width:600px;margin:20px auto;background:#fff;' +
    'border-radius:12px;overflow:hidden;' +
    'box-shadow:0 2px 8px rgba(0,0,0,0.1);">' +

    // Header với màu theo mức nhắc
    '<tr><td style="background:' + level.color + ';' +
    'padding:24px 30px;text-align:center;">' +
    '<h1 style="color:#fff;margin:0;font-size:20px;">' +
    CONFIG.COMPANY_NAME + '</h1>' +
    '<p style="color:rgba(255,255,255,0.85);margin:5px 0 0;' +
    'font-size:14px;">Thông Báo Thanh Toán</p>' +
    '</td></tr>' +

    // Nội dung chính
    '<tr><td style="padding:30px;">' +
    greeting + message +

    // Bảng thông tin hóa đơn
    '<table width="100%" cellpadding="0" cellspacing="0" ' +
    'style="margin:20px 0;border:1px solid #e5e7eb;' +
    'border-radius:8px;overflow:hidden;">' +
    '<tr style="background:' + level.bgColor + ';">' +
    '<td colspan="2" style="padding:12px 16px;font-weight:bold;' +
    'color:' + level.color + ';font-size:15px;">' +
    'Chi Tiết Hóa Đơn</td></tr>' +

    buildInfoRow("Số hóa đơn", row.soHD) +
    buildInfoRow("Khách hàng", row.tenKH) +
    buildInfoRow("Số tiền", '<strong style="color:' +
      level.color + ';font-size:16px;">' + amount + '</strong>') +
    buildInfoRow("Ngày đáo hạn", dueDate) +
    buildInfoRow("Trạng thái",
      diffDays < 0
        ? '<span style="color:#DC2626;font-weight:bold;">' +
          'Quá hạn ' + Math.abs(diffDays) + ' ngày</span>'
        : diffDays === 0
          ? '<span style="color:#2563EB;font-weight:bold;">' +
            'Đến hạn hôm nay</span>'
          : '<span style="color:#059669;font-weight:bold;">' +
            'Còn ' + diffDays + ' ngày</span>'
    ) +
    '</table>' +

    // Thông tin chuyển khoản
    '<div style="background:#F0FDF4;border:1px solid #BBF7D0;' +
    'border-radius:8px;padding:16px;margin:20px 0;">' +
    '<p style="font-weight:bold;color:#166534;margin:0 0 10px;' +
    'font-size:14px;">Thông Tin Chuyển Khoản:</p>' +
    '<p style="margin:4px 0;color:#333;font-size:13px;">' +
    'Ngân hàng: <strong>' + CONFIG.BANK_NAME + '</strong></p>' +
    '<p style="margin:4px 0;color:#333;font-size:13px;">' +
    'Số TK: <strong>' + CONFIG.BANK_ACCOUNT + '</strong></p>' +
    '<p style="margin:4px 0;color:#333;font-size:13px;">' +
    'Chủ TK: <strong>' + CONFIG.BANK_HOLDER + '</strong></p>' +
    '<p style="margin:4px 0;color:#333;font-size:13px;">' +
    'Nội dung CK: <strong>' + row.soHD + ' - ' +
    row.maKH + '</strong></p>' +
    '</div>' +

    footer +
    '</td></tr>' +

    // Footer
    '<tr><td style="background:#f9fafb;padding:20px 30px;' +
    'text-align:center;border-top:1px solid #e5e7eb;">' +
    '<p style="margin:0;color:#6b7280;font-size:12px;">' +
    CONFIG.COMPANY_NAME + ' | ' + CONFIG.COMPANY_PHONE +
    ' | ' + CONFIG.COMPANY_EMAIL + '</p>' +
    '<p style="margin:5px 0 0;color:#9ca3af;font-size:11px;">' +
    'Email này được gửi tự động. Nếu đã thanh toán, ' +
    'vui lòng bỏ qua.</p>' +
    '</td></tr>' +

    '</table></body></html>';

  return html;
}

function buildInfoRow(label, value) {
  return '<tr><td style="padding:10px 16px;border-bottom:' +
    '1px solid #f3f4f6;color:#6b7280;width:40%;font-size:13px;">' +
    label + '</td>' +
    '<td style="padding:10px 16px;border-bottom:1px solid #f3f4f6;' +
    'color:#111827;font-size:13px;">' + value + '</td></tr>';
}

function formatCurrency(amount) {
  return new Intl.NumberFormat('vi-VN').format(amount) + " VNĐ";
}

3 bộ nội dung theo mức nhắc

/**
 * Lời chào phù hợp với tone giọng
 */
function getGreeting(level, tenKH) {
  switch (level.level) {
    case 1: // Trước hạn - nhẹ nhàng
      return '<p style="color:#333;font-size:15px;line-height:1.6;">' +
        'Kính gửi <strong>' + tenKH + '</strong>,</p>' +
        '<p style="color:#555;font-size:14px;line-height:1.6;">' +
        'Cảm ơn Quý khách đã tin tưởng sử dụng dịch vụ của chúng tôi. ' +
        'Chúng tôi xin gửi lời nhắc nhở nhẹ nhàng về hóa đơn sắp ' +
        'đến hạn thanh toán:</p>';

    case 2: // Đúng hạn - chuyên nghiệp
      return '<p style="color:#333;font-size:15px;line-height:1.6;">' +
        'Kính gửi <strong>' + tenKH + '</strong>,</p>' +
        '<p style="color:#555;font-size:14px;line-height:1.6;">' +
        'Chúng tôi trân trọng thông báo rằng hóa đơn dưới đây ' +
        '<strong>đến hạn thanh toán ngày hôm nay</strong>. ' +
        'Kính mong Quý khách sắp xếp thanh toán để đảm bảo ' +
        'tiến độ hợp tác:</p>';

    case 3: // Quá hạn - nghiêm túc
      return '<p style="color:#333;font-size:15px;line-height:1.6;">' +
        'Kính gửi <strong>' + tenKH + '</strong>,</p>' +
        '<p style="color:#555;font-size:14px;line-height:1.6;">' +
        'Chúng tôi nhận thấy hóa đơn dưới đây ' +
        '<strong style="color:#DC2626;">đã quá hạn thanh toán</strong>. ' +
        'Đề nghị Quý khách vui lòng thanh toán trong thời gian ' +
        'sớm nhất để tránh ảnh hưởng đến việc hợp tác:</p>';
  }
}

/**
 * Nội dung bổ sung theo mức nhắc
 */
function getMessage(level, diffDays, soHD, amount, dueDate) {
  if (level.level === 3) {
    return '<div style="background:#FEF2F2;border-left:4px solid #DC2626;' +
      'padding:12px 16px;margin:15px 0;border-radius:0 8px 8px 0;">' +
      '<p style="color:#991B1B;margin:0;font-size:14px;">' +
      '<strong>Hóa đơn ' + soHD + '</strong> trị giá ' + amount +
      ' đã quá hạn <strong>' + Math.abs(diffDays) +
      ' ngày</strong> (hạn thanh toán: ' + dueDate + ').</p></div>';
  }
  return '';
}

/**
 * Lời kết theo mức nhắc
 */
function getFooterMessage(level) {
  switch (level.level) {
    case 1:
      return '<p style="color:#555;font-size:14px;line-height:1.6;' +
        'margin-top:20px;">Nếu Quý khách đã thanh toán, xin vui lòng ' +
        'bỏ qua email này. Mọi thắc mắc vui lòng liên hệ phòng kế toán.' +
        '</p><p style="color:#333;font-size:14px;margin-top:15px;">' +
        'Trân trọng,<br><strong>Phòng Kế toán</strong><br>' +
        CONFIG.COMPANY_NAME + '</p>';

    case 2:
      return '<p style="color:#555;font-size:14px;line-height:1.6;' +
        'margin-top:20px;">Kính mong Quý khách xác nhận thanh toán ' +
        'trong ngày hôm nay hoặc liên hệ với chúng tôi nếu cần hỗ trợ.' +
        '</p><p style="color:#333;font-size:14px;margin-top:15px;">' +
        'Trân trọng,<br><strong>Phòng Kế toán</strong><br>' +
        CONFIG.COMPANY_NAME + '</p>';

    case 3:
      return '<div style="background:#FFFBEB;border:1px solid #FDE68A;' +
        'border-radius:8px;padding:12px 16px;margin:20px 0;">' +
        '<p style="color:#92400E;margin:0;font-size:13px;">' +
        '<strong>Lưu ý:</strong> Nếu không nhận được phản hồi trong ' +
        '7 ngày, chúng tôi sẽ tạm ngưng cung cấp dịch vụ theo điều khoản ' +
        'hợp đồng.</p></div>' +
        '<p style="color:#333;font-size:14px;margin-top:15px;">' +
        'Trân trọng,<br><strong>Phòng Kế toán</strong><br>' +
        CONFIG.COMPANY_NAME + '</p>';
  }
}

Bước 5: Trigger Tự Động Chạy Mỗi Ngày 9h Sáng

Thay vì chạy thủ công, bạn có thể đặt Time-driven trigger để script tự động quét và gửi email mỗi ngày vào lúc 9h sáng (giờ Việt Nam). Đây là thời điểm lý tưởng vì khách hàng thường kiểm tra email đầu giờ sáng.

Cách 1: Tạo trigger bằng code (khuyến nghị)

/**
 * Tạo trigger chạy tự động mỗi ngày lúc 9h sáng
 * CHỈ CẦN CHẠY HÀM NÀY 1 LẦN DUY NHẤT
 */
function createDailyTrigger() {
  // Xóa trigger cũ (nếu có) để tránh trùng lặp
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "sendAdvancedReminders") {
      ScriptApp.deleteTrigger(triggers[i]);
      Logger.log("Đã xóa trigger cũ");
    }
  }

  // Tạo trigger mới
  ScriptApp.newTrigger("sendAdvancedReminders")
    .timeBased()
    .everyDays(1)
    .atHour(9)           // 9h sáng
    .nearMinute(0)       // Khoảng phút 0 (có thể lệch 0-15 phút)
    .inTimezone("Asia/Ho_Chi_Minh")  // Múi giờ VN
    .create();

  Logger.log("Đã tạo trigger chạy mỗi ngày lúc 9:00 AM (VN)");
}

/**
 * Xem danh sách trigger đang hoạt động
 */
function listTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  if (triggers.length === 0) {
    Logger.log("Không có trigger nào.");
    return;
  }
  for (var i = 0; i < triggers.length; i++) {
    Logger.log(
      "Trigger " + (i + 1) + ": " +
      triggers[i].getHandlerFunction() + " - " +
      triggers[i].getTriggerSource()
    );
  }
}

/**
 * Xóa tất cả trigger
 */
function deleteAllTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  Logger.log("Đã xóa " + triggers.length + " trigger(s)");
}

Cách 2: Tạo trigger qua giao diện

Bước 6: Ghi Log Lịch Sử Gửi Email

Trong script nâng cao ở Bước 3, chúng ta đã tích hợp sẵn việc ghi log vào sheet "Log". Bây giờ hãy nâng cấp thêm với hàm xem thống kê và dọn dẹp log cũ.

/**
 * Xem thống kê email đã gửi
 */
function viewEmailStats() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName(CONFIG.LOG_SHEET);
  if (!logSheet) {
    Logger.log("Chưa có sheet Log. Chạy sendAdvancedReminders trước.");
    return;
  }

  var data = logSheet.getDataRange().getValues();
  var stats = {
    total: data.length - 1, // Trừ header
    success: 0,
    error: 0,
    byLevel: { "Trước hạn": 0, "Đúng hạn": 0, "Quá hạn": 0 },
    today: 0
  };

  var today = new Date();
  today.setHours(0, 0, 0, 0);

  for (var i = 1; i < data.length; i++) {
    var sendDate = new Date(data[i][0]);
    sendDate.setHours(0, 0, 0, 0);
    var level = data[i][4];
    var status = data[i][5];

    if (status === "Thành công") {
      stats.success++;
    } else {
      stats.error++;
    }

    if (stats.byLevel[level] !== undefined) {
      stats.byLevel[level]++;
    }

    if (sendDate.getTime() === today.getTime()) {
      stats.today++;
    }
  }

  Logger.log("=== THỐNG KÊ EMAIL NHẮC NHỞ ===");
  Logger.log("Tổng email đã gửi: " + stats.total);
  Logger.log("Thành công: " + stats.success);
  Logger.log("Lỗi: " + stats.error);
  Logger.log("Gửi hôm nay: " + stats.today);
  Logger.log("--- Theo mức nhắc ---");
  Logger.log("Trước hạn: " + stats.byLevel["Trước hạn"]);
  Logger.log("Đúng hạn: " + stats.byLevel["Đúng hạn"]);
  Logger.log("Quá hạn: " + stats.byLevel["Quá hạn"]);
}

/**
 * Xóa log cũ hơn 90 ngày (chạy định kỳ để giảm dung lượng)
 */
function cleanOldLogs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName(CONFIG.LOG_SHEET);
  if (!logSheet) return;

  var data = logSheet.getDataRange().getValues();
  var cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - 90); // 90 ngày trước

  var rowsToDelete = [];
  for (var i = data.length - 1; i >= 1; i--) {
    var logDate = new Date(data[i][0]);
    if (logDate < cutoffDate) {
      rowsToDelete.push(i + 1); // 1-indexed
    }
  }

  // Xóa từ dưới lên để không bị lệch index
  for (var j = 0; j < rowsToDelete.length; j++) {
    logSheet.deleteRow(rowsToDelete[j]);
  }

  Logger.log("Đã xóa " + rowsToDelete.length + " dòng log cũ hơn 90 ngày");
}

Bước 7: Tùy Chỉnh Nâng Cao

Bổ sung thêm các tính năng tiện ích: menu tùy chỉnh trong Google Sheets để gửi thủ công, và logic thông minh hơn để xử lý các trường hợp đặc biệt.

Thêm menu tùy chỉnh vào Google Sheets

/**
 * Tạo menu tùy chỉnh khi mở Spreadsheet
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Nhắc Thanh Toán")
    .addItem("Gửi nhắc nhở tự động", "sendAdvancedReminders")
    .addItem("Gửi nhắc cho dòng đang chọn", "sendManualReminder")
    .addSeparator()
    .addItem("Xem thống kê", "viewEmailStats")
    .addItem("Dọn dẹp log cũ (90 ngày)", "cleanOldLogs")
    .addSeparator()
    .addItem("Tạo trigger hàng ngày 9h", "createDailyTrigger")
    .addItem("Xem trigger hiện tại", "listTriggers")
    .addItem("Xóa tất cả trigger", "deleteAllTriggers")
    .addToUi();
}

/**
 * Gửi email nhắc nhở cho dòng đang chọn (gửi thủ công)
 */
function sendManualReminder() {
  var ui = SpreadsheetApp.getUi();
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(CONFIG.SHEET_NAME);
  var activeRange = sheet.getActiveRange();

  if (!activeRange) {
    ui.alert("Vui lòng chọn 1 dòng trong sheet Công nợ.");
    return;
  }

  var row = activeRange.getRow();
  if (row <= 1) {
    ui.alert("Không thể gửi cho dòng header. Chọn dòng dữ liệu.");
    return;
  }

  var data = sheet.getRange(row, 1, 1, 10).getValues()[0];
  var rowData = {
    maKH: data[0],
    tenKH: data[1],
    email: data[2],
    soHD: data[3],
    soTien: data[4],
    ngayDaoHan: new Date(data[5]),
    trangThai: data[6],
    soLanNhac: data[8] || 0
  };

  // Kiểm tra dữ liệu
  if (!rowData.email) {
    ui.alert("Dòng này không có email.");
    return;
  }
  if (rowData.trangThai === "Đã thanh toán") {
    var confirm = ui.alert(
      "Xác nhận",
      "Hóa đơn này đã được đánh dấu 'Đã thanh toán'. " +
      "Vẫn muốn gửi email?",
      ui.ButtonSet.YES_NO
    );
    if (confirm !== ui.Button.YES) return;
  }

  // Tính mức nhắc
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  rowData.ngayDaoHan.setHours(0, 0, 0, 0);
  var diffDays = Math.floor(
    (rowData.ngayDaoHan - today) / (1000 * 60 * 60 * 24)
  );

  // Cho phép chọn mức nhắc khi gửi thủ công
  var level = getReminderLevel(diffDays);
  if (!level) {
    // Nếu chưa đến thời điểm nhắc, dùng mức "Trước hạn"
    level = {
      level: 1, name: "Trước hạn", color: "#059669",
      bgColor: "#ECFDF5",
      subject: "Nhắc nhở: Hóa đơn sắp đến hạn thanh toán",
      tone: "friendly"
    };
  }

  var result = sendReminderEmail(rowData, level, diffDays);

  if (result.success) {
    // Cập nhật sheet
    sheet.getRange(row, 8).setValue(new Date());
    sheet.getRange(row, 9).setValue(rowData.soLanNhac + 1);

    // Ghi log
    var logSheet = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName(CONFIG.LOG_SHEET);
    if (logSheet) {
      logSheet.appendRow([
        new Date(), rowData.maKH, rowData.email,
        rowData.soHD, level.name + " (Thủ công)", "Thành công"
      ]);
    }

    ui.alert(
      "Thành công!",
      "Đã gửi email nhắc nhở đến " + rowData.email +
      "\nMức nhắc: " + level.name,
      ui.ButtonSet.OK
    );
  } else {
    ui.alert("Lỗi", "Không thể gửi email: " + result.error, ui.ButtonSet.OK);
  }
}

Logic thông minh: Tránh gửi spam

Để tránh gửi quá nhiều email cho cùng 1 khách hàng (gây phản cảm), thêm các điều kiện lọc thông minh:

/**
 * Kiểm tra xem có nên gửi email cho dòng này không
 * Quy tắc:
 * - Không gửi nếu đã nhắc hôm nay
 * - Mức 1 (trước hạn): Chỉ gửi 1 lần duy nhất
 * - Mức 2 (đúng hạn): Chỉ gửi 1 lần duy nhất
 * - Mức 3 (quá hạn): Gửi lại mỗi 3 ngày, tối đa 5 lần
 */
function shouldSendReminder(row, level, diffDays) {
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  // Đã nhắc hôm nay rồi?
  if (row.lanNhacCuoi) {
    var lastRemind = new Date(row.lanNhacCuoi);
    lastRemind.setHours(0, 0, 0, 0);
    if (lastRemind.getTime() === today.getTime()) {
      return false; // Đã nhắc hôm nay
    }

    // Mức 3: Chỉ nhắc lại sau 3 ngày
    if (level.level === 3) {
      var daysSinceLastRemind = Math.floor(
        (today - lastRemind) / (1000 * 60 * 60 * 24)
      );
      if (daysSinceLastRemind < 3) return false;
      if (row.soLanNhac >= 5) return false; // Tối đa 5 lần
    }

    // Mức 1 & 2: Đã nhắc rồi thì không nhắc lại
    if (level.level <= 2 && row.soLanNhac > 0) {
      return false;
    }
  }

  return true;
}

Bước 8: Mẫu Email HTML Hoàn Chỉnh (Copy-Paste)

Dưới đây là bản tổng hợp toàn bộ code hoàn chỉnh mà bạn có thể copy và paste trực tiếp vào Apps Script Editor. Code này bao gồm tất cả các tính năng đã hướng dẫn ở trên.

Cách chạy test nhanh

Giới Hạn Gmail Và Cách Xử Lý

Google có giới hạn về số lượng email bạn có thể gửi qua Apps Script. Biết rõ giới hạn này giúp bạn lập kế hoạch phù hợp và tránh bị block.

Cách xử lý khi có nhiều hóa đơn

/**
 * Kiểm tra quota email còn lại trước khi gửi
 */
function checkEmailQuota() {
  var remaining = MailApp.getRemainingDailyQuota();
  Logger.log("Email quota còn lại hôm nay: " + remaining);
  return remaining;
}

/**
 * Gửi nhắc nhở có kiểm soát quota
 * Nếu gần hết quota, ưu tiên gửi cho hóa đơn quá hạn trước
 */
function sendRemindersWithQuotaCheck() {
  var remaining = MailApp.getRemainingDailyQuota();

  if (remaining < 5) {
    Logger.log("Quota còn lại quá ít (" + remaining +
      "). Dừng gửi email.");
    return;
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  // Phân loại hóa đơn theo mức ưu tiên
  var overdue = [];   // Quá hạn - ưu tiên cao nhất
  var dueToday = [];  // Đúng hạn
  var upcoming = [];  // Sắp đến hạn

  for (var i = 1; i < data.length; i++) {
    var trangThai = data[i][6];
    var email = data[i][2];
    if (trangThai === "Đã thanh toán" || !email) continue;

    var ngayDaoHan = new Date(data[i][5]);
    ngayDaoHan.setHours(0, 0, 0, 0);
    var diffDays = Math.floor(
      (ngayDaoHan - today) / (1000 * 60 * 60 * 24)
    );

    var item = { rowIndex: i, data: data[i], diffDays: diffDays };

    if (diffDays < 0) overdue.push(item);
    else if (diffDays === 0) dueToday.push(item);
    else if (diffDays <= CONFIG.DAYS_BEFORE) upcoming.push(item);
  }

  // Sắp xếp quá hạn theo số ngày (nhiều nhất trước)
  overdue.sort(function(a, b) { return a.diffDays - b.diffDays; });

  // Gửi theo thứ tự ưu tiên
  var allItems = overdue.concat(dueToday).concat(upcoming);
  var sentCount = 0;

  for (var j = 0; j < allItems.length; j++) {
    if (MailApp.getRemainingDailyQuota() < 2) {
      Logger.log("Hết quota email. Đã gửi: " + sentCount +
        ". Còn lại: " + (allItems.length - j));
      break;
    }
    // ... gửi email cho allItems[j] ...
    sentCount++;
  }

  Logger.log("Hoàn tất: " + sentCount + "/" + allItems.length + " email");
}

Câu Hỏi Thường Gặp (FAQ)

var file = DriveApp.getFileById("FILE_ID_ON_DRIVE");
GmailApp.sendEmail(email, subject, body, {
  htmlBody: htmlContent,
  attachments: [file.getAs(MimeType.PDF)]
});

Workflow Hoàn Chỉnh: Từ Bảng Tính Đến Thu Hồi Công Nợ

Tổng hợp lại, đây là quy trình hoàn chỉnh bạn sẽ có sau khi setup xong:

Mở Rộng: Kết Hợp Với Google Form

Bạn có thể nâng cấp hệ thống bằng cách tạo Google Form để khách hàng xác nhận thanh toán. Khi khách nhận email nhắc nhở, họ click vào link Form và xác nhận đã chuyển khoản. Thông tin tự động cập nhật vào Google Sheets.

/**
 * Thêm link xác nhận thanh toán vào email
 * Tạo Google Form với pre-filled URL
 */
function getPaymentConfirmLink(soHD, maKH) {
  var formUrl = "https://docs.google.com/forms/d/e/YOUR_FORM_ID/viewform";
  var preFilled = formUrl +
    "?entry.111111=" + encodeURIComponent(soHD) +
    "&entry.222222=" + encodeURIComponent(maKH);
  return preFilled;
}

/**
 * Khi nhận response từ Form, tự động cập nhật sheet
 * Đặt trigger: On form submit
 */
function onFormSubmit(e) {
  var responses = e.namedValues;
  var soHD = responses["Số hóa đơn"][0];
  var maKH = responses["Mã khách hàng"][0];

  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(CONFIG.SHEET_NAME);
  var data = sheet.getDataRange().getValues();

  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === maKH && data[i][3] === soHD) {
      // Đánh dấu "Đang xác nhận" (chờ kế toán verify)
      sheet.getRange(i + 1, 7).setValue("Đang xác nhận");
      sheet.getRange(i + 1, 10).setValue(
        "KH xác nhận đã TT qua Form lúc " +
        Utilities.formatDate(new Date(), "Asia/Ho_Chi_Minh",
          "dd/MM/yyyy HH:mm")
      );
      break;
    }
  }
}

Tổng Kết

Với hệ thống nhắc nhở thanh toán tự động bằng Google Sheets + Apps Script, bạn đã có trong tay công cụ miễn phí nhưng cực kỳ hiệu quả để quản lý công nợ:

Hãy bắt đầu với script cơ bản (Bước 2), test với 2-3 hóa đơn, rồi nâng cấp dần lên phiên bản nâng cao. Chỉ cần 30 phút setup, bạn sẽ tiết kiệm hàng chục giờ mỗi tháng và không bao giờ bỏ sót hóa đơn nào nữa!