Истории о Google Spreadsheets

Что такое диапазоны данных?

Диапазоны данных — это выпадающее меню в выбранной ячейке таблиц со списком заданных параметров.

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

Кто не знает, то диапазоны данных можно задавать следующим образом:

• Правый клик по ячейке.
• Выбрать «Проверка данных»
• Правила: «Значения из диапазона» или «Значения из списка»

Если у кого-то всё на английском — обратитесь к справке Google 🙂

Именованные диапазоны

Также в Google таблицах есть возможность именовать диапазоны. То есть, если вы раньше вводили в проверку данных диапазон вида A:A, то теперь, можно просто ввести «Диапазон1», и он будет знать, что это такое.

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

Чтобы создать именованный диапазон:

• «Данные» → «Именованные диапазоны»
• «Добавить диапазон»
• Вводим название диапазона и выбираем сам диапазон
• Используем имя диапазона в проверке данных вместо «A:A»
• Профит!

Динамические диапазоны данных

Подошли к самому интересному

Теперь, благодаря диапазонам, мы можем сделать целую выборку данных, которые будут зависеть друг от друга.

К примеру: Выбрать «Правая рука», чтобы в следующей строке появились предметы, доступные только под правую руку. Выбрать «Голова», чтобы в следующей строке появились предметы, доступные на голову и так далее.

Подготовка материала

Создаем вот такой список диапазонов:

 Далее, создаем именованные диапазоны для наших списков.

Далее — создаем ячейки с именованным диапазоном главной группы, то есть «ОСНОВНАЯ»

Вторую колонку оставляете пустой.

Переходим к написанию логики. Для этого нам потребуется Google script. Доступен он во вкладке «Инструменты» — «Редактор скриптов»

Называть его можно как угодно. Вставляем туда следующий код:

function depDrop_(range, sourceRange){
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
range.setDataValidation(rule);
}
function onEdit (){
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
if (aColumn == 1 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
}

Должен получиться следующий вид:

Сохраняем, жмем на «Выполнить» (кнопка, похожая на кнопку Play кассетных плееров) Код попросит разрешение, выдаем его и идём в наш документ с диапазонами.

Теперь, при выборе в основной группе, справа в колонке +1 будет появляться подгруппа с диапазоном данных (видео — для понимания)

Все, конечно, хорошо, удобно и работает, но! При удалении данных из столбца А, данные остаются в В. Что не круто.

Для того, чтобы данные из В очищались, нужно добавить в код следующий кусок скрипта:

if (aColumn == 1 && aCell.getValue() == “”) {
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
range.clearDataValidations();
range.clearContent();
return;
}

Получится общий вид вот такой:

Попробовать можно тут: https://docs.google.com/spreadsheets/d/1qFkhYbdoENslk7Vn0gLI8vn7JIqNpD0kkMHSpPuAOwI/edit#gid=0

Делайте копию — код и всё остальное будет работать.

Послесловие

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

Можно пойти дальше и добавить ещё несколько колонок с данными. Формулы, автозаполнение и так далее.

Google Docs и без GS очень гибкий и мощный инструментарий, а с GS так вообще можно неимоверные вещи творить.