Cách Đồng bộ Google Sheet với Calendar bằng Google Apps Script

Download file mẫu tại đây: https://bit.ly/45wTKaE

📌 Lưu ý:

• Ngày nhập ở cột C và D phải đúng định dạng (vd: 29/09/2025).

• Nếu chỉ cần 1 ngày duy nhất → nhập ngày bắt đầu và ngày kết thúc giống nhau.

Cách sử dụng

Tạo sự kiện

• Điền đủ thông tin từ cột B → E.

• Sau vài giây, hệ thống sẽ tự động đồng bộ sang Google Calendar.

• Cột F (Event ID) sẽ tự động hiện mã sự kiện.

Chỉnh sửa sự kiện

• Khi chỉnh sửa bất kỳ ô nào trong cột B–E → Google Calendar sẽ tự động cập nhật theo.

Xóa sự kiện

• Xóa nội dung tiêu đề hoặc ngày → sự kiện sẽ tự động bị xóa khỏi Google Calendar.

• Nếu xóa nguyên hàng trong Sheet → sự kiện tương ứng cũng sẽ bị xóa.

Nhắc nhở qua email

• Trước 15 ngày & 7 ngày khi đến ngày kết thúc (cột D), hệ thống sẽ gửi email nhắc nhở.

• Email sẽ gửi về danh sách người nhận được cấu hình trong sheet Email_List (hoặc tất cả email đã được chia sẻ quyền trong Calendar, tùy theo setup).

• Email được gửi giống như một email thông thường, có:

  • Tiêu đề sự kiện

  • Ngày bắt đầu – Ngày kết thúc

  • Ghi chú

Yêu cầu cài đặt ban đầu

Dán toàn bộ code Apps Script vào Google Apps Script của file Sheet (chọn Tiện ích -> App Script)

/********** CẤU HÌNH **********/
const CALENDAR_ID = "ID LỊCH CỦA BẠN";
const HEADER_ROW = 1;

/********** HÀM TIỆN ÍCH **********/
// Tìm cột "Event ID" theo header (case-insensitive). Nếu không tìm thấy trả về 6 (cột F).
function findEventIdCol(sheet) {
  var lastCol = Math.max(6, sheet.getLastColumn());
  var headers = sheet.getRange(HEADER_ROW, 1, 1, lastCol).getValues()[0];
  for (var i = 0; i < headers.length; i++) {
    if (!headers[i]) continue;
    var h = String(headers[i]).toLowerCase().replace(/\s+/g, '');
    if (h === 'eventid' || h === 'eventid' || h.indexOf('eventid') !== -1 || String(headers[i]).toLowerCase().indexOf('event id') !== -1) {
      return i + 1;
    }
  }
  // mặc định F
  return 6;
}

// Parse ngày "ngày-tháng-năm" (dd/MM/yyyy hoặc yyyy-MM-dd) hoặc Date object
// Trả về Date object (00:00:00 ngày đó) theo timezone của spreadsheet
function parseDateOnly(v) {
  if (v === null || v === undefined || v === "") return null;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tz = ss.getSpreadsheetTimeZone();

  // Nếu đã là Date object, lấy year/month/day theo timezone của sheet
  if (v instanceof Date) {
    if (isNaN(v.getTime())) return null;
    var Y = parseInt(Utilities.formatDate(v, tz, "yyyy"), 10);
    var M = parseInt(Utilities.formatDate(v, tz, "MM"), 10) - 1;
    var D = parseInt(Utilities.formatDate(v, tz, "dd"), 10);
    return new Date(Y, M, D, 0, 0, 0);
  }

  // Nếu là chuỗi hiển thị (vd "29/09/2025" hoặc "2025-09-29")
  var s = String(v).trim();
  // thử dd/MM/yyyy or d/M/yyyy
  var m = s.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/);
  if (m) {
    var day = parseInt(m[1], 10);
    var month = parseInt(m[2], 10) - 1;
    var year = parseInt(m[3], 10);
    return new Date(year, month, day, 0, 0, 0);
  }
  // thử yyyy-MM-dd (ISO date)
  var m2 = s.match(/^(\d{4})[\/\-](\d{1,2})[\/\-](\d{1,2})$/);
  if (m2) {
    var year2 = parseInt(m2[1], 10);
    var month2 = parseInt(m2[2], 10) - 1;
    var day2 = parseInt(m2[3], 10);
    return new Date(year2, month2, day2, 0, 0, 0);
  }

  // fallback: try native parse
  var d = new Date(s);
  if (!isNaN(d.getTime())) {
    // normalize to date-only in sheet tz
    var Y = parseInt(Utilities.formatDate(d, tz, "yyyy"), 10);
    var M = parseInt(Utilities.formatDate(d, tz, "MM"), 10) - 1;
    var D = parseInt(Utilities.formatDate(d, tz, "dd"), 10);
    return new Date(Y, M, D, 0, 0, 0);
  }

  return null;
}

/********** XỬ LÝ 1 HÀNG **********/
function processRow(sheet, row, eventIdCol) {
  var calendar = CalendarApp.getCalendarById(CALENDAR_ID);
  if (!calendar) {
    writeDebug("Không tìm thấy calendar: " + CALENDAR_ID);
    return;
  }

  // Lấy dữ liệu: B=2, C=3, D=4, E=5
  var title = sheet.getRange(row, 2).getDisplayValue().toString().trim();
  var startRaw = sheet.getRange(row, 3).getValue();
  var endRaw = sheet.getRange(row, 4).getValue();
  var note = sheet.getRange(row, 5).getDisplayValue();
  var eventIdCell = sheet.getRange(row, eventIdCol);
  var existingId = eventIdCell.getValue();

  var startDate = parseDateOnly(startRaw);
  var endDate = parseDateOnly(endRaw);

  // Nếu title hoặc endDate thiếu -> xóa event (nếu có) và clear EventID
  if (!title || !endDate) {
    if (existingId) {
      try {
        var old = calendar.getEventById(String(existingId));
        if (old) old.deleteEvent();
      } catch (e) {
        writeDebug("Xóa event lỗi (maybe already removed): " + e);
      }
      eventIdCell.clearContent();
    }
    return;
  }

  // chuẩn bị end exclusive (all-day events require end = next day)
  var endExclusive = new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate());
  endExclusive.setDate(endExclusive.getDate() + 1);

  // Tạo (hoặc recreate) event: để đơn giản & chính xác cho all-day, xóa event cũ (nếu có) rồi tạo mới
  try {
    if (existingId) {
      try {
        var oldEv = calendar.getEventById(String(existingId));
        if (oldEv) oldEv.deleteEvent();
      } catch (e) {
        // ignore if not found
      }
    }

    var newEv = calendar.createAllDayEvent(title, startDate, endExclusive, {description: note || ""});
    try { newEv.removeAllReminders(); } catch(e){}
    newEv.addPopupReminder(24 * 60); // popup 1 day
    newEv.addEmailReminder(24 * 60); // email 1 day
    eventIdCell.setValue(newEv.getId());
  } catch (err) {
    writeDebug("processRow error row " + row + " : " + err);
  }
}

/********** XỬ LÝ KHI CHỈNH SỬA - sử dụng installable trigger On edit **********/
function onEditInstallable(e) {
  if (!e || !e.range) return;
  var range = e.range;
  var sheet = range.getSheet();

  // chỉ xử lý cột B..E (2..5)
  var startCol = range.getColumn();
  var endCol = startCol + range.getNumColumns() - 1;
  if (endCol < 2 || startCol > 5) return;

  var startRow = range.getRow();
  var numRows = range.getNumRows();
  var eventIdCol = findEventIdCol(sheet);

  for (var i = 0; i < numRows; i++) {
    var row = startRow + i;
    if (row < 2) continue;
    processRow(sheet, row, eventIdCol);
  }
}

/********** Hàm đồng bộ toàn bộ (chạy thủ công lần đầu) **********/
function syncAll() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return; // không có data
  var eventIdCol = findEventIdCol(sheet);
  for (var r = 2; r <= lastRow; r++) {
    processRow(sheet, r, eventIdCol);
  }
}

/********** XỬ LÝ KHI XÓA HÀNG - onChange (installable) **********/
function onChangeInstallable(e) {
  if (!e) return;
  try {
    if (e.changeType && (e.changeType === 'REMOVE_ROW' || e.changeType === 'REMOVE_COLUMN')) {
      cleanupOrphanEvents();
    }
  } catch (err) {
    writeDebug("onChangeInstallable error: " + err);
  }
}

// Xóa các event mà vẫn có trong calendar nhưng đã mất EventID trong sheet
function cleanupOrphanEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cal = CalendarApp.getCalendarById(CALENDAR_ID);
  var sheetId = ss.getId();
  var lastRow = sheet.getLastRow();
  var existing = {};
  if (lastRow >= 2) {
    var idVals = sheet.getRange(2, findEventIdCol(sheet), lastRow - 1, 1).getValues();
    idVals.forEach(r => { if (r[0]) existing[String(r[0])] = true; });
  }
  var now = new Date();
  var events = cal.getEvents(new Date(now.getFullYear()-2,0,1), new Date(now.getFullYear()+5,11,31));
  events.forEach(ev => {
    var desc = ev.getDescription() || "";
    if (desc.indexOf(META_MARKER) !== -1 || desc.indexOf("sheetId=" + sheetId) !== -1) {
      var id = ev.getId();
      if (!existing[id]) {
        try { ev.deleteEvent(); } catch(e){ writeDebug("cleanup delete err: "+e); }
      }
    }
  });
}

/********** GỬI EMAIL NHẮC NHỞ **********/

// Lấy danh sách email từ sheet "Email_List"
function getEmailRecipients() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Email_List");
  if (!sh) return []; // nếu không có sheet thì bỏ qua
  var vals = sh.getRange(1,1,sh.getLastRow(),1).getValues();
  var emails = vals.map(r => String(r[0]).trim()).filter(e => e);
  return emails;
}

function sendReminderEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

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

  var recipients = getEmailRecipients();
  if (recipients.length === 0) {
    writeDebug("Không có email nào trong Email_List để gửi nhắc nhở");
    return;
  }

  for (var r = 2; r <= lastRow; r++) {
    var title = sheet.getRange(r, 2).getDisplayValue();
    var endRaw = sheet.getRange(r, 4).getValue();
    var endDate = parseDateOnly(endRaw);
    if (!endDate) continue;

    var diffDays = Math.floor((endDate - today) / (1000*60*60*24));
    if (diffDays === 15 || diffDays === 7) {
      var note = sheet.getRange(r, 5).getDisplayValue();
      var subject = "⏰ Nhắc nhở: Sự kiện \"" + title + "\" sắp hết hạn";
      var body = "Xin chào,\n\n" +
                 "Sự kiện: " + title + "\n" +
                 "Ngày kết thúc: " + Utilities.formatDate(endDate, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy") + "\n" +
                 (note ? "Ghi chú: " + note + "\n" : "") +
                 "\nVui lòng kiểm tra và xử lý kịp thời.\n\n" +
                 "— Thông báo từ An Trần Digital Calendar";
      try {
        MailApp.sendEmail({
          to: recipients.join(","),
          subject: subject,
          body: body
        });
        writeDebug("Đã gửi nhắc nhở cho sự kiện hàng " + r + " tới: " + recipients.join(","));
      } catch(e) {
        writeDebug("Lỗi gửi mail hàng " + r + ": " + e);
      }
    }
  }
}

/********** HỖ TRỢ / DEBUG **********/
function authorize() {
  // chạy 1 lần để cấp quyền
  var cal = CalendarApp.getCalendarById(CALENDAR_ID);
  if (!cal) throw new Error("Không tìm thấy calendar: " + CALENDAR_ID);
  Logger.log("Authorized: " + cal.getId());
}
function writeDebug(msg) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("_sync_debug");
    if (!sh) sh = ss.insertSheet("_sync_debug");
    sh.insertRowBefore(2);
    sh.getRange(2,1).setValue(new Date());
    sh.getRange(2,2).setValue(msg);
  } catch(e){ Logger.log("writeDebug failed: "+e); }
}

Chạy hàm authorize() để cấp quyền truy cập Calendar & Gmail.

Vào menu Extensions → Triggers và thiết lập:

• onEditInstallable → Trigger type: On edit
• onChangeInstallable → Trigger type: On change
• sendReminders → Trigger type: Time-driven → Day counter (set chọn giờ mà bạn muốn gửi mail) phần này bạn có thể set tùy thích theo nhu cầu của bạn.

Một số lưu ý

• Chỉ nhập dữ liệu từ cột B đến E, cột F hệ thống tự xử lý.

• Nếu thấy không đồng bộ → chạy thủ công hàm syncAll() trong Apps Script.

• Sheet _sync_debug sẽ hiển thị log lỗi (nếu có).

• Người nhận email cần nằm trong danh sách được cấu hình hoặc có quyền truy cập calendar.

Đồng bộ Google Sheet với Calendar bằng Google Apps Script là giải pháp tối ưu giúp bạn quản lý lịch một cách tự động, chính xác và linh hoạt mà không cần phụ thuộc vào thao tác thủ công. Khi dữ liệu được chuẩn hóa trên Google Sheet và logic đồng bộ được thiết kế hợp lý, bạn hoàn toàn có thể kiểm soát, cập nhật và mở rộng hệ thống lịch làm việc một cách chuyên nghiệp, phù hợp cho cả cá nhân lẫn doanh nghiệp. Đây không chỉ là một giải pháp kỹ thuật, mà còn là bước đi thông minh trong hành trình tối ưu quy trình và nâng cao hiệu suất vận hành lâu dài.