Вопрос или проблема
Используя Google таблицы, я хотел бы скрыть определенный набор столбцов, если установлен определенный флажок. Я хочу сделать это для нескольких наборов столбцов и нескольких флажков. Флажки устанавливаются на основе формулы, которая ссылается на другую ячейку.
Например:
Если флажок A1 установлен, я хочу скрыть столбцы I:L. Если флажок A2 установлен, я хочу скрыть столбцы N:Q и так далее. Кроме того, все флажки в столбце A устанавливаются или снимаются в зависимости от того, имеется ли текст в ячейке в той же строке столбца B.
У меня написан следующий скрипт:
function onEdit(e) {
let col = 0;
switch(e.range.getA1Notation()) {
case "A1":
col = 9;
break;
case "A2":
col = 14;
break;
case "A3":
col = 19;
break;
default:
return;
}
if( e.value === "FALSE" ) {
e.source.getActiveSheet().hideColumns(col,4)
}
else {
e.source.getActiveSheet().showColumns(col,4)
}
}
Проблема с этим скриптом заключается в том, что он зависит от изменения ячеек с флажками на “Ложь” при снятии флажка. Поскольку флажок имеет формулу для установки и снятия, это не вызывает срабатывания скрипта. Если я удаляю формулу флажка и вручную устанавливаю или снимаю его, скрипт работает должным образом. Текст в столбце B заполняется в зависимости от выбора в C1. Я хотел бы вызывать этот скрипт скрытия столбцов каждый раз, когда переключается выпадающий список.
Чтобы подвести итоги:
В C1 выбран выпадающий список.
Столбец B заполняется в зависимости от выбора из выпадающего списка.
Флажки в столбце A устанавливаются, если в ячейках столбца B есть текст. Это выполняется автоматически с помощью формулы, установленной для каждой ячейки флажка.
Если флажок в A1 снят, я хочу, чтобы столбцы I:L были скрыты. Если флажок в A2 снят, я хочу, чтобы столбцы N:Q были скрыты, и так далее.
Я хочу, чтобы это вызывалось каждый раз, когда выбирается другой выпадающий список.
Спасибо!
Простые триггеры срабатывают только при активации пользователем:
Триггер onEdit(e) запускается автоматически, когда пользователь изменяет значение любой ячейки в таблице.
Таким образом, диапазон события редактирования e.range никогда не будет ячейкой в столбце A, если они (не) отмечены автоматически через формулу. Кроме того, ваш код выглядел неплохо.
Вам просто нужно проверить, что редактируемая ячейка действительно C1, в этом случае вы можете пройтись по каждому флажку и посмотреть, установлены ли они, и (не) скрыть столбцы соответственно.
Следующий код должен сделать то, что вы хотите:
function onEdit(e) {
let col = 9;
const sheet = e.source.getActiveSheet();
if (e.range.getA1Notation() !== "C1") {
return;
}
for (let i=0; i<3; i++, col+=5) {
sheet.getRange(i+1, 1).getValue() ?
sheet.showColumns(col, 4) :
sheet.hideColumns(col, 4);
}
}
Как это работает:
- если редактируемая ячейка не C1, выполнение прекращается;
- если это так, проверяются все ячейки в столбце A с 1 по 3 строки (но вы, конечно, можете изменить это);
- для каждой ячейки в столбце A скрываются или открываются 4 столбца - в зависимости от ее значения, с использованием тернарного оператора: начиная со столбца 9 для A1 и шагом 5 далее.
Ссылки
Ответ или решение
Для решения вашей задачи в Google Sheets можно использовать Google Apps Script. Ваша проблема заключается в том, что триггер onEdit(e) срабатывает только в случае ручного редактирования, а не когда значения изменяются автоматически через формулы. Тем не менее, вы можете создать специальный триггер, который будет запускаться при изменении значения в ячейке C1, и будет проверять состояние флажков в столбце A.
Вот пример кода, который решает вашу задачу:
function onEdit(e) {
const sheet = e.source.getActiveSheet();
// Проверяем, было ли изменено значение в ячейке C1
if (e.range.getA1Notation() !== "C1") {
return;
}
// Основной цикл для проверки состояний чекбоксов и скрытия/отображения колонок
for (let i = 0; i < 3; i++) { // Предполагается, что у вас 3 чекбокса (A1, A2, A3)
const checkboxValue = sheet.getRange(i + 1, 1).getValue(); // Получаем значение чекбокса
const colIndex = 9 + i * 5; // Индекс колонки, начинающийся с 9 и увеличивающийся на 5 для каждого чекбокса
if (checkboxValue) {
sheet.showColumns(colIndex, 4); // Показываем колонки, если чекбокс отмечен
} else {
sheet.hideColumns(colIndex, 4); // Скрываем колонки, если чекбокс не отмечен
}
}
}Как это работает:
-
Проверка изменения в C1: Скрипт проверяет, изменено ли значение именно в ячейке C1. Если это не так, он просто завершает свою работу.
-
Цикл по чекбоксам: Скрипт проходит через три ячейки (A1, A2 и A3). Вам нужно будет скорректировать число итераций в зависимости от количества чекбоксов, которые вы используете.
-
Скрытие и отображение колонок: Для каждого чекбокса проверяется его состояние. Если он отмечен, соответствующие колонки показываются, если нет — скрываются. Ширина скрываемых/показываемых колонок задается как 4.
Использование:
Чтобы использовать этот скрипт:
- Откройте Google Sheets.
- Перейдите в "Расширения" -> "Apps Script".
- Удалите код по умолчанию и вставьте приведенный выше код.
- Сохраните и закройте редактор Apps Script.
Теперь, каждый раз, когда вы будете изменять значение в ячейке C1, скрипт проверит состояние чекбоксов в столбце A и соответственно будет скрывать или отображать нужные колонки.
Примечание:
Имейте в виду, что данное решение работает только для тех случаев, когда значение в C1 эффективно изменяет состояние текста в столбце B, что в свою очередь влияет на состояние чекбоксов. Убедитесь, что формулы для чекбоксов корректно настроены и обновляют свои значения в зависимости от текста в столбце B.