Как установить защиты на разделы листа как при запуске, так и на ячейках после редактирования пользователем.

Вопрос или проблема

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var editedCell = e.range;
  if (editedCell.getColumn() == 4 && editedCell.getRow() > 1) {
    var link = editedCell.getValue();
    
    // Если вставлена ссылка (непустая ячейка)
    if (link != "") {
      var row = editedCell.getRow();
      var currentTime = new Date(); // Получить текущую дату и время
      
      // Установить отметку времени в колонке F
      sheet.getRange(row, 6).setValue(currentTime);
      
      // Установить формулу в колонке G для вычисления прошедшего времени
      var elapsedTimeFormula="=IF(F" + row + '<>"", NOW() - F' + row + ', "")';
      sheet.getRange(row, 7).setFormula(elapsedTimeFormula);

      // Защитить конкретную ячейку в колонке D, куда была вставлена ссылка
      var cellToProtect = sheet.getRange(row, 4);
      var protection = cellToProtect.protect().setDescription('Ячейка заблокирована после добавления ссылки');
      //var me = Session.getEffectiveUser();
      protection.removeEditors(protection.getEditors());
      //if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      //}
    }
  }
}

function onOpen(e) {
  var sheet = e.source.getActiveSheet();
  var protection = sheet.protect().setDescription('Защищенные заголовки и столбцы по умолчанию');
  var unprotectedLeftOfColumns = sheet.getRange('A2:H1000');
  var unprotectedRightOfColumns = sheet.getRange('J2:N1000');
  protection.setUnprotectedRanges([unprotected]);

  // Убедитесь, что текущий пользователь является редактором, прежде чем удалять других. В противном случае, если права редактирования пользователя
  // поступают от группы, скрипт вызывает исключение при удалении группы.
  //var me = Session.getEffectiveUser(); [Нет никаких причин, почему это не работает для двух пользователей на одном компьютере]
  //protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  //if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  //}

Ответ или решение

Чтобы настроить защиту определённых секций листа Google Sheets при его открытии и в случае редактирования ячеек, вам потребуется реализовать два триггера: onOpen(e) и onEdit(e). Давайте рассмотрим оба сценария более подробно.

Защита при открытии листа (onOpen)

Целью данной функции является установка защитных настроек для заголовков и определённых колонок. Обратите внимание, что необходимо указывать диапазоны, которые не должны быть защищены.

function onOpen(e) {
  var sheet = e.source.getActiveSheet();

  // Установим защиту для всего листа
  var protection = sheet.protect().setDescription('Защита заголовков и колонок');

  // Разрешаем редактирование пользователям, указываем диапазоны, которые не защищены
  var unprotectedRanges = [sheet.getRange('A2:H1000'), sheet.getRange('J2:N1000')];
  protection.setUnprotectedRanges(unprotectedRanges);

  // Удаляем возможность редактирования для всех пользователей, кроме владельца и указанных редакторов
  protection.removeEditors(protection.getEditors());
  protection.setDomainEdit(false); // Запрещаем редактирование для всей доменной группы
}

Защита после редактирования ячейки (onEdit)

Эта функция срабатывает каждый раз, когда пользователь редактирует ячейку. Здесь нужно защищать ячейку в колонке D (после вставки ссылки) и фиксировать дату изменения, а также ввести формулу для расчёта времени.

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var editedCell = e.range;

  // Проверяем, если редактируется колонка D и не первая строка
  if (editedCell.getColumn() == 4 && editedCell.getRow() > 1) {
    var link = editedCell.getValue();

    // Если ячейка не пустая
    if (link != "") {
      var row = editedCell.getRow();
      var currentTime = new Date(); // Получаем текущее время

      // Устанавливаем временную метку в колонке F
      sheet.getRange(row, 6).setValue(currentTime);

      // Устанавливаем формулу в колонке G для вычисления времени
      var elapsedTimeFormula = `=IF(F${row}<>"", NOW()-F${row}, "")`;
      sheet.getRange(row, 7).setFormula(elapsedTimeFormula);

      // Защищаем ячейку в колонке D
      var cellToProtect = sheet.getRange(row, 4);
      var protection = cellToProtect.protect().setDescription('Ячейка заблокирована после добавления ссылки');

      // Удаляем всех редакторов и запрещаем редактирование для домена
      protection.removeEditors(protection.getEditors());
      protection.setDomainEdit(false); // Отмена редактирования для всех пользователей
    }
  }
}

Примечания по работе скриптов

1. Полномочия: Убедитесь, что у вашего скрипта есть необходимые привилегии для удаления редакторов. Зачастую, если у пользователя имеются разрешения на редактирование через группу, это может вызвать ошибку.

2. Ошибки: Если возникают ошибки, попробуйте их отследить, добавив в скрипты логи через Logger.log(), чтобы увидеть, на каком этапе возникает сбой.

3. Эффективность: Защита может занять некоторое время, особенно на больших листах, поэтому учитывайте скорость и производительность.

4. Тестирование: Проведите тщательное тестирование всех изменений с разными пользователями с различными уровнями доступа.

Для оптимизации вашей работы рекомендуется использовать инкапсуляцию защитных функций — создайте дополнительные вспомогательные функции, которые будут обработкой ваших логик, чтобы избежать дублирования кода.

Используя предложенные методы, вы сможете добиться gewünschte защиты при запуске и при редактировании ваших Google Sheets, обеспечив безопасность данных при работе с несколькими пользователями.