воскресенье, 22 октября 2017 г.

Связные списки в google таблицах

Связные списки google - нетривиальная задача. Она не решается с помощью формул google таблиц (я надеюсь, что это только пока). Необходимо обращение к скриптам google.


Подготавливаем лист таблицы из которого будут браться значения для списков. Назовём, например, help.


заполняем нужными нам данными


Таблица

Создаём лист в котором будет выполняться выбор Глав, Параграфов, Разделов.


В Редакторе скриптов вставляем скрипт

var sheethelp  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("help");

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var dataRange = sheethelp.getDataRange();

var data = dataRange.getValues();

var deep=3; // вложенность списков (количество уровней)



function onEdit(e){



  if (sheet.getName()=='help'){

    return;

  }

 var Column = e.range.getColumn();

 var Row = e.range.getRow();

  var arrayfirst = [];

  if (Column==1){

  for (var i in data){

    if (data[i][0]!=""){

      arrayfirst.push(data[i][0]);

    }

  }

 var rule1 = SpreadsheetApp.newDataValidation().requireValueInList(arrayfirst).build();

 var cell1 = sheet.getRange(Row, 1);

     cell1.setDataValidation(rule1);

  }



 for (var i=2; i<=deep;i++){

 var cell = sheet.getRange(Row, i-1);

 var Value = cell.getValue();

 var rule2 = SpreadsheetApp.newDataValidation().requireValueInList(getList(i,Value)).build();

 var cell2 = sheet.getRange(Row, i);

 cell2.setDataValidation(rule2);

  }



  for(var j=Column;j<=deep;j++){

  var cell11=sheet.getRange(Row, j+1).clearContent();

  }

}



function getList(Col,Name){

   var arrayList = [];

    var k=0;

    for (var i in data){

      if (data[i][Col-2]==Name){

        var k=i;break;

      }

    }

    var l=0;

    for (var i = parseInt(k)+1; i < data.length; i++){

      if (data[i][Col-2]!=""){

        l=i;break;

      }   

    }

      if (l==0){

      l=data.length;

    }

    for (var i=k; i<l;i++){

       if (data[i][Col-1]!=""){

      arrayList.push(data[i][Col-1]);

    }

    }

      return arrayList;

  }

Сохраняем.
В столбец Глава пытаемся что-нибудь напечатать и радуемся появлению выпадающих связных списков. 

Переделываем скрипт под себя и свои данные.

16 комментариев:

  1. Привет Роман
    Воспользовался твоим скриптом для создания двух уровневого списка в гугл таблице
    Он работает - это круто!
    НО для лично моей задачи немного его надо отредактировать, но я не понимаю где и что надо отредактировать, чтобы он работал как надо в моей таблице. Напиши свои контакты (телеграмм или фейсбук), чтобы с тобой можно было связаться и ты помог разобраться в работе скрипта.

    ОтветитьУдалить
  2. Ответы
    1. Здравствуй.
      Мой email: igro83@gmail.com.

      Удалить
    2. ответил вам на почту с почты @mail.ru с просьбой написать мне по указанным в письме контактам. выслал предварительную инфо о задаче в скринах. Жду ответа и благодарю за помощь

      Удалить
  3. Константин, снимаю шляпу!)))

    ОтветитьУдалить
  4. Подскажите пожалуйста как этот макрос сделать так, чтобы он работал только на одном листе определённом?
    Если что мой ящик alexantropov74@gmail.com

    ОтветитьУдалить
    Ответы
    1. Доброго дня.
      После строк
      if (sheet.getName()=='help'){

      return;

      }

      добавить

      if (sheet.getName()!='Название листа'){

      return;

      }

      Удалить
    2. Вот это спасибищееее!!!

      Какие-то новые проекты планируете рассматривать\разбирать?

      Удалить
    3. Когда есть что то интересное, выкладываю по мере готовности. Посмотрите блог полностью, может что то и найдёте для себя.

      Удалить
  5. Добрый день! А можно ли как-то просмотреть изображения, которые в начале статьи? Сейчас их нет(...

    ОтветитьУдалить
  6. Добрый день, подскажите, как заполнить таблицу значениями не в первые 3 столбца, а например в столбцы 6-8? какую часть кода поменять?

    ОтветитьУдалить
    Ответы
    1. Попробуйте разобрать код - какие числа за что отвечают.
      Статья уже достаточно старая, поэтому очень многие вещи сделал бы сейчас по другому.
      В нескольких местах нужно исправлять, причём вдумчиво.

      Удалить
  7. Этот комментарий был удален администратором блога.

    ОтветитьУдалить
  8. Здравствуйте! Подскажите вариант создания формы с раскрывающимся списком из гугл таблицы.
    Спасибо!

    ОтветитьУдалить