Cách khắc phục lỗi 'Protected Cell or Object' trong Google Apps Script

Thông báo lỗi

Bạn có thể sẽ gặp phải lỗi này khi tập lệnh của bạn cố gắng sử dụng setValue(), setValues(), hoặc clear() trên một vùng dữ liệu đã bị khóa. Nó trông như thế này:

Exception: You are trying to edit a protected cell or object. Please use the Sheets API to edit protected ranges.

Tại sao tập lệnh của bạn bị lỗi

Quyền truy cập chính là nguyên nhân ở đây. Ngay cả khi bạn là chủ sở hữu bảng tính, Google Apps Script vẫn tôn trọng các thiết lập "Vùng dữ liệu được bảo vệ" (Protected Range) dựa trên người hiện đang kích hoạt mã. Nếu người đang thao tác không có quyền chỉnh sửa thủ công các ô từ A1:C10, tập lệnh của bạn cũng sẽ không có quyền đó.

Điều này thường xảy ra theo một trong ba trường hợp sau:

• Simple Triggers: Bạn đang sử dụng onEdit(e). Các trình kích hoạt này luôn chạy dưới danh nghĩa người dùng hiện tại. Nếu một khách hoặc một người chỉnh sửa bị hạn chế kích hoạt tập lệnh, nó sẽ gặp lỗi ngay khi chạm vào một ô được bảo vệ.
• Custom Buttons: Người dùng nhấp vào một nút để chạy một hàm. Tập lệnh thực thi dưới tài khoản của họ. Nếu họ không thể nhập dữ liệu vào ô, tập lệnh cũng không thể ghi vào đó.
• Shared Libraries: Bạn đang gọi một hàm từ một thư viện trung tâm để cập nhật một trang tính, nhưng người dùng chưa được cấp quyền rõ ràng trong các thiết lập bảo vệ của trang tính cụ thể đó.

Cách khắc phục

Cách 1: Sử dụng Installable Triggers (Cách nhanh nhất)

Installable triggers (Trình kích hoạt có thể cài đặt) là một giải pháp thay thế mạnh mẽ vì chúng chạy dưới danh nghĩa người đã tạo trình kích hoạt, chứ không phải người đã chỉnh sửa trang tính. Nếu bạn (nhà phát triển) có quyền truy cập vào vùng dữ liệu được bảo vệ, tập lệnh sẽ chạy thành công cho tất cả những người khác.

• Mở dự án Apps Script của bạn.
• Nhấp vào biểu tượng Triggers (hình đồng hồ) ở thanh bên trái.
• Nhấp vào + Add Trigger ở góc dưới bên phải.
• Chọn hàm bạn muốn chạy (ví dụ: onFormSubmit).
• Đặt nguồn sự kiện thành From spreadsheet.
• Đặt loại sự kiện thành On edit hoặc On form submit.
• Lưu và ủy quyền cho tập lệnh. Bây giờ nó sẽ chạy với quyền hạn cao hơn của bạn.

Cách 2: Sử dụng Sheets API (Cách chuyên nghiệp)

Thông báo lỗi của Google gợi ý rõ ràng việc sử dụng Sheets API. "Dịch vụ nâng cao" (Advanced Service) này thường mạnh mẽ hơn khi xử lý các vùng dữ liệu bị hạn chế. Nó đặc biệt hữu ích nếu bạn đang cập nhật các tập dữ liệu lớn, chẳng hạn như nhập 500 hàng, nơi các lệnh tiêu chuẩn có thể bị chậm hoặc lỗi.

• Trong trình chỉnh sửa Apps Script, nhấp vào dấu + bên cạnh Services.
• Chọn Google Sheets API và nhấp vào Add.
• Cập nhật mã của bạn để sử dụng dịch vụ Sheets thay vì SpreadsheetApp.

// Phiên bản này bỏ qua các hạn chế giao diện người dùng tiêu chuẩn
function updateProtectedRange() {
  const ssId = SpreadsheetApp.getActive().getId();
  const range = "Sheet1!A1"; 
  const valueRange = {
    values: [["Updated via API"]]
  };
  
  Sheets.Spreadsheets.Values.update(valueRange, ssId, range, {
    valueInputOption: "USER_ENTERED"
  });
}

Cách 3: Quy trình "Mở khóa-Ghi-Khóa lại"

Nếu tập lệnh của bạn có quyền cấp chủ sở hữu, bạn có thể tạm thời dỡ bỏ bảo vệ, thực hiện cập nhật và sau đó khôi phục lại khóa. Đây là một mô hình phổ biến cho các tác vụ bảo trì tự động.

function writeToProtectedRange() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Data");
  const range = sheet.getRange("A1:B10");
  
  // Xác định các bảo vệ hiện có
  const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  let myProtection = protections.find(p => p.getRange().getA1Notation() == "A1:B10");

  // Tạm thời gỡ bỏ khóa
  if (myProtection) {
    myProtection.remove();
  }

  // Thực hiện chỉnh sửa
  range.setValue("Success");

  // Áp dụng lại bảo vệ ngay lập tức
  const newProtection = range.protect().setDescription("Restored by Script");
  newProtection.removeEditors(newProtection.getEditors()); 
  newProtection.addEditor("admin@example.com");
}

Xác minh kết quả

Đừng chỉ tin vào thông báo của tập lệnh. Để xác nhận nó hoạt động, bạn cần kiểm tra với tư cách là một người dùng bị hạn chế:

• Mở trang tính bằng một tài khoản email "thử nghiệm" phụ.
• Đảm bảo tài khoản thử nghiệm này bị chặn chỉnh sửa vùng dữ liệu theo cách thủ công.
• Kích hoạt tập lệnh (nhấp vào nút của bạn hoặc chỉnh sửa một ô).
• Nếu ô được cập nhật và không có lỗi hiện ra, bạn đã giải quyết được vấn đề về quyền.

Mẹo phòng ngừa

Việc quản lý quyền trong Google Sheets có thể trở nên rắc rối khi nhóm của bạn phát triển. Nếu bạn cũng quản lý máy chủ web, bạn sẽ biết rằng quyền truy cập tệp cũng phức tạp không kém. Tôi thường sử dụng Unix Permissions Calculator để giúp hình dung logic chmod. Mặc dù Sheets sử dụng giao diện người dùng trực quan, nhưng khái niệm Chủ sở hữu (Owner) so với Người chỉnh sửa (Editor) là rất giống nhau.

Để tránh những rắc rối này trong tương lai:

• Sử dụng Installable Triggers bất cứ khi nào nhiều người cần kích hoạt tập lệnh trên một trang tính bị khóa.
• Kiểm tra xem người dùng có ít nhất quyền "Editor" đối với tệp hay không; tập lệnh không thể bỏ qua hạn chế "View Only" trừ khi được triển khai dưới dạng Ứng dụng web chạy dưới danh nghĩa "Me".
• Lưu nhật ký về các vùng dữ liệu được bảo vệ để tránh sự chồng chéo vô tình trong mã của bạn.