Мои попытки написать скрипт в табличках Google. Связанные выпадающие списки

Привет!

Моя цель:
Сделать связанные выпадающие списки в таблицах Googl'a.

Постановка проблемы

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

Пути решения
У меня есть уже целых два готовых решения, которые я описал в своих видео:
Раз,
Два.

Первый способ создает каскадные (связанные, зависимые) списки в нескольких колонках. Но! Он нестабилен и выдает ошибки. О_о! Не то, что надо

Второй способ уже лучше, он работает стабильно. Но он дает только решение для 2-х колонок, но не нескольких, как ранее. Н-н-н-не то все-таки!

Ожидаю получить такой результат:

1. Колонок со списками несколько
2. Следующие в списке колонки меняют свои правила при изменении предыдущих
3. Реакция происходит достаточно быстро
4. При появлении новых строк в справке задним числом добавлять новые варианты проверки данных. Может быть. Многого хочу...

Так-так. Это все хорошо, но без скриптов не обойтись. А в них я не очень хорошо разбираюсь. Потому решил прописать сюда этапы своих мучений.

Исследования

Нашел по своему запросу справку Гулга на тему проверки данных, то есть Data Validation.

Тут пришла в голову идея провести несколько тестов, ради которых я и пишу эту статью. Итак:

function GetDataRules() {

// Log information about the data-validation rule for cell A1.
 var cell = SpreadsheetApp.getActive().getRange('G2');
 var rule = cell.getDataValidation();
 if (rule != null) {
   var criteria = rule.getCriteriaType();
   var args = rule.getCriteriaValues();
   Logger.log('The data-validation rule is %s %s', criteria, args);
 } else {
   Logger.log('The cell does not have a data-validation rule.')
 }
 }

Этот кусок кода я запустил для того, чтобы проверить правило проверки данных в ячейке [G2]. И там была проверка данных. Результат следующий:

The data-validation rule is VALUE_IN_RANGE [Range, true]

Ммм... Значит, мы имеем дело с типом критерия -- VALUE_IN_RANGE (значение из диапазона). Но я знаю, что есть и другие варианты настройки условного форматирования. Проведем еще пару экспериментов:

The data-validation rule is VALUE_IN_LIST [[Один, Два], true]

Это он дает при простом перечислении критериев через запятую.

The data-validation rule is NUMBER_BETWEEN [10.0, 100.0]

При выборе числа от 10 до 100.

The data-validation rule is TEXT_CONTAINS [лол]

Текст, содержащий лол)

The data-validation rule is TEXT_IS_VALID_EMAIL []

Текст, содержащий допустимый имейл-адрес. Интересно! 

В общем, этого достаточно. Кстати, перечень их всех вот. Логика процесса ясна. Но мне интересно знать кое-что: Как бы мне теперь при помощи скрипта записать в проверку данных свой список? Лучше еще массивом. Под мои задачи может подойти такой план:

1. На основании некой справки -- таблицы из листа \Справка/ или как-то так, -- вывести необходимый мне диапазон.
2. Превратить этот диапазон в массив, присвоив все его значения переменной.
3. Превратить все это в намертво зашитую проверку данных, которая делается скриптом.
4. Все это обернуть в обработчик событий, который будет мониторить изменения на главном рабочем листе.
5. Успех!

Не знаю я пока, как мне выполнить технически пункты 1, 2, 3 и 4. Неплохо для начала)

Начинать нужно с конца т.к. если в список нельзя загнать значения скриптом, значит, все прочие усилия ничего не будут стоить. Это самое важное! Поищу еще в справке.

Вот такой код требует значений из списка:

 // Установить проверку данных в клетке A1 с выбором "Ой" и "Ай" из выпадающего списка.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Ой', 'Ай']).build();
 cell.setDataValidation(rule);

Ок, а можно этот список задать массивом? Ответа на этот вопрос в справке не могу. Что же, спросим у Гугла! Ответ найден на одном из любимых сайтов. Предлагают код:

function dataValidation() {
  var array = ['1', '2', '3', '4', '5', '6'];
  var cell = SpreadsheetApp.getActive().getActiveCell();
  var rule = SpreadsheetApp
             .newDataValidation()
             .requireValueInList(array, true)
             .setAllowInvalid(false)
             .build();
  cell.setDataValidation(rule);
}

Попробуем! Получилось. Попробуем теперь узнать, как загонять данные из диапазона в массив. Говорят, что тут поможет свойство getRange которое приписывается листу.

var range = sheet.getDataRange(); // Получить диапазон
var values = range.getValues(); // Вписать диапазон в массив

Теперь самое время пофантазировать и соединить полученные знания.

Тесты

Пока, методом небольшого тыка, я написал следующий кусок кода:

function SmartDataValidation() {
  // Объявим несколько переменных для начала:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'способ3' // имя листа, где нужно осуществлуть проверку данных
  var LogSheet = 'Справка' // имя листа, где находится справка
  //--------------------------------------------------------------------------------------

  var ss = SpreadsheetApp.getActiveSpreadsheet(); // книга Гугль-Таблиц
  var ts = ss.getSheetByName(TargetSheet); // целевой лист
  var ls = ss.getSheetByName(LogSheet); // лист со справкой
  var range = ls.getRange("B2:B9"); // получить диапазон
  var values = range.getValues(); // вписать диапазон в массив
  var cell = ts.getRange("A1"); // куда запишем правило
  var rule = SpreadsheetApp
             .newDataValidation()
             .requireValueInList(values, true)
             .setAllowInvalid(false)
             .build();
  cell.setDataValidation(rule);
}

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

1. Пока неясно, как мне найти тот самый кусок рабочего диапазона из справки, который расскажет о необходимом для этой самой ячейки перечне допустимых значений. 
2. Еще я попутно узнал, что можно использовать именованные диапазоны. Это может быть полезно.

Смоделируем данные создав 3 уровня вложенности. Похоже на матрешку

Старшая матрешка №1: Части света (Америка, Европа, Азия...)
Средняя матрешка №2: Страны (США, Франция, Китай...)
Младшая матрешка №3: Города (Чикаго, Париж, Пекин)

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

Тогда возникает еще один важный вопрос: есть ли у обработчика событий переменная, которая укажет на адрес изменяемой клетки? Похоже, что ответ опять будет "да!". Попробуем теперь добиться такого пока бесполезного, но очень важного промежуточного результата:

• Промежуточная цель №1. 

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

Первое, что я обнаружил путем проб и ошибок, что скрипт обязательно должен начинаться так:
onEdit(event)

в скобках может быть любое слово, но имя скрипта обязано быть onRdit. Так-то!

И еще одна загвоздка. Исправлять ошибки в коде оказалось довольно сложно. Редактор скриптов не обладает достаточным функционалом, чтобы это приемлемо сделать, либо мне данный способ недоступен. Я пока нашел следующий выход из положения:
в код натыкал следующие строчки:
Browser.msgBox('0')
Browser.msgBox('1')
Browser.msgBox('2')
Browser.msgBox('3'),
разместил эти строчки в разных частях кода. И когда я запустил событие, то получил на экран сообщение "0", "1", а далее ничего не последовало. Из чего я сделал вывод, что ошибка была мою допущена до того места, где в коде стоит  Browser.msgBox('2'). Это помогло решить проблему. Здесь есть одно важное замечание: код НЕ СРАБОТАЕТ, если ты введешь команду в другом регистре, то есть он чувствителен к большим и маленьким символам -- кошмар!

После недолгих мучений и экспериментов с кодом, я получил результат:

function onEdit(event) {
  // Объявим несколько переменных для начала:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'способ3' // имя листа, где нужно осуществить проверку данных
  var LogSheet = 'Справка' // имя листа, где находится справка
  //--------------------------------------------------------------------------------------
  // 1. Отследить лист, на котором происходит событие
  var ts = event.source.getActiveSheet();
  var sname = ts.getName();
  //Browser.msgBox('0') // будем отслеживать ошибки!
  if (sname == TargetSheet) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // 2. Если имя листа совпало, то делаем дело...
    var ls = ss.getSheetByName(LogSheet); // лист со справкой
    var range = ls.getRange("B2:B9"); // получить диапазон
    var values = range.getValues(); // вписать диапазон в массив
    var cell = event.range.offset(0,1);
    var rule = SpreadsheetApp
    .newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();
    cell.setDataValidation(rule);      
  }
}

Промежуточная цель №1 -- выполнена! Теперь нужно сделать код еще умнее.

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

• Промежуточная цель №2. 

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

Пока что я имею код: var range = ls.getRange("B2:B9"). Этот код намертво зашивает диапазон В2:В9 внутрь процедуры. Но мне-то не это нужно. Для создания многоуровневых списков, исходная таблица так же должна содержать многоуровневую информацию. На примере это выглядеть будет так:

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

На этом этапе разработки мне еще не нужно знать, на каком из уровней матрешки я нахожусь: первом, втором, или третьем. Но, предположим, что на втором, то есть мы выбрали Часть света, и выбор этот -- Европа. Тогда ясно становится, что выбирать нужно из стран по списку:
Британия,
Франция,
Италия.

Тут же наблюдаю, что страны эти встречаются по несколько раз, то есть список не уникален. Выпишем задачи:

1. Узнать что выбрали Европу (на этом этапе как-бы уже знаем, что это -- Европа). Есть.
2. Узнать, на каком уровне матрешки мы находимся. Тоже типа знаем -- на втором.
3. Найти Европу на более высоком уровне, то есть на первом. Узнать, где начинается Европа: это строка 2, и где Европа заканчивается -- строка 8.
4. Узнать, какие значения встречаются на втором уровне со 2-ой по 8-ую строку.
5. Извлечь из списка только уникальные значения.

Этот алгоритм очевиден. Поиск путей его реализации хочу начать с конца: поиск уникальных. Знаю, что в доках есть функция UNIQUE, интересно, как ее использовать в скриптах? Нет? Нет! Нельзя в скриптах использовать стандартные функции Гугла. Придется идти в обход.

План пути в обход:

1. Найти в справке свободную колонку справа. Номер этой колонки может быть задан вручную, но не хочется добавлять множество входных параметров. Но в справке число колонок обязательно равняется числу уровней-матрешек. Так что номер свободной колонки справа можно и просто вычислить.
2. В эту колонку помещать наши уникальные значения. Синтаксис команды будет примерно: cell.setFormula('=UNIQUE(B2:B8)')
3. Всю команду прочитывать уже из этого уникального диапазона необходимых нам значений!

Уже наметился какой-то план и, похоже, он вполне реально выполним! Правда, есть еще пара нерешенных вопросов. Например, как узнать место "Европы" в списке? Можно через ту же самую уловку со свободной ячейкой, но это неизящно. Можно еще методом простого перебора всех вариантов. Это не очень умно, потому что диапазон может быть большой, и тогда успеем поспать, прежде чем скрипт выполнится.

И тут меня посетила одна мысль. А что, если сразу вписать в функцию UNIQUE так же функции поиска необходимого диапазона, а именно строчек 2 и 8 из второго столбца. На одном из форумов я прочел, что Доки понимают стиль [R1C1], если данные ввести через setFormula и это может мне пригодится! И к тому есть функция INDIRECT, которая так же понимает [R1C1]. Вот, что мне нужно! Формула будет выглядеть примерно так:

UNIQUE(INDIRECT("R" & MATCH("Европа";A1:A15;0) & "C" & "2" & ":" & "R" & COUNTIF(A1:A15;"Европа") & "C" & "2";0))

Да, эта формула слегка длинновата. Но это еще не все, ведь диапазон [А1:А15] в ней зашит намертво, хотя и его нам предстоит найти. Правда, его легко найти самом скриптом, не прибегая к помощи функции таблицы. Здесь налицо несколько переменных:

• Слово "Европа",
• Переменные внутри диапазона [А1:А15]:
• номер столбца -- 1
• номер последней ячейки -- 15, номер первой примем за 1 всегда.
• номер столбца -- 2, его можно найти исходя из столбца родительской матрешки ([А1:А15])

Вот и все! Теперь самое время начать кодить)

Формулу ввернуть в ячейку удалось, а чтобы эту формулу оттуда забрать, необходимо еще потрудиться. В итоге этого мне удалось добиться методом простого перебора всех ячеек. Наводит на мысль, что мой алгоритм несовершенен: почему бы просто не сделать перебором весь процесс нахождения списка. Но пока это рабочая версия кода, и я не хочу а этом этапе заходить слишком далеко.

Промежуточная цель №2 -- выполнена! Теперь нужно сделать код еще умнее.

• Промежуточная цель №3. 

• Добавить определитель уровня матрешки. 

Теперь пора наполнять код смыслом. Хоть сам алгоритм и не совершенен, но он работает. И пора двигаться дальше.

Тут же мне по ходу приходится и делать работу над ошибками. Я уже близко...

Исправив несколько ошибок, я добился результатов. Промежуточная цель №3 --  тоже выполнена!

Теперь хочу поработать над вопросом: что если? Пока у меня 2 "что если".

• Промежуточная цель №4. 

• Что если пользователь удалит / изменит сразу несколько ячеек? Исправить ошибки, если они появятся!
• Что если пользователь введет что-то в ячейку, где уже ранее было что-то введено. Будет конфликт версий. Нужно исправить.
• Если копируешь клетки, и вставляешь их ниже, то скрипт ведет себя странно. Нужно исправить.
• Первое: если несколько колонок
• Второе: если несколько строк.

Последняя промежуточная цель уже тянет на готовое решение. Начинается самое веселое, и, возможно, самое сложное.

... Много времени прошло...и, наконец-то, у меня родился код! Теперь, не долго думая, буду им делиться с людьми!

Вот сам код:

function onEdit(event) {
  //--------------------------------------------------------------------------------------
  // Обработчик событий, добавляющий Проверку данных по входным параметрам
  //--------------------------------------------------------------------------------------


  // Объявим несколько переменных для начала:
  //--------------------------------------------------------------------------------------

  var TargetSheet = 'способ3' // имя листа, где нужно осуществить проверку данных
  var LogSheet = 'Справка' // имя листа, где находится справка
  var NumOfLevels = 3 // количество уровней связанных выпадающих списков
  var lcol = 1; //номер самого левого столбца, а котором проверяются изменения; A=1, B=2 и т.д.
  var lrow = 2; //номер строки, начиная с которое правило будет действовать
  //--------------------------------------------------------------------------------------


// [ 01 ]. Отследить лист, на котором происходит событие
  var ts = event.source.getActiveSheet();
  var sname = ts.getName();
  //Browser.msgBox('0') // будем отслеживать ошибки!
  if (sname == TargetSheet) {
    // ss -- это текущая книга
    var ss = SpreadsheetApp.getActiveSpreadsheet();
 
// [ 02 ]. Если имя листа совпало, то делаем дело...
    var ls = ss.getSheetByName(LogSheet); // лист со справкой
 
// [ 03 ]. Определяем уровень матрешки
 
    //--------------На изменяемом листе--------------------------------
    var br = event.source.getActiveRange();
    var scol = br.getColumn(); // номер колонки, в которой произведено изменение
    var srow = br.getRow() // номер строки, в которой произведено изменение
    // Проверь, может строка не подходит
    if (srow >= lrow) {
    // номер уровня матрешки зависит от установок
    // необходимо из установок брать номер колонки, который равен
    // первому уровню матрешки
    var CurrentLevel = scol-lcol+2;
    // нужно скорректировать уровень матрешки на
    // размер изменяемой области
    var ColNum = br.getLastColumn() - scol + 1;
    CurrentLevel = CurrentLevel + ColNum - 1;
      // так же нужно скорректировать диапазон r
      if (ColNum > 1) {
        br = br.offset(0,ColNum-1);
      } // меняемый диапазон широкий
    var HeadLevel = CurrentLevel - 1; // уровень головной матрешки
 
      // тут уже необходимо по строкам разобрать
      var RowNum = br.getLastRow() - srow + 1;
      for (var j = 1; j <= RowNum; j++) {
      var r = br.getCell(j,1);
   
      var SearchText = r.getValue(); // искомый текст
    // если что-то вообще выбрано!
    if (SearchText != '') {
    // текущий уровень не должен превышать количество уровней, иначе
    // мы выходим за рамки требуемого диапазона
    if (CurrentLevel <= NumOfLevels ) {  
    //-------------------------------------------------------------------
 
// [ 04 ]. Нужо определить переменные для настройки списка с
    // даными для будущей проверки данных
    //---------------Это на листе со справкой--------------------------
    var lastRow = ls.getLastRow(); // получить адрес последней ячейки          
    // лепим код из коротких кусочков =)
    // повторяющаяся часть формулы...
    var IndCodePart = 'INDIRECT("R1C' + HeadLevel + ':R' + lastRow + 'C'
    IndCodePart = IndCodePart + HeadLevel + '";0)'
    // сама формула
    var code = '=UNIQUE(INDIRECT("R" & MATCH("';
    code = code + SearchText + '";';
    code = code + IndCodePart;
    code = code + ';0) & "C" & "' + CurrentLevel
    code = code + '" & ":" & "R" & COUNTIF(';
    code = code + IndCodePart;
    code = code + ';"' + SearchText + '") + MATCH("';
    code = code + SearchText + '";';
    code = code + IndCodePart;
    code = code + ';0) - 1';
    code = code + '& "C" & "' ;
    code = code + CurrentLevel + '";0))';
    // код получен! Теперь неплохо бы его вставить куда нужно
    var KudaCol = NumOfLevels + 2
    var KudaNado = ls.getRange(1, KudaCol);
      KudaNado.setFormulaR1C1(code);
    // теперь самое время получить необходимый массив
    var values = [];
    var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol);
    for (var i = 1; i <= lastRow; i++) {
    var currentValue = ChtoNado.getCell(i,1).getValue();
      if (currentValue != '') {
      values.push(currentValue);
      }
      else {
        i = lastRow
      }    
    }
    //-------------------------------------------------------------------
 
// [ 05 ]. Построить правило проверки данных
    var cell = r.offset(0,1);
    var rule = SpreadsheetApp
    .newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();
    cell.setDataValidation(rule);
    } // уровень матрешки подходит
  } // непустая ячейка
      else
      {
      // Browser.msgBox('Куда? Куда? Куда вы удалились?')
      // если справа есть колонки, то в них нужно убрать
      // лишнее условное форматирование и очистить их...
      if (CurrentLevel <= NumOfLevels ) {
        for (var i = 1; i <= NumOfLevels; i++) {
          var cell = r.offset(0,i);
          // посистить
          cell.clear({contentsOnly: true});
          // убрать условное форматирование
          cell.clear({validationsOnly: true});
        }
      } // уровень матрешки подходит
      } // пустая ячейка
  } // цикл перехода построчно
  } // номер строки
  } // рабочий лист
}

Эта история еще будет иметь свое продолжение, надеюсь. Продолжение следует...

И продолжение последовало! Я вернулся, нашел немного времени, чтобы слегка довести до ума данную работу. Я делаю сейчас 2 теста:

1. Тест на увеличение уровней матрешки. Пройден успешно. Я добавил к модели еще один уровень -- планету, добавив по традиции планету Татуин со своими частями света, странами и городами. Вышло ничего. В смысле, вышло, и всё работает как надо. Я доволен.
2. Тест на увеличение нагрузки. Это может быть самым больным вопросом. На всякий случай сделаю копию файла... Хочется не тратить время и сразу загрузить файл серьезно. 
1. 100 000 городов. Мрак. Таблица даже не выполняя скрипт, еле грузится. Сохраню так же статью на случай, если выкинет из браузера...Полчаса спустя я смог настроить уровни матрешки так, чтобы в каждом уровне не было слишком много вхождений, ибо я сразу убедился, что много вхождений проверка данных не выдержит. И, наконец, я начал запускать скрипт. Он работает! На каждое изменение тратит 3-5 сек., но работает исправо. Ты можешь сам это проверить, перейдя к документу по этой ссылке.
3. Сколько же строк максимально можно запихнуть в один уровень матрешки? Честно, я не знаю и пока нет желания это исследовать. Вся надежда на тебя =)

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