Template Quản Lý Công Nợ & Phải Thu Trên Google Sheets [Miễn Phí 2026]

// Cột G - Tính công nợ hiện tại từ Sổ công nợ

=SUMIFS('Sổ công nợ'!F:F, 'Sổ công nợ'!B:B, A2, 'Sổ công nợ'!D:D, "Phát sinh")
 - SUMIFS('Sổ công nợ'!F:F, 'Sổ công nợ'!B:B, A2, 'Sổ công nợ'!D:D, "Thu")

// Cột H - Tỷ lệ sử dụng hạn mức

=IF(F2>0, G2/F2, 0)

// Cột I - Trạng thái tín dụng (tự động phân loại)

=IF(H2>=1, "🔴 Vượt HM",
  IF(H2>=0.8, "🟠 Cảnh báo",
    IF(H2>=0.5, "🟡 Chú ý", "🟢 Bình thường")))

// Cột H - Số ngày quá hạn (chỉ tính cho khoản Phát sinh chưa thu đủ)

=IF(AND(D2="Phát sinh", I2="Chưa thu"),
  MAX(0, TODAY() - G2), "")

// Cột I - Trạng thái tự động

=IF(D2="Thu", "Đã thu",
  IF(TODAY() > G2, "Quá hạn", "Chưa thu"))

// Cột D (0-30 ngày) - Nợ trong hạn hoặc quá hạn dưới 30 ngày

=SUMPRODUCT(
  ('Sổ công nợ'!B$2:B$1000 = A2) *
  ('Sổ công nợ'!D$2:D$1000 = "Phát sinh") *
  ('Sổ công nợ'!I$2:I$1000 = "Chưa thu") *
  ((TODAY() - 'Sổ công nợ'!G$2:G$1000) <= 30) *
  ((TODAY() - 'Sổ công nợ'!G$2:G$1000) >= 0) *
  ('Sổ công nợ'!F$2:F$1000)
)

// Cột E (31-60 ngày) - Nợ quá hạn 31 đến 60 ngày

=SUMPRODUCT(
  ('Sổ công nợ'!B$2:B$1000 = A2) *
  ('Sổ công nợ'!D$2:D$1000 = "Phát sinh") *
  ('Sổ công nợ'!I$2:I$1000 = "Chưa thu") *
  ((TODAY() - 'Sổ công nợ'!G$2:G$1000) >= 31) *
  ((TODAY() - 'Sổ công nợ'!G$2:G$1000) <= 60) *
  ('Sổ công nợ'!F$2:F$1000)
)

// Cột F (61-90 ngày) - Nợ quá hạn 61 đến 90 ngày

=SUMPRODUCT(
  ('Sổ công nợ'!B$2:B$1000 = A2) *
  ('Sổ công nợ'!D$2:D$1000 = "Phát sinh") *
  ('Sổ công nợ'!I$2:I$1000 = "Chưa thu") *
  ((TODAY() - 'Sổ công nợ'!G$2:G$1000) >= 61) *
  ((TODAY() - 'Sổ công nợ'!G$2:G$1000) <= 90) *
  ('Sổ công nợ'!F$2:F$1000)
)

// Cột G (>90 ngày) - Nợ khó đòi, quá hạn trên 90 ngày

=SUMPRODUCT(
  ('Sổ công nợ'!B$2:B$1000 = A2) *
  ('Sổ công nợ'!D$2:D$1000 = "Phát sinh") *
  ('Sổ công nợ'!I$2:I$1000 = "Chưa thu") *
  ((TODAY() - 'Sổ công nợ'!G$2:G$1000) > 90) *
  ('Sổ công nợ'!F$2:F$1000)
)

// Tổng phải thu - Tổng phát sinh trừ tổng đã thu

=SUMIFS('Sổ công nợ'!F:F, 'Sổ công nợ'!D:D, "Phát sinh")
 - SUMIFS('Sổ công nợ'!F:F, 'Sổ công nợ'!D:D, "Thu")

// Tổng nợ quá hạn - Tổng các khoản phát sinh đã quá hạn thanh toán

=SUMPRODUCT(
  ('Sổ công nợ'!D$2:D$1000 = "Phát sinh") *
  ('Sổ công nợ'!I$2:I$1000 = "Chưa thu") *
  (TODAY() > 'Sổ công nợ'!G$2:G$1000) *
  ('Sổ công nợ'!F$2:F$1000)
)

// Tỷ lệ thu hồi tháng hiện tại

=SUMPRODUCT(
  ('Sổ công nợ'!D$2:D$1000 = "Thu") *
  (MONTH('Sổ công nợ'!A$2:A$1000) = MONTH(TODAY())) *
  (YEAR('Sổ công nợ'!A$2:A$1000) = YEAR(TODAY())) *
  ('Sổ công nợ'!F$2:F$1000)
)
/
SUMPRODUCT(
  ('Sổ công nợ'!D$2:D$1000 = "Phát sinh") *
  (MONTH('Sổ công nợ'!A$2:A$1000) = MONTH(TODAY())) *
  (YEAR('Sổ công nợ'!A$2:A$1000) = YEAR(TODAY())) *
  ('Sổ công nợ'!F$2:F$1000)
)

// Top 10 khách hàng nợ nhiều nhất (dùng SORT + UNIQUE)

=SORT(
  UNIQUE('Khách hàng'!A2:B51),
  BYROW(UNIQUE('Khách hàng'!A2:A51),
    LAMBDA(id, SUMIFS('Sổ công nợ'!F:F, 'Sổ công nợ'!B:B, id, 'Sổ công nợ'!D:D, "Phát sinh")
              - SUMIFS('Sổ công nợ'!F:F, 'Sổ công nợ'!B:B, id, 'Sổ công nợ'!D:D, "Thu"))),
  -1
)

// Mở Apps Script: Extensions > Apps Script

function sendDebtReminders() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const debtSheet = ss.getSheetByName("Sổ công nợ");
  const customerSheet = ss.getSheetByName("Khách hàng");
  const data = debtSheet.getDataRange().getValues();
  const customers = customerSheet.getDataRange().getValues();

  const today = new Date();
  let sentCount = 0;

  // Duyệt từng dòng trong Sổ công nợ
  for (let i = 1; i < data.length; i++) {
    const type = data[i][3];       // Cột D: Loại
    const dueDate = data[i][6];    // Cột G: Hạn thanh toán
    const status = data[i][8];     // Cột I: Trạng thái
    const customerId = data[i][1]; // Cột B: Mã KH
    const amount = data[i][5];     // Cột F: Số tiền
    const invoice = data[i][2];    // Cột C: Số hóa đơn

    // Chỉ nhắc nợ khoản phát sinh, chưa thu, quá hạn
    if (type !== "Phát sinh" || status !== "Chưa thu") continue;
    if (!(dueDate instanceof Date)) continue;

    const daysOverdue = Math.floor(
      (today - dueDate) / (1000 * 60 * 60 * 24)
    );

    // Gửi nhắc nhở ở các mốc: 0 ngày (đến hạn), 7, 15, 30, 60 ngày
    const reminderDays = [0, 7, 15, 30, 60];
    if (!reminderDays.includes(daysOverdue)) continue;

    // Tìm email khách hàng
    const customer = customers.find(c => c[0] === customerId);
    if (!customer || !customer[4]) continue; // Không có email

    const email = customer[4];
    const companyName = customer[1];
    const contactName = customer[2];

    // Nội dung email theo mức độ
    let subject, urgency;
    if (daysOverdue === 0) {
      subject = `[Nhắc nhở] Hóa đơn ${invoice} đến hạn thanh toán hôm nay`;
      urgency = "đến hạn thanh toán hôm nay";
    } else if (daysOverdue <= 15) {
      subject = `[Nhắc nhở] Hóa đơn ${invoice} đã quá hạn ${daysOverdue} ngày`;
      urgency = `đã quá hạn thanh toán ${daysOverdue} ngày`;
    } else if (daysOverdue <= 30) {
      subject = `[QUAN TRỌNG] Hóa đơn ${invoice} quá hạn ${daysOverdue} ngày`;
      urgency = `đã quá hạn ${daysOverdue} ngày, vui lòng thanh toán ngay`;
    } else {
      subject = `[KHẨN CẤP] Hóa đơn ${invoice} quá hạn ${daysOverdue} ngày`;
      urgency = `đã quá hạn ${daysOverdue} ngày. Khoản nợ sẽ được chuyển sang xử lý pháp lý nếu không thanh toán trong 7 ngày`;
    }

    const body = `Kính gửi Anh/Chị ${contactName} - ${companyName},

Chúng tôi xin thông báo hóa đơn ${invoice} với số tiền ${formatCurrency(amount)} ${urgency}.

Chi tiết:
- Số hóa đơn: ${invoice}
- Số tiền: ${formatCurrency(amount)}
- Hạn thanh toán: ${formatDate(dueDate)}
- Số ngày quá hạn: ${daysOverdue} ngày

Vui lòng thanh toán qua tài khoản:
- Ngân hàng: [Tên ngân hàng]
- STK: [Số tài khoản]
- Chủ TK: [Tên công ty]
- Nội dung CK: Thanh toán ${invoice}

Trân trọng,
[Tên công ty của bạn]
Bộ phận Kế toán - ĐT: [Số điện thoại]`;

    MailApp.sendEmail(email, subject, body);
    sentCount++;

    // Log vào sheet
    debtSheet.getRange(i + 1, 10).setValue(
      `Đã gửi nhắc nợ ${formatDate(today)}`
    );
  }

  // Thông báo kết quả
  SpreadsheetApp.getUi().alert(
    `Đã gửi ${sentCount} email nhắc nợ thành công!`
  );
}

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

function formatDate(date) {
  return Utilities.formatDate(date, "Asia/Ho_Chi_Minh", "dd/MM/yyyy");
}

// Gửi email tổng hợp aging report cho quản lý mỗi thứ 2

function sendWeeklyAgingReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const agingSheet = ss.getSheetByName("Aging Report");
  const data = agingSheet.getDataRange().getValues();

  // Lấy dữ liệu tổng hợp
  let totalDebt = 0, total030 = 0, total3160 = 0;
  let total6190 = 0, total90plus = 0;
  let overdueCustomers = [];

  for (let i = 1; i < data.length; i++) {
    if (!data[i][0]) continue; // Bỏ qua hàng trống

    const customerId = data[i][0];
    const companyName = data[i][1];
    const rowTotal = data[i][2] || 0;
    const d030 = data[i][3] || 0;
    const d3160 = data[i][4] || 0;
    const d6190 = data[i][5] || 0;
    const d90plus = data[i][6] || 0;

    totalDebt += rowTotal;
    total030 += d030;
    total3160 += d3160;
    total6190 += d6190;
    total90plus += d90plus;

    // Ghi nhận KH có nợ quá hạn >30 ngày
    if (d3160 + d6190 + d90plus > 0) {
      overdueCustomers.push({
        id: customerId,
        name: companyName,
        total: rowTotal,
        overdue: d3160 + d6190 + d90plus
      });
    }
  }

  // Sắp xếp theo nợ quá hạn giảm dần
  overdueCustomers.sort((a, b) => b.overdue - a.overdue);

  // Tạo nội dung HTML email
  let html = `
    
BÁO CÁO TUỔI NỢ TUẦN - ${formatDate(new Date())}
    

      

        
Chỉ số
Giá trị
Tỷ lệ
      
      

        
Tổng phải thu
        
${formatCurrency(totalDebt)}
        
100%
      
      

        
Trong hạn (0-30 ngày)
        
${formatCurrency(total030)}
        
${(total030/totalDebt*100).toFixed(1)}%
      
      

        
Quá hạn 31-60 ngày
        
${formatCurrency(total3160)}
        
${(total3160/totalDebt*100).toFixed(1)}%
      
      

        
Quá hạn 61-90 ngày
        
${formatCurrency(total6190)}
        
${(total6190/totalDebt*100).toFixed(1)}%
      
      

        
Nợ khó đòi (>90 ngày)
        
${formatCurrency(total90plus)}
        
${(total90plus/totalDebt*100).toFixed(1)}%
      
    
  `;

  // Thêm danh sách KH nợ quá hạn
  if (overdueCustomers.length > 0) {
    html += '
Khách hàng cần theo dõi:
';
    overdueCustomers.slice(0, 10).forEach(c => {
      html += `
${c.name} (${c.id}) -
        Nợ quá hạn: ${formatCurrency(c.overdue)}
        / Tổng: ${formatCurrency(c.total)}
`;
    });
    html += '
';
  }

  // Gửi cho quản lý
  const managerEmail = "quanly@congty.vn"; // Thay email thực
  MailApp.sendEmail({
    to: managerEmail,
    subject: `[Báo cáo] Aging Report tuần ${formatDate(new Date())}`,
    htmlBody: html
  });
}

// Thiết lập trigger tự động mỗi thứ 2
function setupWeeklyTrigger() {
  ScriptApp.newTrigger('sendWeeklyAgingReport')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(8)
    .create();
}

// Thiết lập trigger nhắc nợ hàng ngày lúc 9h sáng
function setupDailyReminderTrigger() {
  ScriptApp.newTrigger('sendDebtReminders')
    .timeBased()
    .everyDays(1)
    .atHour(9)
    .create();
}

// Tạo dropdown Mã KH liên kết từ Sheet Khách hàng

// Chọn cột B trong Sổ công nợ > Data > Data validation
// Criteria: List from a range
// Range: 'Khách hàng'!A2:A1000
// Hiển thị: Show dropdown list in cell
// On invalid: Reject input

// Dropdown cho cột Loại (chỉ cho phép Phát sinh hoặc Thu)

// Chọn cột D > Data > Data validation
// Criteria: List of items
// Items: Phát sinh, Thu
// On invalid: Reject input

// Thêm cột phụ tự điền Tên KH khi chọn Mã KH

=IFERROR(
  VLOOKUP(B2, 'Khách hàng'!A:B, 2, FALSE),
  ""
)

// Apps Script: Kiểm tra hạn mức trước khi cho nợ thêm

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sổ công nợ") return;

  const row = e.range.getRow();
  const col = e.range.getColumn();

  // Khi nhập số tiền (cột F) cho giao dịch Phát sinh
  if (col === 6 && row > 1) {
    const type = sheet.getRange(row, 4).getValue();
    if (type !== "Phát sinh") return;

    const customerId = sheet.getRange(row, 2).getValue();
    const amount = e.value;

    // Lấy hạn mức và nợ hiện tại từ Sheet Khách hàng
    const customerSheet = e.source.getSheetByName("Khách hàng");
    const customers = customerSheet.getDataRange().getValues();
    const customer = customers.find(c => c[0] === customerId);

    if (customer) {
      const creditLimit = customer[5];  // Cột F: Hạn mức
      const currentDebt = customer[6];  // Cột G: Nợ hiện tại
      const newTotal = currentDebt + Number(amount);

      if (newTotal > creditLimit) {
        SpreadsheetApp.getUi().alert(
          `CẢNH BÁO VƯỢT HẠN MỨC!\n\n` +
          `Khách hàng: ${customer[1]}\n` +
          `Hạn mức: ${formatCurrency(creditLimit)}\n` +
          `Nợ hiện tại: ${formatCurrency(currentDebt)}\n` +
          `Giao dịch mới: ${formatCurrency(Number(amount))}\n` +
          `Tổng sau GD: ${formatCurrency(newTotal)}\n` +
          `Vượt hạn mức: ${formatCurrency(newTotal - creditLimit)}`
        );
      }
    }
  }
}