Blog

What's the idea?

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:

• requiring sufficiently high qualifications for staff,
• lack of "foolproof",
• complexity in the organization of protection and distribution of access rights,
• difficulty in organizing automatic data exchange with other information sources (websites, databases)
• etc. etc.

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".

Why Django?

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.

Preparing the project and setting up the development environment

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!):

• How to Install VirtualBox on Windows
• Installing Ubuntu 20.04 on VirualBox
• Installing PyCharm and creating a virtual environment

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)

1. Change settings.py

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

2. Create a database (DB)

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

3. Preliminary configuration of the Admin panel (administrative panel of the Django framework)

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.

4. Filling the database

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:

• Categories
• Subcategories
• and directly Goods

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.

5. Make Lists Dependent

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 a new form (file dropdown/forms.py
• Add a new js script (file static/js/restricted-model-choice-field.js
• Apply changes to an existing file dropdown/admin.py

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:

• Automatic sheet creation Nome ;
• Automatic addition of all formulas on the Nome sheet;
• When editing a previously created row of dependent lists, the values ​​and formulas to the right of the edited cell are automatically deleted.
• Ability to shift the table on the Nome sheet to the right and down by an arbitrary number of columns
• 10/01/2021 Added check for Home and Data sheets in the file

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:

1. create sheet named " Data "
2. copy the data required for dependent lists into it
3. copy and paste this script into the file
4. refresh the page
5. in the appeared custom menu select: Create sheets

You can get more information from this video (RU voice!):

ATTENTION!
This article continues: Infinite Dropdown Lists: Personal Financial Plan (example # 1)

The script below allows you to access Google Spreadsheets files to extract data from the spreadsheet sheets and write new information.

In order for the script to work, the following conditions must be met:

• Create Service Account: https://console.cloud.google.com/home/dashboard
• Create a new project, install virtual environment and install the Google API software package (see video below)
• Create a file in the service account credentials.json
• Upload the credentials.json file to the project root
• Create a Google Spreadsheet file and give the bot access to this file (see video below)
• Copy files main.py and a1range.py and copy the code below

Code for main.py file:

from a1range import A1Range
import os.path
from googleapiclient.discovery import build
from google.oauth2 import service_account

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
SERVICE_ACCOUNT_FILE = os.path.join(BASE_DIR, 'credentials.json')

credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1KKOM9tpdCKtQmxyKkP_mcEMj1zreHyZ72cLL0ENXSHw'
SAMPLE_RANGE_NAME = 'Sheet1'


service = build('sheets', 'v4', credentials=credentials).spreadsheets().values()

# Call the Sheets API
# result = service.get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
#                      range=SAMPLE_RANGE_NAME).execute()

# data_from_sheet = result.get('values', [])

array = {'values': [[5, 6, None, 100], ['=SUM(A1:A4)', '=SUM(B1:B4)']]}
range_ = A1Range.create_a1range_from_list('Sheet1', 4, 1, array['values']).format()
response = service.update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                          range=range_,
                          valueInputOption='USER_ENTERED',
                          body=array).execute()

Code for a1range.py file:

from dataclasses import dataclass


@dataclass
class A1Range:
    sheet_name: str
    start_row: int
    start_col: int
    end_row: int
    end_col: int

    def format(self) -> str:
        start = f"{self.col_number_to_letter(self.start_col)}{self.start_row}"
        end = f"{self.col_number_to_letter(self.end_col)}{self.end_row}"
        return f"{self.sheet_name}!{start}:{end}"

    def iter_rows(self):
        return range(self.start_row, self.end_row + 1)

    def iter_cols(self):
        return range(self.start_col, self.end_col + 1)

    @staticmethod
    def col_number_to_letter(j: int) -> str:
        if 1 <= j <= 26:
            return chr(ord('A') + j - 1)
        elif 27 <= j <= 26 * 26:
            first_letter = chr(ord('A') - 1 + (j - 1) // 26)
            second_letter = chr(ord('A') + (j - 1) % 26)
            return first_letter + second_letter
        else:
            raise ValueError(f"Col number is out of range: {j!r}")

    @staticmethod
    def col_letter_to_number(letters: str) -> int:
        letters = letters.upper()
        if len(letters) == 1 and (ord(letters) < ord('A') or ord(letters) > ord('Z')):
            raise ValueError(f"Col letter is out of range: {letters!r}")

        if len(letters) == 2 and (ord(letters[1]) < ord('A') or ord(letters[1]) > ord('Z')):
            raise ValueError(f"The second Col letter is out of range: {letters!r}")

        if len(letters) == 1:
            return ord(letters) - ord('A') + 1
        elif len(letters) == 2:
            return (ord(letters[0]) - ord('A') + 1) * 26 + ord(letters[1]) - ord('A') + 1

    @staticmethod
    def extract_letters(text) -> str:
        only_letters = ''
        for t in text:
            if t.isalpha():
                only_letters += t
        return only_letters

    @staticmethod
    def extract_digits(text) -> str:
        only_digits = ''
        for t in text:
            if t.isdigit():
                only_digits += t
        return only_digits

    @classmethod
    def parse_a1_range(cls, a1: str):
        if "!" in a1 and ":" in a1:
            sheet_name, cell_range = a1.split('!')
            range_start, range_end = cell_range.split(':')
            start_col, start_row = cls.extract_letters(range_start), cls.extract_digits(range_start)
            end_col, end_row = cls.extract_letters(range_end), cls.extract_digits(range_end)
            return cls(
                sheet_name=sheet_name,
                start_col=cls.col_letter_to_number(start_col),
                start_row=int(start_row),
                end_col=cls.col_letter_to_number(end_col),
                end_row=int(end_row),
            )
        else:
            raise ValueError(f'Error! For method "parse_a1_range()" must be full address!')

    @classmethod
    def create_a1range_from_list(cls, sheet_name, from_row, from_col, array):
        """
        The method find coordinates in format A1Notation for a list with data for inserting into a Google Sheet
        :param sheet_name: sheet name in Google Sheet
        :param from_col: the column coordinate of the upper left corner
        :param from_row: the row coordinate of the upper left corner
        :param array: a list with data for inserting in Google Sheet
        :return: coordinates for Google Sheet in format A1Notation
        """
        count_rows = len(array)
        count_cols = 0
        for row in array:
            if len(row) > count_cols:
                count_cols = len(row)
        return cls(
            sheet_name=sheet_name,
            start_col=from_col,
            start_row=from_row,
            end_col=from_col+count_cols-1,
            end_row=from_row+count_rows-1,
        )

You can find more information in this video (RU voice):

The method of parsing (scraping), discussed in the article Parsing (scraping) using Google Apps Script is incredibly simple and easy to use. There is no need for complex programs, or high qualifications or special knowledge, or a lot of experience. All code is written directly in the Google Sheets editor, and then placed and run there.

In addition to all this, the script can be run on a schedule at any time in 24/7 mode, and the result can be processed right there and, if necessary, sent by email or a new file with the received and processed data can be created on Google disk.

However, there may be problems here. First of all, this script does not work with sites (more precisely, with pages) where pre-registration is required. Since, to register on the site, you need to create a session and place its code in special files - cookies. However, the standard UrlFetchApp function used in Apps Script to access a website does not allow cookies.

There is another problem, which has recently manifested itself more and more often. This is the unwillingness of site owners to share information with bots. Therefore, you can often see a picture when the html-code issued by the server at the request of the script is significantly different from the code that is issued to the browser.

Actually, precisely in order to quickly find out whether it is possible to parse (scrap) the site we need or not, this program was written:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Check', 'fastAnalyzer')
      .addSeparator()
      .addItem('Create Sheeet CHECK', 'createSheet')
      .addToUi();
}


function fastAnalyzer() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Check');
  const urlDomain = ss.getRange(2, 1).getValue();
  
  let url = urlDomain;
  let count = 0;
  let response = UrlFetchApp.fetch(url);
  let lll = response.getContentText().length;
  ss.getRange(2, 2).setValue(response.getResponseCode());
  ss.getRange(2, 3).setValue(lll);
  let html = response.getContentText();
  let whatLookingFor = ss.getRange(3, 2).getValue();
  let pos = html.indexOf(whatLookingFor);
  ss.getRange(3, 3).setValue(pos);

  ss.getRange(4, 1, ss.getLastRow(), 2).clearContent();
  let totalNumber = html.length
  let i = 0;

  // Check defoult value of delta - number of symbols in 1 row
  // If the cell is empty, print the defole value = 5000
  let delta = Number.parseInt(ss.getRange(2, 4).getValue());
  if (isNaN(delta)) {
    delta = 5000
    // Print defoult value of number of symbols in 1 row
    ss.getRange(2, 4).setValue(delta);
  };

  let iStart = 0;
  while (true) {
    let iEnd = iStart + delta;
    if (iEnd > totalNumber) iEnd = totalNumber; 
    ss.getRange(4 + i, 1).setValue("Символы от " + iStart + " до " + iEnd);
    ss.getRange(4 + i, 2).setValue(html.slice(iStart,iEnd));
    // let currentRow = (4+i) + ":" + (4+i)
    // ss.getRange(3, 3).setValue(currentRow);
    // ss.getRange(currentRow).activate();
    // ss.setRowHeight(4+i, 200);
    i++;
    if (iEnd - iStart < delta) break;
    iStart = iEnd;

  };
}

function createSheet() {

// is exist Check sheet?
  let s = SpreadsheetApp.getActiveSpreadsheet();
  
// create if not exist
  if(!s.getSheetByName('Check')) {
    s.insertSheet('Check', 0);
    ssc = s.getSheetByName('Check');

    ssc.getRange(1, 1).setValue("Url сайта");
    ssc.getRange(1, 2).setValue("Код ответа сайта");
    ssc.getRange(1, 3).setValue("Число символов на выбранной странице");
    ssc.getRange(1, 4).setValue("Изменить вывод числа знаков в однй строке");
    ssc.getRange(2, 1).setBackground('ACCENT3');  // yellow background cell
    ssc.getRange(3, 1).setValue("Какой элемент кода ищем?");
    ssc.getRange(3, 2).setBackground('ACCENT3');  // yellow background cell
  };
}

So, all you need to work with this program is

• create a new Google Spreadsheet
• open a code editor in this file
• completely copy the code from this article into the code editor of the file you just created (and don't forget to save it!)
• refresh the spreadsheet page and select a custom menu item: Create Sheet CHECK

The program is ready to go! All that is needed now is to add the site url to the Google tables page, the code element that you want to find on this page, and run the script. The answer will be ready in a second!

You can find more information in this video (RU voice):

The script below allows you to automatically find and download information from one of the freelance exchanges.

The domain name is taken from the Google Spread Sheets page. And search results are also uploaded there.

The function scraper() , which contains two cycles, is used as a control script. In the first loop ( for-loop ), we access the web pages of the site and save them in the variable html . In the second loop ( while-loop ), this variable is sequentially processed using three auxiliary functions:

• The getBlock function finds the part of the html code (code block) inside the tag (usually by the unique value of the attributes of this tag), and returns this block as a string value;
• The deleteBlock function, on the contrary, deletes the found fragment of the html code inside the block and also returns the remainder of this block as a string value.
• Unlike the first two functions, the getOpenTag function does not delete the found tag, but returns it as a string value. True, not the whole tag, but only the first (opening part) of this tag.

function scraper() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const urlDomain = ss.getRange(1, 1).getValue();
  
  let url = urlDomain;
  let count = 0;
  for (let page = 1; page < 5; page++) {
    url = urlDomain + page + '/';
    if (page == 1) url = urlDomain;
    let response = UrlFetchApp.fetch(url);
    ss.getRange(2, 1).setValue(response.getResponseCode());
    let html = response.getContentText();
    let p = 0;
    while (true) {
    
      let out = getBlock(html, 'div', html.indexOf('class="JobSearchCard-primary"', p));
      let block = out[0];
      p = out[1] + 1;
      if (p == 0) break;
      
      let title1 = getBlock(block, 'div', 0)[0];
      let title = getBlock(title1, 'a', 0)[0];
      
      let link = getOpenTag(title1, 'a', 0);
      link = getAttrName(link, 'href', 0)
      let formula = '=HYPERLINK("https://www.freelancer.com' +link + '", "' + title + '")';
      ss.getRange(3 + 3 * count, 2).setValue(formula);
      
      let price = getBlock(block, 'div', block.indexOf('class="JobSearchCard-primary-price'))[0];
      if (price.includes('span')) price =  deleteBlock(price, 'span', price.indexOf('span'));
      ss.getRange(3 + 3 * count + 1, 2).setValue(price).setHorizontalAlignment('right');
      
      let description = getBlock(block, 'p', block.indexOf('class="JobSearchCard-primary-description"'))[0];
      ss.getRange(3 + 3 * count, 1, 3).mergeVertically().setValue(description)
          .setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
          .setVerticalAlignment('middle')
          .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
  
      ss.getRange(3 + 3 * count, 2, 3).setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID);
      
      let cat = getBlock(block, 'div', block.indexOf('class="JobSearchCard-primary-tags"'))[0];
      cat = cat.split('</a>').map(item => item.split('>')[1]);
      cat.pop();
      cat = cat.join(', ');
      ss.getRange(3 + 3 * count + 2, 2).setValue(cat);
    
      count++;
    
    }; 
  };
}

function getAttrName(html, attr, i) {
  let idxStart = html.indexOf(attr , i);
  if (idxStart == -1) return "Can't to find attr " + attr + ' !';
  idxStart = html.indexOf('"' , idxStart) + 1;
  let idxEnd = html.indexOf('"' , idxStart);
  return html.slice(idxStart,idxEnd).trim();
}

function getOpenTag(html, tag, idxStart) {
  let openTag = '<' + tag;
  let lenOpenTag = openTag.length;
  // where we are?
  if (html.slice(idxStart, idxStart + lenOpenTag) != openTag) {
    idxStart = html.lastIndexOf(openTag, idxStart);
    if (idxStart == -1) return "Can't to find openTag " + openTag + ' !';
  };
  // begin loop after openTag
  let idxEnd = html.indexOf('>', idxStart) + 1;
  if (idxStart == -1) return "Can't to find closing bracket '>' for openTag!";
  return html.slice(idxStart,idxEnd).trim();
}

function deleteBlock(html, tag, idxStart) { // delete opening & closing tag and info between them
  let openTag = '<' + tag;
  let lenOpenTag = openTag.length;
  let closeTag = '</' + tag + '>';
  let lenCloseTag = closeTag.length;
  let countCloseTags = 0;
  let iMax = html.length;
  let idxEnd = 0;
  // where we are?
  if (html.slice(idxStart, idxStart + lenOpenTag) != openTag) {
    idxStart = html.lastIndexOf(openTag, idxStart);
    if (idxStart == -1) return ["Can't to find openTag " + openTag + ' !', -1];
  };
  // begin loop after openTag
  let i = html.indexOf('>') + 1;
  
  while (i <= iMax) {
    i++;
    if (i === iMax) {
      return ['Could not find closing tag for ' + tag, -1];
    };
    let carrentValue = html[i];
    if (html[i] === '<'){
      let closingTag = html.slice(i, i + lenCloseTag);
      let openingTag = html.slice(i, i + lenOpenTag);
      if (html.slice(i, i + lenCloseTag) === closeTag) {
        if (countCloseTags === 0) {
          idxEnd = i + lenCloseTag;
          break;
        } else {
          countCloseTags -= 1;
        };
      } else if (html.slice(i, i + lenOpenTag) === openTag) {
        countCloseTags += 1;
      };
    };
  };
  return (html.slice(0, idxStart) + html.slice(idxEnd, iMax)).trim();
}

function getBlock(html, tag, idxStart) {  // <tag .... > Block </tag>
  let openTag = '<' + tag;
  let lenOpenTag = openTag.length;
  let closeTag = '</' + tag + '>';
  let lenCloseTag = closeTag.length;
  let countCloseTags = 0;
  let iMax = html.length;
  let idxEnd = 0;
  // where we are?
  if (html.slice(idxStart, idxStart + lenOpenTag) != openTag) {
    idxStart = html.lastIndexOf(openTag, idxStart);
    if (idxStart == -1) return ["Can't to find openTag " + openTag + ' !', -1];
  };
  // change start - will start after openTag!
  idxStart = html.indexOf('>', idxStart) + 1;
  let i = idxStart;
  
  while (i <= iMax) {
    i++;
    if (i === iMax) {
      return ['Could not find closing tag for ' + tag, -1];
    };
    let carrentValue = html[i];
    if (html[i] === '<'){
      let closingTag = html.slice(i, i + lenCloseTag);
      let openingTag = html.slice(i, i + lenOpenTag);
      if (html.slice(i, i + lenCloseTag) === closeTag) {
        if (countCloseTags === 0) {
          idxEnd = i - 1;
          break;
        } else {
          countCloseTags -= 1;
        };
      } else if (html.slice(i, i + lenOpenTag) === openTag) {
        countCloseTags += 1;
      };
    };
  };
  return [html.slice(idxStart,idxEnd + 1).trim(), idxEnd];
}

You can find more information in this video (RU voice):

The examples below detail JavaScript and Apps Script methods for finding array elements: find() , findIndex () , indexOf() , lastIndexOf() , includes() , every() and some() .

Methods findIndex() , indexOf() and lastIndexOf() find the index of the array that satisfies the specified condition, and the find() method find its value.

The includes() method is convenient for use in the if operator, since it returns true if the item is found, and false if not.

The every() method checks that ALL elements of the array match the condition specified in the callback function, and the some() method checks Whether this condition satisfies at least ONE element of the array.

const arr = [1, 2, 3, 4, 5, 3];
  
// find() returns the VALUE of the FIRST item satisfies condition of the callback function
// OR return <underfined>

  let valueGteater3 = arr.find(item => item > 3);
  console.log(valueGteater3); // 4
  
  let valueGteater5 = arr.find(item => item > 5);
  console.log(valueGteater5); // undefined

// findIndex() returns the INDEX of the FIRST item satisfies condition of the callback function
// OR return <-1>

  let indexItemGteater3 = arr.findIndex(item => item > 3);
  console.log(valueGteater3); // 3
  
  let indexItemGteater5 = arr.findIndex(item => item > 5);
  console.log(valueGteater5); // -1
  
// variant for not first item  
  
  let notFirstItem = arr.find((item, index) => {
    if (item > 3 && index > 3) return true;
  });
  console.log(notFirstItem); // 5
  
// indexOf() returns the INDEX of the FIRST item is equal the specified value
// OR return <-1>

  let valueEqual3 = arr.indexOf(3);
  console.log(valueEqual3); // 2
  
  let valueEqual3Next = arr.indexOf(3, 3);
  console.log(valueEqual3Next); // 5
 
  let valueEqual1 = arr.indexOf(1, 3);
  console.log(valueEqual1); // -1
  
// lastIndexOf() returns the INDEX of the LAST item is equal the specified value
// OR return <-1>

  let valueEqual3Last = arr.lastIndexOf(3);
  console.log(valueEqual3Last); // 5
  
  let valueEqual3NextLast = arr.lastIndexOf(3, 4);
  console.log(valueEqual3NextLast); // 2
 
  let valueEqual5Last = arr.lastIndexOf(5, 3);
  console.log(valueEqual5Last); // -1
  
// const arr = [1, 2, 3, 4, 5, 3];
// includes() returns TRUE is element is icluded in the array, and FALSE is not

  let isIncludes3 = arr.includes(3);
  console.log(isIncludes3); // true
  
  let isIncludes6 = arr.includes(6);
  console.log(isIncludes6); // false
  
// every() returns TRUE if ALL elements satisfy the condition, and FALSE is not

  let allPositive = arr.every(item => item > 0);
  console.log(allPositive); // true
  
  let moreThen1 = arr.every(item => item > 1);
  console.log(moreThen1); // false

// some() returns TRUE if AT LEAST ONE element satisfy the condition, and FALSE is not

  let moreThen4 = arr.some(item => item > 4);
  console.log(moreThen4); // true
  
  let moreThen5 = arr.some(item => item > 5);
  console.log(moreThen5); // false

You can find more information in this video (RU voice):

Short description of the method

The .reduce() method, like most other methods using the callback function, is a for loop, inside which the callback function sequentially processes all elements of the array. However, this is the only method that uses another parameter - accumulator, due to which the .reduce() method is often used to calculate the sum of the array.

The .reduce() method can pass 4 parameters to the callback function:

1. the current value of the sum of the element of the array accumulator
2. the current value of the array element item
3. optional parameter index - index of the current element
4. optional and rarely used parameter array

This method can also be used for more complex calculations. For example, below is a script that creates an array of indexes of the elements of the arr array, which are odd numbers.

const arr = [1, 2, 3, 4, 5];
  
// sum with for loop
  let sumArr = 0;
  for (let i = 0; i < arr.length; i++) {
    sumArr += arr[i];
  };
  console.log(sumArr);
  
// sum with forEach method
  let sumArr_1 = 0;
  arr.forEach(item => sumArr_1 += item);
  console.log(sumArr_1);
  
// sum with reduce method
  let sumArr_2 = arr.reduce((acc, item) => acc + item, 0);
  console.log(sumArr_2);
  
// with initial value
  sumArr_2 = arr.reduce((acc, item, index, array) => {
    console.log(`acc = ${acc}, item = ${item}, index = ${index}`);
    return acc + item;
  }, 0);
  console.log(sumArr_2);
  
// without initial value
  sumArr_2 = arr.reduce((acc, item, index, array) => {
    console.log(`acc = ${acc}, item = ${item}, index = ${index}`);
    return acc + item;
  });
  console.log(sumArr_2);
  
// find indexes items that are odd numbers  
  let newArr = arr.reduce((acc, item, index) => {
    if (item % 2 !== 0) acc.push(index);
    return acc;
  }, []);
  console.log(newArr); // [ 0, 2, 4 ]
  
 // reduceRight
  sumArr_2 = arr.reduceRight((acc, item, index, array) => {
    console.log(`acc = ${acc}, item = ${item}, index = ${index}`);
    return acc + item;
  });
  console.log(sumArr_2);

You can find more information in this video (RU voice):

Arrays in Apps Script can be created in several ways:

1. Declare the name of the array in the line of code and equate it to a sequence of values taken in square brackets;
2. Read data from a Google SpreadSheet using the .getValues() method;
3. Dividing a string using the .split([delimiter]) method (usually a space is used as the delimiter delimiter;
4. PP 3, when a word is used as a string, and as an divide, an empty string.

function createArray() {
  
// ======== HOW TO CREATE ARRAY? ===========

  // 1.) Write
  var arr = [1, 2, 3];
  
  // 2.) Read from spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var arr1 = ss.getDataRange().getValues(); // [[1.0, 2.0, 3.0]]
  
  // 3.) Using string splitting
  var string = 'I am learning Google Spreadsheet';
  var arr2 = string.split(' '); // [I, am, learning, Google, Spreadsheet]
  
  // 4.) Using string splitting
  var arr3 = 'word'.split(''); // [w, o, r, d]
  
}

There are several ways to add elements to an array:

1. The simplest one is to equate a specific element of an array (by specifying its index in square brackets) to the desired value.



If the maximum index value is exceeded by more than one, then all elements between the old and new maximum indexes are automatically set to null .

2. Adding elements to the end of an array using the .push (value) method;
3. Adding elements to the beginning of the array using the .unshift (value) method;
4. Deleting an element with maximum index .pop () ;



The value of the deleted element can be assigned to a new variable.

5. Deleting an element with a null index .shift () ;
6. Address change of array elements using the .splice (index, hawManyDelete, whatInsert) method;

Where:
• index - index number from which indexes will be inserted / deleted;
• hawManyDelete - the number of items to be deleted. If hawManyDelete = 0, there will be no deletion;
• whatInsert - the value of the element (s) that (s) will be inserted, starting at index number index .
7. Method of changing the order of indexes in an array from direct to reverse and vice versa .reverse() ;
8. The method for sorting the elements of the array .sort() . Without a parameter in the form of a function, it is used extremely rarely due to its limitations: it only sorts unicode strings in ascending order.

function addDeleteItems() {

// ================ ADD ITEMS =======================

  // 1.) [index]
  var arr4 = [1, 2, 3];
  arr4[3] = 4; // [1.0, 2.0, 3.0, 4.0]
  arr4[5] = 6; // [1.0, 2.0, 3.0, 4.0, null, 6.0]
  arr4[-1] = 0; // [1.0, 2.0, 3.0, 4.0, null, 6.0]
  
  // 2.) .push(value)
  arr4.push(7); //  [1.0, 2.0, 3.0, 4.0, null, 6.0, 7.0]
  
  // 3.) .unshift(value)
  arr4.unshift(0) // [0.0, 1.0, 2.0, 3.0, 4.0, null, 6.0, 7.0]
  
// ================= DEL ITEMS ======================

  // 4.) .pop()
  var x = arr4.pop(); // [0.0, 1.0, 2.0, 3.0, 4.0, null, 6.0]
  
  // 5.) .shift()
  var x = arr4.shift(); // [1.0, 2.0, 3.0, 4.0, null, 6.0]
  
// ===== ADD (INSERT), DELETE, CAHGE =================

  // 6.) .splice(index, hawManyDelete, whatInsert)
  arr4.splice(4, 0, 5); // [1.0, 2.0, 3.0, 4.0, 5.0, null, 6.0]
  arr4.splice(5, 1);    // [1.0, 2.0, 3.0, 4.0, 5.0, 6.0]

// ============ CHANGE ORDER =========================
  // 7.) .reverse()
  arr4.reverse(); // [6.0, 5.0, 4.0, 3.0, 2.0, 1.0]
  
  // 8.) .sort()
  arr4 = [6.0, 5.0, 404.0, 41.0, 4.0, 3.0, 2.0, 1.0];
  arr4.sort();
}

You can find more information and examples in this video (RU voice):

Short description of the method

The .forEach() method, in essence, is a for loop, inside which the callback function sequentially processes all elements of the array.

The .forEach() method can pass three parameters to a callback function:

1. the current value of the array element item
2. optional parameter index - index of the current element
3. optional and rarely used parameter array

This is perhaps the only method that returns nothing. Therefore, it is used for external processing, for example, for printing array elements.

const arr = [1, 2, 3, 4, 5];
  
  for (let i = 0; i < arr.length; i++) {
    console.log(arr[i]);
  };

// variant #1
  arr.forEach(function(item) {
    console.log(item);
  });

// variant #2
  arr.forEach(item => console.log(item));
  
  console.log('------------------------------------');
  
// callback function parameters
  arr.forEach((item, idx, array) => console.log(item, idx, array));

  console.log('------------------------------------');

// get intersection of arrays
  let arr1 = [1, 3, 5];
  let arr2 = [1, 2, 3, 4, 5];
  let result = [];

  let res = arr1.forEach(item => {
    if (arr2.includes(item)) return result.push(item);
  });

  console.log(result); // [ 1, 3, 5 ]
  console.log(res); // undefined

You can find more information in this video (RU voice):