Блог

В чём идея?

Для тех, кто ещё не знаком со связанными (зависимыми) выпадающими списками и пока не успел оценить по достоинству тот невероятный набор удобств, который они дают при работе с электронными таблицами, рекомендую познакомиться с этой статьёй. (Там же есть ссылка на видео)

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

• требование достаточно высокой квалификации к персоналу,
• отсутствие "защиты от дурака",
• сложность в организации защиты и распределении прав доступа,
• сложность в организации автообмена данными с другими источниками информации (вебсайты, базы данных)
• и т.д. и т.п.

Особенно это актуально для владельцев малого бизнеса, хорошо знакомых с симптомами "болезни роста": то, что невероятно помогало ещё вчера стало мешать и тормозить сегодня.

С этой целью и была предпринята попытка совместить достоинства электронных таблиц и базы данных. И местом для подобного "полигона" была выбрана административная панель популярного фреймоворка Django, написанного на языке программирования Python.

Почему именно Django?

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

Ну, а такие приятные "мелочи", как невероятная популярность этого фреймворка, его надёжность и производительность просто укрепили в этом выборе.

Подготовка проекта и настройка среды разработки

Python - дитя Unix. Поэтому, выбор unix-подобной операционной системы (Linux или macOS) будет совершенно логичным решением. Тем более, что переходить на компьютер с другой OC совсем не обязательно: можно просто установить на своём компьютере с любой операционной системой виртуальную машину и установить Linux уже на неё. А также установить PyCharm - удобную и бесплатную среду разработки.

О том, как это сделать подробно рассказано и показано в этих видео:

• Как установить VirtualBox на Windows
• Установка Ubuntu 20.04 на VirualBox
• Установка PyCharm и создание виртуального окружения

После успешной установки всего вышеперечисленного можно переходить к созданию проекта. Для этого надо создать каталог с названием проекта django-dropdown-admin, войти в этот каталог и запустить процесс создания виртуального окружения и тут же его запустить:

$ virtualenv venv - p python3

$ source venv/bin/activate

Виртуальное окружение запущено, о чём говорит имя папки в скобках, появившееся в начале командной строки: (venv).

Далее устанавливаем Django:

(venv) $ pip install django

Создаём проект (обратите внимание на точку в конце!):

(venv) $ django-admin startproject main .

И приложение dropdown:

(venv) $ django-admin startapp dropdown

В результате должно получиться что-то вроде этого:

.
├── dropdown
│   ├── admin.py
│   ├── apps.py
│   ├── __init__.py
│   ├── migrations
│   │   └── __init__.py
│   ├── models.py
│   ├── tests.py
│   └── views.py
├── main
│   ├── asgi.py
│   ├── __init__.py
│   ├── settings.py
│   ├── urls.py
│   └── wsgi.py
├── manage.py
└── venv

С этого момента все дальнейшие действия удобнее будет выполнять в IDE PyCharm. (см видео в конце этой статьи)

1. Изменяем файл settings.py

Прежде всего внесём изменения в настройки - файл main/settings.py:

1.) Добавим вновь созданное приложение в INSTALLED_APPS

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    # === my apps =======
    'dropdown',
]

2.) Отменим сложную валидацию паролей пользователей для режима отладки

# Password validation
# https://docs.djangoproject.com/en/4.0/ref/settings/#auth-password-validators

if not DEBUG:
    AUTH_PASSWORD_VALIDATORS = [
        {
            'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
        },
        {
            'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
        },
        {
            'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
        },
        {
            'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
        },
    ]

3.) И укажем путь для файлов статики (потребуется для изменения js админки)

# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/4.0/howto/static-files/

STATIC_URL = 'static/'

STATICFILES_DIRS = [
    BASE_DIR / "static",
]

Чтобы логически завершить последнее действие, сразу же добавим в директорию проекта новый каталог для файлов статики: static

2. Создаём базу данных (БД)

Как известно в Django используется ORM (Object–relational mapping), поэтому для создания БД необходимо всего-навсего описать её структуру в файлеmodels.py и затем провести миграции, которые, по описанию этой структуры, создадут необходимые таблицы базы данных и установят между полями этих таблиц все необходимые связи.

Содержимое файла dropdown/models.py:

from django.db import models


class Category(models.Model):
    name_cat = models.CharField(max_length=250)

    def __str__(self):
        return self.name_cat


class Subcategory(models.Model):
    cat = models.ForeignKey(Category, on_delete=models.CASCADE,
                            verbose_name='category')
    name_subcat = models.CharField(max_length=250)

    def __str__(self):
        return self.name_subcat


class Good(models.Model):
    subcat = models.ForeignKey(Subcategory, on_delete=models.CASCADE,
                               verbose_name='subcategory')
    name_good = models.CharField(max_length=250)
    price = models.DecimalField(max_digits=6, decimal_places=2)

    def __str__(self):
        return self.name_good


class OrderItem(models.Model):
    order = models.ForeignKey("dropdown.Order", on_delete=models.CASCADE, verbose_name="order")
    cat = models.ForeignKey(Category, on_delete=models.CASCADE, verbose_name='cat')
    subcat = models.ForeignKey(Subcategory, on_delete=models.CASCADE, verbose_name='subcat')
    name_good = models.ForeignKey(Good, on_delete=models.CASCADE, verbose_name='good')
    quantity = models.PositiveIntegerField(default=0)
    amount = models.DecimalField(max_digits=9, decimal_places=2)

    def __str__(self):
        return f'{self.name_good} + {self.quantity}'


class Order(models.Model):
    order_id = models.PositiveIntegerField(unique=True)
    order_date = models.DateTimeField(auto_now=True)
    total_quantity = models.PositiveIntegerField(default=0)
    total_amount = models.DecimalField(max_digits=9, decimal_places=2)

    def __str__(self):
        return str(self.order_id)


class AllowedCombination(models.Model):
    cat = models.ForeignKey(Category, on_delete=models.CASCADE)
    subcat = models.ForeignKey(Subcategory, on_delete=models.CASCADE)
    good = models.ForeignKey(Good, on_delete=models.CASCADE)

    def __str__(self):
        return f'{self.cat} {self.subcat} {self.good}'

    class Meta:
        ordering = ['pk']

Теперь, когда структура БД описана, необходимо её воплотить в самой БД, для чего мы сначала в окне терминала первой командой создаём миграции (python manage.py makemigrations):

(venv) $ python manage.py makemigrations 

Migrations for 'dropdown':
  dropdown/migrations/0001_initial.py
    - Create model Category
    - Create model Good
    - Create model Order
    - Create model Subcategory
    - Create model OrderItem
    - Add field subcat to good
    - Create model AllowedCombination

А затем, следующей командой (python manage.py migrate), применяем все эти миграции к нашей базе данных:

(venv) $ python manage.py migrate

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, dropdown, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying dropdown.0001_initial... OK
  Applying sessions.0001_initial... OK

3. Предварительная настройка Админки (административной панели фреймворка Django)

В самом простейшем варианте, мы можем работать с нашей базой данных прямо из Админки. Кстати, это тот редкий случай, когда "простейшее" совсем не означает "худшее". Как раз наоборот - стандартная админка Django уже содержит в себе невероятно удобные и мощные инструменты для добавления, изменения и удаления данных из БД.

И всё, что для этого нужно - это все лишь сделать минимальное описание структуры Админки в файле dropdown/admin.py с помощью следующего кода:

from django.contrib import admin

from .models import (AllowedCombination, Category, Good, Order, OrderItem,
                     Subcategory)

admin.site.register(Category)
admin.site.register(Subcategory)
admin.site.register(Good)


class OrderItemInline(admin.TabularInline):
    model = OrderItem
    extra = 0


class OrderAdmin(admin.ModelAdmin):
    inlines = [OrderItemInline]


admin.site.register(Order, OrderAdmin)


class OrderItemAdmin(admin.ModelAdmin):
    pass


admin.site.register(OrderItem, OrderItemAdmin)


class AllowedCombinationAdmin(admin.ModelAdmin):
    list_display = ['cat', 'subcat', 'good', ]


admin.site.register(AllowedCombination, AllowedCombinationAdmin)

Для входа в Админку осталось сделать последний шаг - создать Суперпользователя.

Поэтому снова входим в окно терминала и сначала вводим команду создания Суперпользователя (python manage.py createsuperuser), а затем последовательно отвечаем на вопросы системы:

(venv) $ python manage.py createsuperuser
Username (leave blank to use 'su'): root
Email address: root@root.com
Password: 
Password (again): 
Superuser created successfully.

4. Наполняем БД новыми данными

Теперь самое время проверить, что у нас получилось. Запускаем сайт с помощью команды python manage.py runserver

(venv) $ python manage.py runserver

Watching for file changes with StatReloader
Performing system checks...

System check identified no issues (0 silenced).
April 06, 2022 - 17:33:16
Django version 4.0.3, using settings 'main.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CONTROL-C.

и переходим в админку по url: http://127.0.0.1:8000/admin/

И логинимся как Суперпользователь, используя login и password, которые ввели при созданиии пользователя superuser.

В нашем варианты будет три связанных выпадающих списка:

• Категории (Categories)
• Подкатегории (Subcategories)
• и непосредственно Товары (Goods)

Необходимо сначала заполнить их, а потом заполнить таблицу Допустимых Комбинаций (AllowedCombination) на основании которой наш скрипт и будет предлагать значения в выпадающих списках. Ну, что-то вроде этого:

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

5. Делаем списки связанными

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

Чтобы научить Django новому функционалу, надо добавить немного "магии", а именно:

• Добавить новую форму (файл dropdown/forms.py
• Добавить новый js-скрипт (файл static/js/restricted-model-choice-field.js
• Внести изменения в уже существующий файл dropdown/admin.py

Добавляем файл dropdown/forms.py:

import json
from typing import Any

from django import forms
from django.forms import Media, widgets

from .models import Good, OrderItem, Subcategory


class RestrictedSelect(widgets.Select):
    @property
    def media(self):
        media = super().media
        media += Media(js=["js/restricted-model-choice-field.js"])
        return media


class BoundRestrictedModelChoiceField(forms.BoundField):
    def get_restrictions(self):
        restrictions = {}

        restrict_on_form_field = self.form.fields[self.field.restrict_on_form_field]
        # Можно оптимизировать
        for restricting_object in restrict_on_form_field.queryset:
            allowed_objects = self.field.queryset.filter(**{self.field.restrict_on_relation: restricting_object})
            for obj in allowed_objects:
                restrictions.setdefault(obj.id, set()).add(restricting_object.id)

        return restrictions

    def build_widget_attrs(self, attrs, widget=None):
        attrs = super().build_widget_attrs(attrs, widget)

        restrictions = self.get_restrictions()
        restrictions = {k: [str(v) for v in vs] for k, vs in restrictions.items()}
        attrs["data-restrictions"] = json.dumps(restrictions)

        bound_restrict_on_form_field = self.form[self.field.restrict_on_form_field]
        attrs["data-restricted-on"] = bound_restrict_on_form_field.html_name

        return attrs


class RestrictedModelChoiceField(forms.ModelChoiceField):
    widget = RestrictedSelect

    def __init__(self, *args, restrict_on_form_field: str = None, restrict_on_relation: str = None, **kwargs):
        super().__init__(*args, **kwargs)

        if not restrict_on_form_field:
            raise ValueError("restrict_on_form_field is required")
        self.restrict_on_form_field = restrict_on_form_field

        if not restrict_on_relation:
            raise ValueError("restrict_on_relation is required")
        self.restrict_on_relation = restrict_on_relation

    def get_bound_field(self, form, field_name):
        return BoundRestrictedModelChoiceField(form, self, field_name)


class OrderItemForm(forms.ModelForm):
    class Meta:
        model = OrderItem
        fields = "__all__"

    subcat = RestrictedModelChoiceField(
        Subcategory.objects.all(),
        restrict_on_form_field="cat",
        restrict_on_relation="allowedcombination__cat",
    )
    name_good = RestrictedModelChoiceField(
        Good.objects.all(),
        restrict_on_form_field="subcat",
        restrict_on_relation="allowedcombination__subcat",
    )

Добавляем файл статики static/js/restricted-model-choice-field.js:

(function(){
  function throwError(baseElement, message) {
    console.error(message, baseElement);
    throw new Error(message);
  }

  function reset(baseElement) {
    baseElement.value = "";
  }

  function getRestrictedFields() {
    return Array.prototype.slice.apply(document.querySelectorAll("[data-restrictions]"));
  }

  function getFieldsRestrictedOn(baseElement) {
    let elements = getRestrictedFields();
    return elements.filter(e => getRestrictedOnField(e) === baseElement);
  }

  function isFormsetTemplate(baseElement) {
    return baseElement.name.indexOf("__prefix__") >= 0;
  }

  function getRestrictedOnField(baseElement) {
    if (isFormsetTemplate(baseElement)) {
      return null;
    }

    let fieldName = baseElement.getAttribute("data-restricted-on");
    if (!fieldName) {
      throwError(baseElement, "data-restricted-on is undefined");
    }

    if (fieldName.indexOf("__prefix__") >= 0) {
      fieldName = cleanDynamicFormSetName(baseElement, fieldName);
    }

    let form = baseElement.closest("form");
    if (!form) {
      throwError(baseElement, "The field is not inside a form");
    }

    let fields = form.querySelectorAll(`[name=${fieldName}]`);
    if (fields.length == 0) {
      throwError(baseElement, `Could not find field ${fieldName}`);
    }

    if (fields.length > 1) {
      console.warn(`Found multiple fields ${fieldName}`);
    }
    return fields[0];
  }

  function cleanDynamicFormSetName(baseElement, fieldName) {
    let prefixIx = fieldName.indexOf("__prefix__");
    let selfPrefix = baseElement.name.slice(prefixIx);
    let prefixMatch = selfPrefix.match(/\d+/);
    if (!prefixMatch) {
      throwError(baseElement, `Cannot detect dynamic formset prefix: ${baseElement.name}`);
    }

    return fieldName.replace("__prefix__", prefixMatch[0]);
  }

  function getRestrictions(baseElement) {
    let restrictionsJson = baseElement.getAttribute("data-restrictions");
    if (!restrictionsJson) {
      throwError(baseElement, "data-restrictions is undefined");
    }

    return JSON.parse(restrictionsJson);
  }

  function updateOptionList(baseElement) {
    if (isFormsetTemplate(baseElement)) {
      return;
    }

    let refField = getRestrictedOnField(baseElement);
    if (!refField) {
      throwError(baseElement, "Could not find refField");
    }

    let restrictions = getRestrictions(baseElement);

    let options = Array.prototype.slice.apply(baseElement.querySelectorAll("option"));
    options.forEach(option => {
      if (!option.value) {
        option.hidden = false;
        return;
      }

      let allowedOnValues = restrictions[option.value] || [];
      option.hidden = allowedOnValues.indexOf(refField.value) < 0;
    });
  }

  function clearOptionList(baseElement) {
    let options = Array.prototype.slice.apply(baseElement.querySelectorAll("option"));
    options.forEach(option => {
      option.hidden = true;
    });
  }

  document.addEventListener("change", event => {
    let element = event.target;
    getFieldsRestrictedOn(element).forEach(baseElement => {
      reset(baseElement);
      updateOptionList(baseElement);
      baseElement.dispatchEvent(new Event("change", {bubbles: true}));
    });
  });

  document.addEventListener("DOMContentLoaded", () => {
    getRestrictedFields().forEach(baseElement => {
      if (isFormsetTemplate(baseElement)) {
        clearOptionList(baseElement);
      } else {
        updateOptionList(baseElement);
      }
    });
  })
})();

И вносим изменения в уже существующий файл dropdown/admin.py:

from django.contrib import admin

from .forms import OrderItemForm
from .models import (AllowedCombination, Category, Good, Order, OrderItem,
                     Subcategory)

admin.site.register(Category)
admin.site.register(Subcategory)
admin.site.register(Good)


class OrderItemInline(admin.TabularInline):
    model = OrderItem
    form = OrderItemForm
    extra = 0


class OrderAdmin(admin.ModelAdmin):
    inlines = [OrderItemInline]


admin.site.register(Order, OrderAdmin)


class OrderItemAdmin(admin.ModelAdmin):
    form = OrderItemForm


admin.site.register(OrderItem, OrderItemAdmin)


class AllowedCombinationAdmin(admin.ModelAdmin):
    list_display = ['cat', 'subcat', 'good', ]


admin.site.register(AllowedCombination, AllowedCombinationAdmin)

Ву а-ля! Теперь в нашей Админке при заполнении таблицы Заказа (Order) мы можем использовать Связанные Выпадающие списки:

А общий список файлов нашего проекта выглядит теперь следующим образом:

.
├── db.sqlite3
├── dropdown
│   ├── admin.py
│   ├── apps.py
│   ├── forms.py
│   ├── __init__.py
│   ├── migrations
│   │   ├── 0001_initial.py
│   │   └── __init__.py
│   ├── models.py
│   ├── __pycache__
│   │   ├── admin.cpython-38.pyc
│   │   ├── apps.cpython-38.pyc
│   │   ├── __init__.cpython-38.pyc
│   │   └── models.cpython-38.pyc
│   ├── tests.py
│   └── views.py
├── main
│   ├── asgi.py
│   ├── __init__.py
│   ├── __pycache__
│   │   ├── __init__.cpython-38.pyc
│   │   ├── settings.cpython-38.pyc
│   │   ├── urls.cpython-38.pyc
│   │   └── wsgi.cpython-38.pyc
│   ├── settings.py
│   ├── urls.py
│   └── wsgi.py
├── manage.py
├── static
│   └── js
│       └── restricted-model-choice-field.js
└── venv

Более подробно о создание этого проекта рассказано (и, самое главное, показано!) в этом видео:

Данная статья является продолжением статьи Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 3).

Здесь рассмотрен пример практического применения Бесконечных связанных списков при составлении Личного Финансового Плана.

Помимо добавления нескольких удобных для пользователя функций (автоматическая вставка даты внесения платежа и формулы для ведения баланса приходов-расходов) скрипт, приведённый содержит целый ряд существенных доработок и улучшений:

// global variable
const excludedSheets = ['Summary', 'Category'];
const dataSheet = 'Category';

var colShift = 3;
var rowMin = 4;

function onEdit(e) {

  let row = e.range.getRow();
  let col = e.range.getColumn();
  let sheetName = e.source.getActiveSheet().getName();
  if (excludedSheets.includes(sheetName)) return;
  let name = e.value;
  let oldName = e.oldValue;
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  let mask = JSON.stringify(sh.getRange(row, colShift+1, 1, col-colShift).getValues()[0]);
  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet).getLastColumn();
  
  if(name !== oldName && row >= rowMin && col < colMax+colShift) {
    insertDateFormulasAndDataValidation(row, col, sheetName)
    fillColumn(row, col, mask, sheetName);
  }
}

function insertDateFormulasAndDataValidation(row, col, sheetName) {

  if (col == colShift+1) {
    let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    let dateValue = sh.getRange(row, 2).getValue();
    if (dateValue == '') {
      let today = new Date();
      let timeZone = Session.getScriptTimeZone();
      let todayStr = Utilities.formatDate(today, timeZone, 'dd.MM.yyyy');
      sh.getRange(row, 2).setValue(todayStr);
    };
    let formulaStr = '=I' + (row-1) + '+H' + row + '-C' + row; 
    sh.getRange(row, 9).setFormula(formulaStr);
    let conN = String.fromCharCode(65+colShift) 
    let adress = sheetName + '!' + conN + (row+1) + ':' + conN + (row+3);
    sh.getRange(adress).setDataValidation(SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(sh.getRange(dataSheet+'!$A$2:$A'), true)
      .build());    
  }
}

function tmp() {
  insertDateFormulasAndDataValidation(7, 4, 'Transactions');
}

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Create sheets', 'createSheets')
      .addToUi();
}

function fillColumn(row, col, mask, sheetName) {

  let col_data = col - colShift;

// clear dataVal and Value
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet).getLastColumn();
  sh.getRange(row, col + 1, 1, colMax-col_data).clearDataValidations().clearContent();
 
// find date
  let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet);
  let arrData = sd.getDataRange().getValues();
  let arrData_2 = [];
  
  let iMax = arrData.length - 1;
  for(let i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col_data)) == mask) {
      arrData_2.push(arrData[i].slice(0, col_data+1));
    }
  }
  arrData_2 = arrData_2.map(item => item.pop())
  let uniqArrDate_2 = arrData_2.filter(uniqValues); 

// add dataVal
  col++;
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInList(uniqArrDate_2, true)
  .build());
}

function uniqValues(item, index, arr) {
  return arr.indexOf(item) === index;
}

Дополнительную информацию вы можете получить из этого видео:

Данная статья является продолжением статьи Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 2).

Здесь описаны изменения изменения и дополнения кода, а именно:

• Автоматическое создание листа Номе ;
• Автоматическое добавление всех формул на листе Номе;
• При редактировании созданной ранее строки зависимых (связанных списков), автоматически удаляются значения и формулы справа от редактируемой ячейки.
• Возможность сдвига таблицы на листе Номе вправо на произвольное число столбцов
• 01.10.2021 Добавлена проверка наличия листов Home и Data в рабочем файле (книге)

var colShift = 0;

function onEdit(e) {

  let row = e.range.getRow();
  let col = e.range.getColumn();
  let sheetName = e.source.getActiveSheet().getName();
  let name = e.value;
  let oldName = e.oldValue;
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  let mask = JSON.stringify(sh.getRange(row, colShift+1, 1, col-colShift).getValues()[0]);
  if (!doesSheetExist('Home', true)) return;
  if (!doesSheetExist('Data', true)) return;

  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").getLastColumn();
  
  if(sheetName === "Home" && name !== oldName && col < colMax+colShift) {
    fillColumn(row, col, mask);
  }
}

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Create sheets', 'createSheets')
      .addToUi();
}

function fillColumn(row, col, mask) {

  let col_data = col - colShift;

// clear dataVal and Value
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  let colMax = sh.getLastColumn();
  sh.getRange(row, col + 1, 1, colMax).clearDataValidations().clearContent();
 
// find date
  let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  let arrData = sd.getDataRange().getValues();
  let arrData_2 = [];
  
  let iMax = arrData.length - 1;
  for(let i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col_data)) == mask) {
      arrData_2.push(arrData[i].slice(0, col_data+1));
    }
  }
  arrData_2 = arrData_2.map(item => item.pop())
  let uniqArrDate_2 = arrData_2.filter(uniqValues); 

// add dataVal
  col++;
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInList(uniqArrDate_2, true)
  .build());
}

function createSheets() {

// is exist Home?
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sd;
  if (doesSheetExist('Data', true)) {
    sd = ss.getSheetByName('Data');
  } else {
    return;
  }
  
  
// create if not exist
  if(!ss.getSheetByName('Home')) {
    ss.insertSheet('Home', 0);
// create Data Val
    var sh = ss.getSheetByName('Home');
    sh.getRange('Home!A2:A20').setDataValidation(SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(sh.getRange('Data!$A$2:$A'), true)
      .build());
    sh.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');

  };

}

function uniqValues(item, index, arr) {
  return arr.indexOf(item) === index;
}


function doesSheetExist(sheetName, needAlert) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let allSheets = ss.getSheets().map(sheet => sheet.getName())
  if (allSheets.includes(sheetName)) return true;
  if (needAlert) Browser.msgBox(`Error! Could not find sheet "${sheetName}"!`);
  
  return false;
}

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

Всё что для этого нужно:

1. cоздать лист с именем "Data"
2. скопировать в него данные, необходимые для связанных (зависимых) списков
3. скопировать и встваить в файл этот скрипт
4. обновить страницу
5. в появившемся пользовательском меню выбрать: Create sheets

Дополнительную информацию вы можете получить из этого видео:

ВНИМАНИЕ!
У этой статьи есть продолжение: Бесконечные Связанные Списки: Личный Финансовый План (пример #1)

Данная статья является продолжением статьи Бесконечные Зависимые Выпадающие Списки в Google Sheets.

Здесь описаны изменения изменения и дополнения кода, а именно:

• Автоматическое создание листов Номе Data_2;
• Автоматическое добавление всех формул на листе Номе;
• При редактировании созданной ранее строки зависимых (связанных списков), автоматически удаляются значения и формулы справа от редактируемой ячейки.

function onEdit(e) {

  var row = e.range.getRow();
  var col = e.range.getColumn();
  var list_name = e.source.getActiveSheet().getName();
  var name = e.value;
  var oldName = e.oldValue;
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var mask = JSON.stringify(sh.getRange(row, 1, 1, col).getValues()[0]);
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var colMax = sh.getLastColumn();
  
  if(list_name === "Home" && name !== oldName && col < colMax) {
    fillColumn(row, col, mask);
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Create sheets', 'createSheets')
      .addToUi();
}


function fillColumn(row, col, mask) {

// clear dataVal and Value
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var colMax = sh.getLastColumn();
  sh.getRange(row, col + 1, 1, colMax).clearDataValidations().clearContent();

// find date
  var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var arrData = sd.getDataRange().getValues();
  var arrData_2 = [];
  arrData_2.push(arrData[0]);
  
  var iMax = arrData.length - 1;
  for(var i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col)) == mask) {
      arrData_2.push(arrData[i]);
    }
  }
   
// clear Data_2
  var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
  sd_2.getDataRange().clearContent();

// insert data
  sd_2.getRange(1, 1, arrData_2.length, arrData_2[0].length).setValues(arrData_2);
  
// add dataVal
  col++;
  var list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'];
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(sh.getRange('Data_2!$' + list[col - 1] + '$2:$' + list[col - 1] + '$1000'), true)
  .build());
}

function createSheets() {

// is exist Home?
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sd = ss.getSheetByName('Data')
  
// create if not exist
  if(!ss.getSheetByName('Home')) {
    ss.insertSheet('Home', 0);
// create Data Val
    var sh = ss.getSheetByName('Home');
    sh.getRange('Home!A2:A20').setDataValidation(SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(sh.getRange('Data!$A$2:$A'), true)
      .build());
    sh.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');

  };

// is exist Data_2?
  if(!ss.getSheetByName('Data_2')) {
   
// create if not exist
    var k = ss.getNumSheets();
    ss.insertSheet('Data_2', k + 1);
    var sd_2 = ss.getSheetByName('Data_2');
    sd_2.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');
  };
}

Теперь, в этой версии программы появилась возможность по нажатию всего лишь одной(!) кнопки из пользовательского меню автоматически создавать все листы файла, необходимые для работы скрипта (включая форматирование и валидацию данных).

Всё что для этого нужно:

1. cоздать лист с именем "Data"
2. скопировать в него данные, необходимые для связанных (зависимых) списков
3. скопировать и встваить в файл этот скрипт
4. обновить страницу
5. в появившемся пользовательском меню выбрать: Create sheets

Дополнительную информацию вы можете получить из этого видео:

ВНИМАНИЕ!


У этой статьи есть продолжение: Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 3)

В статье рассмотрен вариант создания на листе Google Spreadsheet связанных выпадающего списков, практически не ограниченных ни по числу связанных элементов, ни по количству сток на листе.

Скрипт программы представлен ниже:

function onEdit(e) {

  let col = e.range.getColumn();
  let list_name = e.source.getActiveSheet().getName();
  let name = e.value;
  
  if(list_name=="Home") {
    fillColumn(col, name);
  }
}

function fillColumn(col, name) {

// find date
  var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var iMax = sd.getLastRow();
  var col_data = [];
  
  for(var i=2; i<=iMax; i++) {
    var x = sd.getRange(i, col).getValue();
    if(x == name) {
      col_data.push(sd.getRange(i, col+1).getValue());
    }
  }
   
// clear Data_2
  var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
  sd_2.getRange(2, col+1, sd_2.getLastRow(), sd_2.getLastColumn()).clearContent();


// insert data
  iMax = col_data.length;

  for(var i=2; i<=iMax+1; i++) {
    sd_2.getRange(i, col+1).setValue(col_data.shift());
  }
}

Обратите внимание: имена листов должно ТОЧНО соответствовать именам, указанным в скрипте!

Главный лист: "Home"

лист промежуточных данных: "Data_2"

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

У этой статьи есть продолжение: Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 2)

Теперь, в новой версии программы появилась возможность по нажатию всего лишь одной(!) кнопки из пользовательского меню автоматически создавать все листы файла, необходимые для работы скрипта (включая форматирование и валидацию данных).