This article is a continuation of Infinite Dependent Dropdowns in Google Sheets.
Changes and additions to the code are described here, namely:
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');
};
}
Now, in this version of the program, it is possible to automatically create all the sheets of the file necessary for the script to work (including data formatting and validation) by pressing just one(!) button from the user menu.
All you need for this:
You can get more information from this video (RU voice):
ATTENTION!
This article has a continuation: Infinite Dependent Dropdowns in Google Sheets (part 3)
The article considers the option of creating dependent drop-down lists on the Google Spreadsheet sheet, practically unlimited neither in the number of linked elements, nor in the number of rows on the sheet.
The program script is shown below:
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());
}
}
Please note: sheet names must EXACTLY match the names specified in the script!
Main sheet: "Home"
datasheet: "Data",intermediate data sheet: "Data_2"
These names in the script are highlighted in red, so, if desired, it will not be difficult to find them to replace with the names of your sheets.
This article has a continuation: Infinite Dependent Dropdowns Lists in Google Sheets (Part 2)
Now, in the new version of the program, it is possible to automatically create all the sheets of the file necessary for the script to work (including data formatting and validation) by pressing just one (!) button from the user menu.
More information you can find in this video (RU voice):
For those who are not yet familiar with dependent dropdown lists and have not yet had time to appreciate the incredible set of conveniences that they provide when working with spreadsheets, I recommend get to know this article. (There is also a link to the video)
Those who do not need to advertise the obvious advantages of dependent dropdown lists, sooner or later "rest" against the natural, and alas, inevitable limitations of spreadsheets:
This is especially true for small business owners who are well acquainted with the symptoms of "growing pains": what was incredibly helpful yesterday has become a hindrance and brake today.
To this end, an attempt was made to combine the advantages of spreadsheets and databases. And the administrative panel of the popular Django framework, written in the Python programming language, was chosen as the place for such a "polygon".
The main argument for this choice was the administrative panel of this framework. The Django admin has a tabular structure, so it looks like a spreadsheet. Therefore, improvements to the level of compliance with the specified parameters here require the most minimal.
Well, such pleasant "little things" as the incredible popularity of this framework, its reliability and performance just strengthened this choice.
Python is the child of Unix OS. Therefore, choosing a unix-like operating system (Linux or macOS) would be a completely logical decision. Moreover, it is not at all necessary to switch to a computer with another OS: you can simply install a virtual machine on your computer with any operating system and install Linux on it already. And also install PyCharm - a convenient and free development environment.
How to do this is described in detail and shown in these videos (RU voice!):
After successfully installing all of the above, you can proceed to create a project. To do this, you need to create a directory with the name of the project django-dropdown-admin, enter this directory and start the process of creating a virtual environment and then to start it:
$ virtualenv venv - p python3
$ source venv/bin/activate
The virtual environment is running, as indicated by the parenthesized folder name that appears at the beginning of the command line: (venv).
Next, install Django:
(venv) $ pip install django
Create a project (note the dot at the end of the command line!):
(venv) $ django-admin startproject main .
And start the dropdown application:
(venv) $ django-admin startapp dropdown
You will see something like this as a result:
.
├── 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
From now on, all further actions will be more convenient to perform in the PyCharm IDE. (see video at the end of this article)
First of all, let's make changes to the settings - the main/settings.py file:
1.) Add the newly created application to 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.) Cancel complex validation of user passwords for debug mode
# 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.) And specify the path for the static files (it will be required to change the admin js script)
# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/4.0/howto/static-files/
STATIC_URL = 'static/'
STATICFILES_DIRS = [
BASE_DIR / "static",
]
To logically complete the last action, immediately add a new directory for static files to the project directory: static
As you know, Django uses ORM (Object-relational mapping), so to create a database, you just need to describe its structure in the models.py file and then carry out migrations, which, according to the description of this structure, will create the necessary database tables and establish all necessary links between the fields of these tables.
The contents of the file 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']
Now that the database structure is described, it is necessary to implement it in the database itself, for which we first create migrations in the terminal window with the first command (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
And then, with the following command (python manage.py migrate), we apply all these migrations to our database:
(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
In the simplest version, we can work with our database directly from the Admin. By the way, this is the rare case when "simplest" does not mean "worst" at all. Quite the contrary - the standard Django admin panel already contains incredibly convenient and powerful tools for adding, changing and deleting data from the database.
And all that is needed for this is just to make a minimal description of the Admin panel structure in the file dropdown/admin.py using the following code:
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)
To enter the Admin panel, it remains to take the last step - create a Superuser.
Therefore, we enter the terminal window again and first enter the command to create the Superuser (python manage.py createsuperuser), and then sequentially answer the system's questions:
(venv) $ python manage.py createsuperuser
Username (leave blank to use 'su'): root
Email address: root@root.com
Password:
Password (again):
Superuser created successfully.
Now is the time to check what we got. Run the site using the command 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.
and go to the admin panel by url: http://127.0.0.1:8000/admin/
And we log in as a Superuser, using the login and password that we entered when creating the superuser user.
In our version, there will be three dependent drop-down lists:
You must first fill them in, and then fill in the table of Allowed Combinations (AllowedCombination) on the basis of which our script will offer values in the drop-down lists. Well, something like this:
Of course, in each case, all tables, the names of categories and subcategories, the depth of nesting of all these categories-subcategories-sub-subcategories, and, of course, the values themselves, will be completely different. The proposed option is just an illustration of the idea itself.
Everything that was before - the most that is, the usual and standard Django. Which knows how to work with lists, but does not yet know how to make them dependent, that is, to suggest the values of the subsequent list, taking into account the choice of the previous one.
To teach Django new functionality, you need to add a little "magic":
Add file 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",
)
Add a static file 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);
}
});
})
})();
And we make changes to the already existing dropdown/admin.py file:
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)
Ready! Now in our Admin panel, when filling in the Order table, we can use the Dependent Dropdown Lists:
And the general list of files of our project now looks like this:
.
├── 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
More details about the creation of this project are described (and, most importantly, shown!) In this video (RU voice!):
This article is a continuation of the article Infinite Dependent Dropdown Lists in Google Sheets (part 3) .
Here's an example of how Infinite Linked Lists can be used in a Personal Financial Plan.
In addition to adding several user-friendly functions (automatic insertion of the payment date and the formula for maintaining the balance of receipts and expenses), the script provided contains a number of significant improvements and improvements:
// 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;
}
You can get more information from this video (RU voice!):
This article is a continuation of the article Infinite Dependent Dropdown Lists in Google Sheets (part 2) .
Changes, changes and additions to the code are described here, namely:
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;
}
This version of the program retains the ability to automatically create all the sheets of the file necessary for the script to work (including formatting and data validation) by pressing just one (!) button from the user menu.
All you need for this:
You can get more information from this video (RU voice!):
ATTENTION!
This article continues: Infinite Dropdown Lists: Personal Financial Plan (example # 1)