CRM + Google Sheets + Apps Script: Tự Xây Pipeline Tự Động

// Trigger: onEdit hoặc onFormSubmit
function onNewLeadAdded(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('PIPELINE');
  const lastRow = sheet.getLastRow();
  const newRow = sheet.getRange(lastRow, 1, 1, 9).getValues()[0];

  const customerName = newRow[1];  // Cột B
  const customerEmail = newRow[2]; // Cột C
  const salesRepEmail = newRow[8]; // Cột I

  // 1. Gửi email chào mừng cho khách hàng
  if (customerEmail) {
    const subject = "Xin chào " + customerName + " — Cảm ơn bạn đã liên hệ";
    const body = "Kính gửi " + customerName + ",\n\n" +
      "Cảm ơn bạn đã quan tâm đến dịch vụ của chúng tôi.\n" +
      "Đội sales sẽ liên hệ với bạn trong vòng 24 giờ làm việc.\n\n" +
      "Trân trọng,\nĐội Sales SheetStore";
    GmailApp.sendEmail(customerEmail, subject, body);
  }

  // 2. Thông báo cho sales rep được assign
  if (salesRepEmail) {
    const repSubject = "[Lead mới] " + customerName + " cần follow-up";
    const repBody = "Bạn vừa được assign lead mới:\n\n" +
      "Tên: " + customerName + "\n" +
      "Email: " + customerEmail + "\n" +
      "SĐT: " + newRow[3] + "\n\n" +
      "Vui lòng liên hệ trong 24 giờ.";
    GmailApp.sendEmail(salesRepEmail, repSubject, repBody);
  }

  // 3. Set follow-up date = ngày mai
  const tomorrow = new Date();
  tomorrow.setDate(tomorrow.getDate() + 1);
  sheet.getRange(lastRow, 7).setValue(tomorrow); // Cột G

  console.log('New lead processed: ' + customerName);
}

function sendFollowUpReminders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('PIPELINE');
  const data = sheet.getDataRange().getValues();
  const today = new Date();
  today.setHours(0, 0, 0, 0);

  const overdueDeals = [];
  const todayDeals = [];

  // Bỏ qua row 1 (header)
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const stage = row[4];      // Cột E - Giai đoạn
    const followUpDate = row[6]; // Cột G - Ngày follow-up
    const salesRepEmail = row[8]; // Cột I

    // Bỏ qua deals đã closed
    if (stage === 'Closed Won' || stage === 'Closed Lost') continue;
    if (!followUpDate || !salesRepEmail) continue;

    const fDate = new Date(followUpDate);
    fDate.setHours(0, 0, 0, 0);

    if (fDate < today) {
      overdueDeals.push({ row: i + 1, data: row });
    } else if (fDate.getTime() === today.getTime()) {
      todayDeals.push({ row: i + 1, data: row });
    }
  }

  // Gom deals theo sales rep và gửi 1 email tổng hợp
  const repDeals = {};
  [...overdueDeals, ...todayDeals].forEach(deal => {
    const email = deal.data[8];
    if (!repDeals[email]) repDeals[email] = { overdue: [], today: [] };
    if (overdueDeals.includes(deal)) {
      repDeals[email].overdue.push(deal);
    } else {
      repDeals[email].today.push(deal);
    }
  });

  Object.keys(repDeals).forEach(repEmail => {
    const deals = repDeals[repEmail];
    let body = '📋 TÓM TẮT FOLLOW-UP HÔM NAY

';

    if (deals.overdue.length > 0) {
      body += "⚠️ QUÁ HẠN (" + deals.overdue.length + " deals):
";
      deals.overdue.forEach(d => {
        body += "  • " + d.data[1] + " (" + d.data[2] + ") — " +
          "Hạn: " + d.data[6] + "
";
      });
      body += '
';
    }

    if (deals.today.length > 0) {
      body += "📅 CẦN FOLLOW-UP HÔM NAY (" + deals.today.length + " deals):
";
      deals.today.forEach(d => {
        body += "  • " + d.data[1] + " (" + d.data[2] + ")
";
      });
    }

    body += '
Vui lòng cập nhật trạng thái sau khi liên hệ.';

    GmailApp.sendEmail(
      repEmail,
      "[CRM] " + (deals.overdue.length + deals.today.length) + " deals cần follow-up hôm nay",
      body
    );
  });

  console.log('Reminders sent to ' + Object.keys(repDeals).length + ' reps');
}

function sendWeeklyPipelineReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('PIPELINE');
  const data = sheet.getDataRange().getValues();

  // Tính toán thống kê
  const stats = {
    total: 0,
    byStage: {},
    totalValue: 0,
    weightedValue: 0,
    closedWon: 0,
    closedWonValue: 0
  };

  const stageWeights = {
    'New Lead': 0.1,
    'Contacted': 0.2,
    'Qualified': 0.4,
    'Proposal': 0.6,
    'Negotiation': 0.8,
    'Closed Won': 1.0
  };

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

    const stage = row[4];
    const value = parseFloat(row[5]) || 0;

    stats.total++;
    stats.byStage[stage] = (stats.byStage[stage] || 0) + 1;
    stats.totalValue += value;
    stats.weightedValue += value * (stageWeights[stage] || 0);

    if (stage === 'Closed Won') {
      stats.closedWon++;
      stats.closedWonValue += value;
    }
  }

  // Tạo nội dung báo cáo
  const formatVND = (n) =>
    new Intl.NumberFormat('vi-VN').format(n) + ' đ';

  let report = '📊 BÁO CÁO PIPELINE TUẦN
';
  report += '================================

';
  report += "Tổng deals: " + stats.total + "
";
  report += "Pipeline value: " + formatVND(stats.totalValue) + "
";
  report += "Weighted forecast: " + formatVND(Math.round(stats.weightedValue)) + "
";
  report += "Closed Won tuần này: " + stats.closedWon + " deals
";
  report += "Doanh thu chốt: " + formatVND(stats.closedWonValue) + "

";
  report += 'PHÂN BỔ THEO GIAI ĐOẠN:
';

  Object.keys(stageWeights).forEach(stage => {
    const count = stats.byStage[stage] || 0;
    if (count > 0) {
      report += "  " + stage + ": " + count + " deals
";
    }
  });

  // Đọc email manager từ CONFIG sheet
  const configSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('CONFIG');
  const managerEmail = configSheet
    ? configSheet.getRange('B1').getValue()
    : Session.getActiveUser().getEmail();

  GmailApp.sendEmail(
    managerEmail,
    "[CRM] Báo cáo pipeline tuần " + new Date().toLocaleDateString("vi-VN"),
    report
  );

  console.log('Weekly report sent to: ' + managerEmail);
}

// Tự động chuyển deal sang "Stale" nếu không có activity 14 ngày
function markStaleDeals() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('PIPELINE');
  const data = sheet.getDataRange().getValues();
  const today = new Date();
  const staleDays = 14;

  let staleCount = 0;

  for (let i = 1; i < data.length; i++) {
    const stage = data[i][4];
    const followUpDate = data[i][6];

    // Chỉ check các deal đang open
    if (!stage || stage.includes('Closed') || stage === 'Stale') continue;
    if (!followUpDate) continue;

    const fDate = new Date(followUpDate);
    const daysDiff = Math.floor(
      (today - fDate) / (1000 * 60 * 60 * 24)
    );

    if (daysDiff > staleDays) {
      // Đánh dấu màu đỏ và thêm note
      sheet.getRange(i + 1, 5).setBackground('#fee2e2'); // Highlight đỏ
      const currentNote = sheet.getRange(i + 1, 5).getNote();
      sheet.getRange(i + 1, 5).setNote(
        "[Stale " + daysDiff + " ngày] " + currentNote
      );
      staleCount++;
    }
  }

  console.log("Marked " + staleCount + " stale deals");
}