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

Question

[PiShim]

Добрый день. Не могу придумать ни скрипта, ни логики по защите данных ввода в ячейку. Суть задачи такова: Есть Диапазон А1:A100, в нем есть данные "Брак", "Экспорт". В диапазоне B1:B100 в каждой ячейке имеется выпадающий список с уже прописанными значениями. Нужно чтобы если в A1=Брак то B1 не могли вносить значения, ну и так далее по диапазонам.

Answer 1

[Владимир]

Настроить проверку данных по кастомной формуле типа "=NOT(A1="Брак")" и поставить галочку "Запрещать ввод данных". Плюс сообщение можно для полного счастья добавить с объяснением причин.

Comments

[PiShim]

Владимир Подобный вариант проделывал с обычным условием Если. Но проблема возникает при выпадающем списке. Как в него добавить формулу по условиям.

[Владимир]

PiShim, а выпадающий список сделан тоже через проверку данных или как-то иначе?

[PiShim]

Владимир, через проверку данных. Думал в скрипт засунуть, но в дальнейшем создавало бы проблемы с применением фильтров и сортировкой диапазона.

[Владимир]

PiShim, тогда, наверное, только ловить событие onEdit() и защищать соседнюю ячейку через Range.protect() или устанавливать новую проверку данных через Range.setDataValidation().

[PiShim]

Владимир, решил написать примерно то что вы обозначили выше, но состоящих из 2 диапазонов которые могут заполнить 3 по условию. Теперь надо построчно прописать изменения, то есть A1=Брак, то B1=Запрет
function somname(){//функция которая проверяет два массива данных и сводит один по условию
var cell = SpreadsheetApp.getActive().getRange('A46:A50');
var range = SpreadsheetApp.getActive().getRange('D45:D50');
var range2 = SpreadsheetApp.getActive().getRange('C45:C45');
var cell2 =SpreadsheetApp.getActive().getRange('B46').getValue();
if(cell2=="Брак"){
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range2).build();
cell.setDataValidation(rule);}
else{var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);};

}

[Владимир]

PiShim, а можно пример таблицы посмотреть? Навскидку мне кажется что код не оптимальный.

[PiShim]

Владимир, сейчас не могу, уехал от рабочего места. До этого пытался перевести функцию на события построчно
пока написал так, пока не работает времени нет доработать ...
function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var row = e.range.getRow();
var colum = e.range.getColumn();
var value = e.source.getActiveSheet.getRange(row, 1).getValue();
var range1 = SpreadsheetApp.getActive().getRange('D45:D50');
var range2 = SpreadsheetApp.getActive().getRange('C45:C45');
var cell2_1 = SpreadsheetApp.getActive().getRange("B46").getValue();
if (colum == 1 && value =="B46"){
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range1).build();
cell.setDataValidation(rule);

}

}

[Владимир]

PiShim, мне кажется очень сомнительным что у вас жестко прописаны ссылки на ячейки, вплоть до одной конкретной (B46). Обычно это не самая хорошая идея. Но нужно видеть таблицу чтобы что-то сказать.

[PiShim]

Владимир, https://docs.google.com/spreadsheets/d/1DgY8Y5XUZx...

ссылка на основной документ. Там где "Брак" (Столбец J) то в строках M,N соответствующие строки должны быть не доступны для редактирования.

[Владимир]

PiShim, пользуйтесь (я сделал для одного столбца пока что, но смысл должен быть ясен):

function onEdit(e) {
  const whatchedColumn = 10;
  const whatchedText = "Брак";
  const textIfWrong = "---";
  const offset = 3;
  let range = e.range;
  if (range.getColumn()==whatchedColumn && range.getValue()==whatchedText){
    const newRange = range.offset(0, offset);
    newRange.setValue(textIfWrong)
    const rule = SpreadsheetApp.newDataValidation().requireValueInList([textIfWrong]).setAllowInvalid(false);
    newRange.setDataValidation(rule);
  }
}

[PiShim]

Владимир, спасибо большое за рабочий скрипт. Правда спасибо, но у меня проблема что значения в ячейку подгружаются путем присваивания A6=A$473$ и так получается что скрипт не работает без события.

[Владимир]

Ну так в А473 тоже как-то значения вносятся же. В этот момент скрипт должен сработать. А дальше только правильно выставить оффсеты в коде.

[PiShim]

Владимир, В ячейку A6 у меня значения перетягиваются при помощи importrange с другого документа, далее из ячейки A6 значения при помощи формулы автоматически перетягиваются в ячейку A473, на этом моменте скрипт не хочет работать. Извиняюсь за наглость, может есть возможность связаться по видеосвязи?

Answer 2

[Владимир Юрченков]

Если речь о онлайн таблицах гугл, то просто выделите диапазон, далее ПКМ -> Защитить диапазон
Справа откроется меню и там можно настроить.

Если в обычном excel тоже могу подсказать.

Comments

[PiShim]

Владимир Юрченков открыл, но не нашел защита по условию. Мне нужно если в ячейке A1 есть Брак, то B1 блочилась для ввода данных и так по всему диапазону

[Владимир Юрченков]

Тогда вам нужно смотреть в сторону кастомных дополнений.