Đồng Bộ Google Calendar Với Google Sheets Bằng Apps Script

Giới Thiệu

Google Calendar và Google Sheets là hai công cụ mạnh mẽ của Google Workspace. Kết hợp chúng qua Apps Script, bạn có thể: xuất lịch hẹn vào báo cáo, tạo events hàng loạt từ sheet dữ liệu, theo dõi lịch làm việc nhân viên, và gửi nhắc nhở tự động.

Đọc Events Từ Calendar Vào Google Sheets

/**
 * Đọc tất cả events trong khoảng thời gian và ghi vào Sheet
 * @param {string} calendarId - ID calendar (email hoặc 'primary')
 * @param {Date} startDate
 * @param {Date} endDate
 */
function syncCalendarToSheet(calendarId, startDate, endDate) {
  try {
    const calendar = CalendarApp.getCalendarById(calendarId) || CalendarApp.getDefaultCalendar();
    const events = calendar.getEvents(startDate, endDate);

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName('Calendar Events') || ss.insertSheet('Calendar Events');

    // Clear và set headers
    sheet.clearContents();
    const headers = ['ID', 'Tiêu đề', 'Bắt đầu', 'Kết thúc', 'Địa điểm', 'Mô tả', 'Người tham gia', 'Trạng thái'];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold').setBackground('#4285F4').setFontColor('#FFFFFF');

    if (events.length === 0) {
      console.log('No events found in range.');
      return;
    }

    // Ghi events vào sheet
    const rows = events.map(event => {
      const guests = event.getGuestList().map(g => g.getEmail()).join(', ');
      return [
        event.getId(),
        event.getTitle(),
        event.getStartTime(),
        event.getEndTime(),
        event.getLocation() || '',
        event.getDescription() || '',
        guests,
        event.getMyStatus().toString()
      ];
    });

    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

    // Format cột ngày
    sheet.getRange(2, 3, rows.length, 2).setNumberFormat('dd/MM/yyyy HH:mm');
    sheet.autoResizeColumns(1, headers.length);

    console.log('Synced ' + events.length + ' events to sheet.');
    SpreadsheetApp.getActiveSpreadsheet().toast(events.length + ' events đã được đồng bộ!', 'Calendar Sync', 3);

  } catch (error) {
    console.error('syncCalendarToSheet error: ' + error.toString());
    throw error;
  }
}

// Hàm tiện ích: Sync tháng hiện tại
function syncCurrentMonth() {
  const now = new Date();
  const startDate = new Date(now.getFullYear(), now.getMonth(), 1);
  const endDate = new Date(now.getFullYear(), now.getMonth() + 1, 0, 23, 59, 59);
  syncCalendarToSheet('primary', startDate, endDate);
}

Tạo Events Hàng Loạt Từ Google Sheets

/**
 * Đọc sheet "Events To Create" và tạo Calendar events
 * Headers cần có: Tiêu đề | Ngày bắt đầu | Giờ bắt đầu | Ngày kết thúc | Giờ kết thúc | Địa điểm | Mô tả | Email khách
 */
function createEventsFromSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Events To Create');
  if (!sheet) throw new Error('Sheet "Events To Create" not found');

  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const calendar = CalendarApp.getDefaultCalendar();

  const colTitle = headers.indexOf('Tiêu đề');
  const colStartDate = headers.indexOf('Ngày bắt đầu');
  const colStartTime = headers.indexOf('Giờ bắt đầu');
  const colEndDate = headers.indexOf('Ngày kết thúc');
  const colEndTime = headers.indexOf('Giờ kết thúc');
  const colLocation = headers.indexOf('Địa điểm');
  const colDesc = headers.indexOf('Mô tả');
  const colGuests = headers.indexOf('Email khách');
  const colEventId = headers.indexOf('Event ID'); // Cột lưu ID sau khi tạo

  let created = 0;
  let skipped = 0;

  for (let i = 1; i < data.length; i++) {
    const row = data[i];

    // Bỏ qua nếu đã có Event ID (đã tạo rồi)
    if (colEventId >= 0 && row[colEventId]) {
      skipped++;
      continue;
    }

    if (!row[colTitle] || !row[colStartDate]) continue;

    try {
      // Parse ngày giờ
      const startDate = new Date(row[colStartDate]);
      if (row[colStartTime]) {
        const timeParts = String(row[colStartTime]).match(/(d+):(d+)/);
        if (timeParts) {
          startDate.setHours(parseInt(timeParts[1]), parseInt(timeParts[2]), 0);
        }
      }

      const endDate = row[colEndDate] ? new Date(row[colEndDate]) : new Date(startDate.getTime() + 60 * 60 * 1000);
      if (row[colEndTime]) {
        const timeParts = String(row[colEndTime]).match(/(d+):(d+)/);
        if (timeParts) {
          endDate.setHours(parseInt(timeParts[1]), parseInt(timeParts[2]), 0);
        }
      }

      const options = {};
      if (colLocation >= 0 && row[colLocation]) options.location = row[colLocation];
      if (colDesc >= 0 && row[colDesc]) options.description = row[colDesc];

      // Thêm khách mời
      if (colGuests >= 0 && row[colGuests]) {
        options.guests = row[colGuests];
        options.sendInvites = true;
      }

      const event = calendar.createEvent(row[colTitle], startDate, endDate, options);

      // Lưu Event ID vào sheet
      if (colEventId >= 0) {
        sheet.getRange(i + 1, colEventId + 1).setValue(event.getId());
      }

      created++;
      Utilities.sleep(200); // Tránh rate limit Google Calendar API

    } catch (err) {
      console.error('Row ' + (i + 1) + ': ' + err.toString());
      if (colEventId >= 0) {
        sheet.getRange(i + 1, colEventId + 1).setValue('ERROR: ' + err.message);
      }
    }
  }

  const msg = 'Tạo ' + created + ' events. Bỏ qua ' + skipped + ' (đã tồn tại).';
  console.log(msg);
  SpreadsheetApp.getActiveSpreadsheet().toast(msg, 'Calendar Sync', 5);
}

Xử Lý Duplicate Events

/**
 * Kiểm tra event đã tồn tại chưa (tránh tạo trùng khi chạy lại script)
 */
function findExistingEvent(calendar, title, startDate) {
  // Tìm trong window ±1 ngày để tránh bỏ sót do timezone
  const searchStart = new Date(startDate.getTime() - 24 * 60 * 60 * 1000);
  const searchEnd = new Date(startDate.getTime() + 24 * 60 * 60 * 1000);

  const existingEvents = calendar.getEvents(searchStart, searchEnd);
  return existingEvents.find(e => {
    return e.getTitle() === title &&
           e.getStartTime().toDateString() === startDate.toDateString();
  });
}

Nhắc Nhở Deadline Tự Động

/**
 * Chạy hàng ngày lúc 8 giờ sáng — gửi email nhắc deadline 3 ngày tới
 */
function sendDeadlineReminders() {
  const today = new Date();
  const threeDaysLater = new Date(today.getTime() + 3 * 24 * 60 * 60 * 1000);

  const calendar = CalendarApp.getDefaultCalendar();
  const upcomingEvents = calendar.getEvents(today, threeDaysLater);

  if (upcomingEvents.length === 0) return;

  let emailBody = '<h2>Sự kiện sắp tới trong 3 ngày</h2><ul>';
  upcomingEvents.forEach(event => {
    const daysUntil = Math.ceil((event.getStartTime() - today) / (1000 * 60 * 60 * 24));
    emailBody += '<li><strong>' + event.getTitle() + '</strong> — ';
    emailBody += event.getStartTime().toLocaleString('vi-VN') + ' (' + daysUntil + ' ngày nữa)</li>';
  });
  emailBody += '</ul>';

  MailApp.sendEmail({
    to: Session.getActiveUser().getEmail(),
    subject: 'Nhắc việc: ' + upcomingEvents.length + ' sự kiện sắp tới',
    htmlBody: emailBody
  });
}

Use Cases Thực Tế

• Lịch làm việc nhân viên: Sync lịch cá nhân mỗi người vào sheet tổng hợp, manager xem được lịch toàn bộ team.
• Theo dõi deadline dự án: Tạo events Calendar từ Gantt chart trong Sheets, nhắc nhở tự động khi deadline đến gần.
• Đặt lịch hẹn: Khách hàng điền Google Form → Apps Script tự tạo Calendar event và gửi xác nhận.
• Báo cáo thời gian: Export events từ Calendar vào Sheets để tính tổng giờ làm việc theo dự án.

Kết hợp tính năng này với gửi email tự động để tạo hệ thống nhắc nhở lịch hẹn hoàn chỉnh cho doanh nghiệp.