Tự Động Hóa Quản Lý Công Việc Với Google Sheets & Apps Script: Hướng Dẫn Từ A-Z

// Cột A-H trong sheet "Tasks"

A: Task ID | B: Tên task | C: Người phụ trách

D: Deadline | E: Trạng thái | F: Ưu tiên

G: Email người phụ trách | H: Ghi chú

// Trạng thái: "Chưa làm" | "Đang làm" | "Chờ duyệt" | "Hoàn thành" | "Trễ hạn"

// === AUTOMATION #1: Email nhắc deadline ===

function

sendDeadlineReminders() {

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

  // Bỏ qua hàng tiêu đề (hàng 1)
  for (let i = 1; i < data.length; i++) {
    const taskName   = data[i][1]; // Cột B
    const assignee   = data[i][2]; // Cột C
    const deadline   = new Date(data[i][3]); // Cột D
    const status     = data[i][4]; // Cột E
    const email      = data[i][6]; // Cột G

    // Bỏ qua task đã hoàn thành
    if (status === 'Hoàn thành' || !email) continue;

    deadline.setHours(0, 0, 0, 0);
    const daysLeft = Math.round(
      (deadline - today) / (1000 * 60 * 60 * 24)
    );

    // Nhắc khi còn 3 ngày, 1 ngày, hoặc đã trễ
    if (daysLeft === 3 || daysLeft === 1 || daysLeft < 0) {
      const subject = daysLeft < 0
        ? '[TRE HAN ' + Math.abs(daysLeft) + ' ngay] ' + taskName
        : 'Deadline con ' + daysLeft + ' ngay: ' + taskName;

      const body = 'Xin chao ' + assignee + ',

'
        + (daysLeft < 0
          ? 'Task da TRE HAN ' + Math.abs(daysLeft) + ' ngay!'
          : 'Deadline task sap den!')
        + '

Task: ' + taskName
        + '
Deadline: ' + deadline.toLocaleDateString('vi-VN')
        + '
Trang thai: ' + status
        + '

Vui long cap nhat tien do.

Tran trong';

      GmailApp.sendEmail(email, subject, body);
      console.log('Da gui reminder cho ' + assignee + ': ' + taskName);
    }
  }
}

// === AUTOMATION #2: Tự động cập nhật trạng thái và màu ===

function autoUpdateTaskStatus() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                              .getSheetByName('Tasks');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

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

  for (let row = 2; row <= lastRow; row++) {
    const deadlineCell = sheet.getRange(row, 4); // Cột D
    const statusCell   = sheet.getRange(row, 5); // Cột E
    const rowRange     = sheet.getRange(row, 1, 1, 8);

    const deadline = new Date(deadlineCell.getValue());
    const status   = statusCell.getValue();
    deadline.setHours(0, 0, 0, 0);

    // Bỏ qua hàng trống hoặc task đã xong
    if (!deadlineCell.getValue() || status === 'Hoàn thành') {
      if (status === 'Hoàn thành') {
        // Tô màu xanh lá cho task hoàn thành
        rowRange.setBackground('#d4edda');
      }
      continue;
    }

    const daysLeft = Math.round(
      (deadline - today) / (1000 * 60 * 60 * 24)
    );

    if (daysLeft < 0 && status !== 'Trễ hạn') {
      // Tự động mark trễ hạn + tô màu đỏ
      statusCell.setValue('Trễ hạn');
      rowRange.setBackground('#f8d7da');
    } else if (daysLeft === 0) {
      // Hôm nay deadline + tô màu vàng cam
      rowRange.setBackground('#fff3cd');
    } else if (daysLeft <= 3) {
      // Sắp deadline + tô màu vàng nhạt
      rowRange.setBackground('#fff9c4');
    } else if (status === 'Đang làm') {
      // Đang làm bình thường + tô màu xanh nhạt
      rowRange.setBackground('#d1ecf1');
    } else {
      // Reset về trắng nếu không có điều kiện nào
      rowRange.setBackground('#ffffff');
    }
  }

  // Cập nhật timestamp lần cuối chạy
  const infoSheet = SpreadsheetApp.getActiveSpreadsheet()
                                  .getSheetByName('Info');
  if (infoSheet) {
    infoSheet.getRange('B1').setValue(
      'Cập nhật lúc: ' + new Date().toLocaleString('vi-VN')
    );
  }
}

// === AUTOMATION #3: Telegram daily digest ===

const TELEGRAM_TOKEN = 'THAY_BANG_BOT_TOKEN_CUA_BAN';
const TELEGRAM_CHAT_ID = 'THAY_BANG_CHAT_ID_CUA_BAN';

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

  const overdue   = [];
  const dueToday  = [];
  const dueSoon   = []; // 1-3 ngày

  for (let i = 1; i < data.length; i++) {
    const taskName = data[i][1];
    const assignee = data[i][2];
    const deadline = new Date(data[i][3]);
    const status   = data[i][4];

    if (!taskName || status === 'Hoàn thành') continue;

    deadline.setHours(0, 0, 0, 0);
    const daysLeft = Math.round(
      (deadline - today) / (1000 * 60 * 60 * 24)
    );

    if (daysLeft < 0) {
      overdue.push(`❌ ${taskName} - ${assignee} (trễ ${Math.abs(daysLeft)} ngày)`);
    } else if (daysLeft === 0) {
      dueToday.push(`🔴 ${taskName} - ${assignee}`);
    } else if (daysLeft <= 3) {
      dueSoon.push(`🟡 ${taskName} - ${assignee} (còn ${daysLeft} ngày)`);
    }
  }

  // Tạo nội dung message
  let message = `📊 *DAILY TASK DIGEST*
`;
  message += `📅 ${today.toLocaleDateString('vi-VN')}

`;

  if (overdue.length > 0) {
    message += `*⚠️ TRỄ HẠN (${overdue.length} task):*
`;
    message += overdue.join('
') + '

';
  }

  if (dueToday.length > 0) {
    message += `*🔴 HÔM NAY DEADLINE (${dueToday.length} task):*
`;
    message += dueToday.join('
') + '

';
  }

  if (dueSoon.length > 0) {
    message += `*🟡 SẮP ĐẾN HẠN (${dueSoon.length} task):*
`;
    message += dueSoon.join('
') + '

';
  }

  if (overdue.length === 0 && dueToday.length === 0 && dueSoon.length === 0) {
    message += '✅ Tất cả task đều on track! Tiếp tục cố gắng nhé! 💪';
  }

  // Gửi qua Telegram API
  const url = `https://api.telegram.org/bot${TELEGRAM_TOKEN}/sendMessage`;
  const payload = {
    chat_id: TELEGRAM_CHAT_ID,
    text: message,
    parse_mode: 'Markdown'
  };

  UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload)
  });

  console.log('Đã gửi Telegram digest thành công');
}

// === Gửi thông báo Slack qua Incoming Webhook ===

const SLACK_WEBHOOK_URL = 'https://hooks.slack.com/services/THAY_BANG_WEBHOOK_CUA_BAN';

function sendSlackNotification(message) {
  const payload = { text: message };
  UrlFetchApp.fetch(SLACK_WEBHOOK_URL, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload)
  });
}

// Gọi hàm này khi muốn gửi thông báo
// sendSlackNotification('🔔 Task X đã hoàn thành bởi Nguyễn Văn A');

// === AUTOMATION #4: Form → Task (chạy khi có Form submission) ===

function onFormSubmit(e) {
  // e.values là mảng dữ liệu từ Form theo thứ tự cột
  const [timestamp, taskName, description,
         deadline, priority, requesterEmail] = e.values;

  // Mở sheet Tasks
  const ss       = SpreadsheetApp.getActiveSpreadsheet();
  const taskSheet = ss.getSheetByName('Tasks');
  const lastRow  = taskSheet.getLastRow();

  // Tạo Task ID tự động
  const taskId = 'TASK-' + String(lastRow).padStart(4, '0');

  // Thêm row mới
  taskSheet.appendRow([
    taskId,              // A: Task ID
    taskName,            // B: Tên task
    '',                  // C: Người phụ trách (chưa assign)
    new Date(deadline),  // D: Deadline
    'Chưa làm',         // E: Trạng thái
    priority,            // F: Ưu tiên
    requesterEmail,      // G: Email
    description          // H: Ghi chú
  ]);

  // Gửi email xác nhận cho người yêu cầu
  GmailApp.sendEmail(
    requesterEmail,
    `✅ Đã nhận yêu cầu: ${taskName}`,
    `Xin chào,

Yêu cầu công việc của bạn đã được ghi nhận:

Task ID: ${taskId}
Task: ${taskName}
Deadline: ${deadline}
Ưu tiên: ${priority}

Chúng tôi sẽ assign người phụ trách và phản hồi trong vòng 24 giờ.

Trân trọng`
  );

  // Gửi thông báo Telegram cho team lead
  const telegramMsg =
    `📥 *YÊU CẦU MỚI*
` +
    `ID: ${taskId}
` +
    `Task: ${taskName}
` +
    `Deadline: ${deadline}
` +
    `Ưu tiên: ${priority}
` +
    `Từ: ${requesterEmail}`;

  sendTelegramDailyDigest(); // Hoặc gọi hàm send riêng
}

// === AUTOMATION #5: Dashboard refresh + Weekly report ===

function refreshDashboard() {
  const ss        = SpreadsheetApp.getActiveSpreadsheet();
  const taskSheet = ss.getSheetByName('Tasks');
  const dashSheet = ss.getSheetByName('Dashboard');

  if (!dashSheet) {
    console.log('Không tìm thấy sheet Dashboard');
    return;
  }

  const data    = taskSheet.getDataRange().getValues();
  const today   = new Date();
  today.setHours(0, 0, 0, 0);

  // Đếm theo trạng thái
  let stats = {
    total:      0,
    chuaLam:    0,
    dangLam:    0,
    choaDuyet:  0,
    hoanThanh:  0,
    treLan:     0
  };

  let completedThisWeek = 0;
  const weekStart = new Date(today);
  weekStart.setDate(today.getDate() - today.getDay()); // Đầu tuần (Chủ nhật)

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

    switch(data[i][4]) {
      case 'Chưa làm':   stats.chuaLam++;   break;
      case 'Đang làm':   stats.dangLam++;   break;
      case 'Chờ duyệt':  stats.choaDuyet++; break;
      case 'Hoàn thành': stats.hoanThanh++; break;
      case 'Trễ hạn':    stats.treLan++;    break;
    }

    // Đếm hoàn thành trong tuần này
    const deadline = new Date(data[i][3]);
    if (data[i][4] === 'Hoàn thành' && deadline >= weekStart) {
      completedThisWeek++;
    }
  }

  // Cập nhật Dashboard sheet
  dashSheet.getRange('B2').setValue(stats.total);
  dashSheet.getRange('B3').setValue(stats.dangLam);
  dashSheet.getRange('B4').setValue(stats.hoanThanh);
  dashSheet.getRange('B5').setValue(stats.treLan);
  dashSheet.getRange('B6').setValue(completedThisWeek);
  dashSheet.getRange('B7').setValue(
    stats.total > 0
      ? Math.round((stats.hoanThanh / stats.total) * 100) + '%'
      : '0%'
  );
  dashSheet.getRange('B8').setValue(
    'Cập nhật: ' + new Date().toLocaleString('vi-VN')
  );

  console.log('Dashboard đã được refresh:', JSON.stringify(stats));
}

// Gửi báo cáo tuần mỗi thứ Sáu 17:00
function sendWeeklyReport() {
  const today = new Date();
  if (today.getDay() !== 5) return; // Chỉ chạy vào thứ Sáu

  // Refresh dashboard trước
  refreshDashboard();

  const ss        = SpreadsheetApp.getActiveSpreadsheet();
  const dashSheet = ss.getSheetByName('Dashboard');

  const total            = dashSheet.getRange('B2').getValue();
  const inProgress       = dashSheet.getRange('B3').getValue();
  const completed        = dashSheet.getRange('B4').getValue();
  const overdue          = dashSheet.getRange('B5').getValue();
  const completedThisWk  = dashSheet.getRange('B6').getValue();
  const completionRate   = dashSheet.getRange('B7').getValue();

  const reportBody = `
BÁO CÁO TUẦN - ${today.toLocaleDateString('vi-VN')}
================================================

TỔNG QUAN:
  • Tổng task: ${total}
  • Đang thực hiện: ${inProgress}
  • Hoàn thành: ${completed}
  • Trễ hạn: ${overdue}

TUẦN NÀY:
  • Task hoàn thành tuần này: ${completedThisWk}
  • Tỷ lệ hoàn thành: ${completionRate}

Link Sheets: ${ss.getUrl()}
  `.trim();

  // Gửi cho quản lý (thay email bên dưới)
  GmailApp.sendEmail(
    'manager@company.com',  // ← Thay email quản lý
    `📊 Báo cáo tuần ${today.toLocaleDateString('vi-VN')}`,
    reportBody
  );
}