👉Оглавление таблицы

Как создать оглавление для таблицы Google на листе, с именами листов и со ссылками на каждый лист

🔗 Ссылка на пример

Перейдя по этой ссылке, вы получите свою копию таблицы с формулами и кодом. После создания копии, в течение 5-ти секунд функция сработает. Вы получите ссылки на листы этой книги.

💰 Почему это полезно

Статья полезна в 2-х случаях:

Лист с оглавлением

Ссылки на листы книги будут выглядеть так:

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

Попробуйте:

Польза для новичков

Эта статья будет полезна тем, кто хочет изучать скрипты Google (Apps Script). Пример из статьи использует базовый код, как раз для начинающих. Особенно много материала в главе с объяснениями. Вы начнёте писать скрипты.

🎯 Решение

Если коротко, скопируйте себе код, и формулы, которые есть в примере. И оглавление готово!

Скопируйте код

⚠️ Если вы не видите код ниже ↓, скопируйте его отсюда.

Этот код мы позже будем использовать как простую функцию на листе:

=getSheets()

Скрипты Google позволяют создавать свои — пользовательские функции. Своя функция — это скрипт, который вы можете использовать в формулах, как обычную функцию таблицы. 


Приготовьте таблицу

Простой способ: скопируйте себе мой файл. Он уже содержит необходимой код. Когда вы копируете пример, вы копируете файл + скрипт. Такой скрипт называется встроенным, он привязан к таблице.

Второй способ установки — в новый или существующий файл. Создайте новую таблицу Google или используйте уже ранее созданную таблицу.

Вставьте код

Для этого откройте редактор скриптов через меню таблицы:

↑ Меню таблицы для вызова редактора скриптов

↑ Окно скрипт откроется в отдельной вкладке

Так вы создадите новый скрипт Google. Этот скрипт привязан к одному документу Google: к той таблице, из которой он был создан. Для других ваших таблиц придётся снова использовать редактор скриптов, и копировать код. 

В примере мы используем функцию из кода на листе \Sheet1/ в виде формулы в ячейке [А2]:

=getSheets()

А точнее, мы берём более умный вариант, который позволит формуле обновляться:

=getSheets(F1)

⚠️ Если вы скопируете формулу поверх ячейки, то формат текста, который вы копируете тоже перейдёт в таблицу. Чтобы не копировать формулу и форматирование:

Формула вернёт на лист список имён листов, и их номеров:

⚠️ Если формула в первый раз у вас не сработает, удалите знак равенства перед ней, выделите другую ячейку, а после этого снова введите знак = перед началом формулы. Это поможет формуле запуститься.

getSheets — это пользовательская функция, созданная скриптом. Когда у вас изменится число листов в книге, формула не просчитается сама 😩. 

Заставить формулу пересчитать значения можно! Для этого меняйте аргумент внутри формулы. Мы добавили в формулу такой аргумент — ссылку на ячейку [F1]:

=getSheets(F1)

В [F1] установили флажок. Чтобы сделать флажок:

При каждом нажатии на флажок в [F1], формула =getSheets(F1) обновится 😀.

Кроме нашей формулы, мы используем еще одну формулу, в ячейке [C2].

Русский вариант формулы:

=FILTER(ГИПЕРССЫЛКА("#gid=" & B2:B & "&range=A1";A2:A);A2:A<>"") 

Вариант с английскими именами функций:

=FILTER(HYPERLINK("#gid=" & B2:B & "&range=A1",A2:A),A2:A<>"") 

↑ Эта формула создает красивые ссылки из номеров и имён листов. 

Полный план установки

Чтобы получить такой же результат, как в примере, необходимо:

🤓 Объяснение

Начнём с кода. Есть минимум 3 варианта получить себе код:

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

Где живет код скриптов Google

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

В новой вкладке браузера появится редактор. Это среда для разработки, место, где вы создадите свои скрипты, или куда вставите скрипты других разработчиков. В отличие от таблицы, тут нет приятных элементов управления, всё что здесь есть — это текстовый редактор, в котором можно создавать новый код. Такая простота даёт вам неограниченные возможности: при помощи команд кода вы можете создавать собственные правила, создавать то, что не было предусмотрено в таблицах изначально. Создавать то, что никто до вас не делал!

Далее все примеры про код — основаны на работе в этом редакторе.

Редактор скрипов выглядит так:

Я пронумеровал основные его части:

5. Файлы проекта. Это как вкладки — листы в таблице. 

6. Номера строк вашей функции. Это для удобства работы с кодом, чтобы ссылаться на строку по её номеру.

7. Окно с кодом. Это и есть тот самый «блокнот», где вы пишете свой код.

<> Составные части кода

У нас есть 17 строк кода — функция getSheets

Обратите внимание, что в коде функция называется getSheets, и точно с таким же именем мы используем её в таблице:

Мы используем код в таблице как обычную функцию. Получаем список листов книги, и их айди.

Далее мы разберём код по косточкам: 

Поехали!

Скрипт Google. Функции и переменные

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

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

Функция-мясорубка(мясо) { верни фарш; }

В формулах таблицы мы тоже используем функции, и работают они по тому же принципу.

Вот пример простой функции в скриптах, которая возвращает ноль: 

function() { return 0; }

В конце функция вернёт результат через слово return . В данном случае результат — ноль. Функция может вернуть ноль, или фарш, или что угодно. За это отвечает слово return — «вернуть». Строка 16 нашего кода возвращает переменную с результатом: return result;.  

Команда return всегда завершает работу функции, даже если после неё есть другой код:

return; // 🛑 здесь конечная, дальше код не выполняется

Когда код доходит до return, программа заканчивает свою работу. То есть фаза return result; означает «вернуть результат и выйти из функции».


Получить листы → Get Sheets → GetSheets 

Пробелов быть не должно, иначе не сработает.

Задание для любознательных. Попробуйте: 


Внутри скобок могут быть входящие аргументы —переменные. Например, переменная с именем рынка: на каком именно рынке купить мясо. Приведём простой пример функции с аргументами: 

function(A, B) { return A + B; }

↑ вернет результат от сложения двух чисел: A + B

Информация для любознательных:

В скриптах Google код основан на популярном  языке «Ява скрипт» —  JavaScript. Большинство свойств скриптов Google и языка JavaScript — одинаковы

Например, чуткость к регистру. В скрипте Google важно, с какой буквы вы пишите аргументы: со строчной или с ЗАГЛАВНОЙ:

 a и A — это 2 разные переменные 🤓!

Советуем изучить основы языка JavaScript, чтобы лучше понять механику скриптов Google.

Итого: Функция getSheets() — без входящий аргументов. В скобках ничего нет. Такая функция-мясорубка не требует мяса и лука на вход.


Скобками обрамляется любая группа строк кода: функция, условие, цикл

Скобки {} нужны, чтобы компьютер мог отделить группы команд одну от другой. 

Каждая строка кода — это команда. Когда команд становится много, важен порядок их выполнения. Вот за порядком и следят фигурные скоби {}. Они — это корпус мясорубки.

Комментарий может находится вначале или в конце строки с кодом:

// Это комментарий в начале строки

return 500; // А это комментарий в конце строки

Все что идет после двух косых //  и до конца строки программа не выполняет. Когда выполняется функция, компьютер пропускает комментарии. Они строго говоря не нужны в коде. 

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

Комментарии — это как подписи под кнопками нашей электро-мясорубки. Если подписи оторвать, мясорубка будет работать не хуже прежнего. Но без подписей может быть тяжело догадаться, как её правильно запустить. Самые ценные комментарии те, которые оставил для самого себя. Позже они сэкономят ваше время и нервы.

var — кодовое слово, то есть это слово специальное.

🤓 можно использовать let и const, кроме var. Помните, если вы встретите в коде эти слова: let и const, и var, — перед вами объявления. Так вы даёте компьютеру знать, что хотите что-то назначить, и запомнить.


Вот несколько вариантов названий переменных:

file— ✔️ правильное имя переменной

4file — ❌ неправильно! Начинать имя с цифры нельзя

file# — ❌ Неправильно! Решётки и всякие символы, кроме нижнего подчёркивания — нельзя.

var — ❌ так нельзя потому что это слово специальное.

my_file — ✔️ так можно.

Дальше вы снова увидите в коде объявления переменных, когда имя переменной идет после кодового слова var. Это правило: перед тем, как получить что-то, надо это объявить, и назвать. При повторном использовании var не пишется, только при первом упоминании.


«Объявить переменную с именем file, которая содержит значение...»

Предложение получилось незаконченным. Если вы так и оставите запись var file = , то редактор не даст сохранить код. Явно не хватает чего-то справа от знака равенства.

Если есть знак равенства, надо написать, чему именно равна переменная. Так вы дадите ей значение. А потом это значение можно поменять. Например, переменная с числом:

var number = 0; // Объявили и присвоили значение 0.

number = 5;     // Поменяли ноль на пять. Второй раз использовать var не принято.

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

Скрипт Google. Объектная модель таблиц

Чтобы не вводить SpreadsheetApp каждый раз с клавиатуры, запомните только первые 2 буквы:

SpreadsheetApp —это один из больший встроенных объектов в скриптах Google. Таких больших блоков несколько.

Любознательным. Чтобы увидеть все большие объекты скриптов Google:

Попробуйте ввести SpreadsheetApp. и после точки ввести слово get «получить».  Вы увидите выпадающий список всего, что можно получить в объекте. Останется выбрать из списка то, что вам нужно. 


Проект с кодом привязан к текущему файлу— к таблице. Слово «Текущий» можно перевести как "Active" на английский. Вот так наша функция сама определяет, что ей взять и откуда. Теперь вы понимаете, почему нашей функции не нужны входящие переменные. Это «умная» мясорубка, сама знает где лежит мясо. 

Вся строка кода:

var file = SpreadsheetApp.getActive();

В переменную с именем file записали текущую книгу. Конечно, не саму книгу, а объект — её отображение в скриптах. Через текущую книгу — file — мы получим список всех листов текущей книги. 



Читаем всю фразу var file = SpreadsheetApp.getActive(); как

«Объявим переменную с именем file, которая содержит значение текущей книги getActive() из приложения таблиц SpreadsheetApp» 


Изложим суть того, что сказано выше. Читаем как стихи:

***

Вот создали мы function getSheets() { … } 

Которая в конце return result; 

// Добавили немного комментариев

И создали: var file = 

И объявили

SpreadsheetApp.getActive();

_______Продолжение следует______

var sheets = file.getSheets();

В строке 4 создали новую переменную var sheets и дали ей значение — массив всех листов книги: file.getSheets(); .  Обратите внимание, что листы мы получили через переменную file

Тут мы впервые используем связь между переменными в скриптах Google. Из книги получаем её листы. Когда несколько переменных вытекают одна из другой — это цепочка переменных:

var file = SpreadsheetApp.getActive();

var sheets = file.getSheets();

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

Все переменные скриптов Google и их связи составляют объектную модель

Красивые слова — объектная модель —  это что из чего вытекает.  

🤓 Любознательным. Попробуйте:

Написание кода — это как сборка мясорубки. Есть сто способов создать новую мясорубку, или написать код. Прекрасно, что многие детали и даже целые узлы уже готовы. Работа программиста — только собрать их вместе. Это похоже на сборку паззла. В итоге, зная простые основы, писать код легко, и даже увлекательно!


🤓 Любознательным задание:


В скриптах Google, как в любом механизме, работает вложенная система.  Работает это очень просто: одно вложено в другое:

Дерево → Сундук → Утка → Заяц → Яйцо → Игла → Смерть Кощеева


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

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


В подчинении у приложения табличек находятся все книги, к которым у вас есть доступ. Там же имеется текущая книга. Она чаще всего вам и нужна.

Цепочка для получения текущей книги состоит из двух элементов: Приложение таблиц → Текущая книга

Уже знакомый вам код:

SpreadsheetApp.getActive()

Текущую книгу получили через метод getActive() .

🤓 Любознательным. Есть методы и свойства. Методы что-то делают и содержат скобки в конце:

Метод

Вася, принеси (ящик с инструментом);

В скобках можем указать, что именно нам надо принести.

Вызов метода

Вася, принеси (String предмет);

В подсказе указано, что именно программа ожидает увидеть после слова «принеси». Но конкретно не указано что принести, а вместо этого идёт подходящее название для переменной: «предмет». Слово «String » — «Текст» — это подсказка, что именно программа ожидает получить. 

Свойство

Длина этого гвоздя — 30 миллиметров;

Гвоздь — длина = 30;

Это что-то статичное, что не предполагает вариантов. Скобок нет.

Вернемся к нашей переменной file , которая не нужна. Для меня код яснее, когда переменные из цепочки объявляются по очереди, здесь в 2 строки:

var file = SpreadsheetApp.getActive();

var sheets = file.getSheets();

Есть еще причина почему я так пишу. На всякий случай: мало, ли, вдруг понадобится еще потом переменная file. Привычка разработчика: все оставлять про запас.


Писать код коротко, или длинно — это дело вкуса. Вот так можно заменить строки 3 и 5 на одну строку кода:

var sheets = SpreadsheetApp.getActive().getSheets();

Видите, получилась цепочка из трёх звеньев: A.b.c. Мы избавились от переменной file!

Для создания мясорубки мне надо:

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

Пойти на склад → достать ящик → взять детали.

Скрипт Google. Переход по элементам массива

var result = [], sheet; 

Здесь мы опять видим кодовое слово var. Значит, перед нами объявление переменной. Точнее двух переменных: result, sheet

Еще в этой строке мы видим:

🤓 Любознательным. Мы объявили «пустую» переменную sheet. Она пустая потому что после неё не стоит знак равенства. Но в программировании не бывает пустоты. Проверьте сами:

Я создал пустую переменную sheet  чтобы потом использовать её. Дальше буду перебирать все листы текущей книги. По очереди sheet будет принимать значения каждого из листов.

Понятие «массив» тут новое. Массив — это список. Представьте огромную пулемётную ленту, в которой нет патронов. Это будет пустой массив []. Мысленно соедините эту пулемётную ленту с мясорубкой. Вместо патронов мясорубка получит небольшие порции лука и мяса. Чем больше зарядите мясных и луковых «патронов», тем больше фарша получите на выходе.


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

var a, b, c, d, e, f, g = 'G', z;

↑ Так должно сработать. Я обычно перечисляю несколько переменных перед началом цикла-перебора нескольких элементов. Например, перед таким циклом, как этот:

Здесь появился новый концепт — цикл:

for (var i = 0; i < sheets.length; i++) {

  // тело цикла... 

}

Цикл — это перебор элементов списка. В нашем примере цикл находится в строках с 9 по 14. Код в цикле проходит по списку листов книги — sheets. Это своего рода перекличка.  Запуск цикла — это когда ваша мясорубка начинает перемалывать мясо и лук. Один оборот = один ход цикла. 

Если вы не знакомы с программированием, но немного разбираетесь в формулах, вам поможет такой пример создания цикла формулами в таблице: 

1. Объявление цикла. За объявление цикла отвечает сама таблица, ее строки и ячейки. Мы только заполнили диапазон [A4:A9] значениями: Лист0, Лист1, Лист2, Лист3 и так далее. Это наш массив.

2. Тело. Потом мы создали формулу в [C4], которая будет обрабатывать элементы массива. Формула =RIGHT(A4) — это тело цикла. Формулу для примера можно взять любую. В примере мы забираем правый символ элемента массива с функцией RIGHT (ПРАВСИМВ).

3. Пуск. Вписали текст формулы в ячейку. Для наглядной иллюстрации я поставил эту же формулу в ячейку [E4] — напротив первого элемента массива. Копируем формулу вниз — запускаем цикл. Формула везде будет делать одно и тоже, но каждый раз она будет брать правый символ у нового элемента массива. Это цикл!

Циклы встречаются в жизни на каждом шагу. Не только в формулах и скриптах.

Посмотрите на анимацию с пандами, чтобы немного отдохнуть от этой информации.

Эта GIF-анимация зациклена. То есть в ней тоже есть элементы цикла:

«всегда» делай вот что:

Панды — вперёд!

Это тело выполняется столько раз, сколько заложено в цикле... 

В практике скриптов Google чаще нам требуются конечные циклы, когда мы перебираем ограниченное число элементов: строк таблицы, листов книги, формул, диапазонов. 

Цикл состоит из объявления, и тела:

for (var i = 0; i < sheets.length; i++) { }

Переменная i  — просто буква, и всё. Названия для переменных цикла часто делают простыми и ничего не значащими. Это потому что в цикле они служат счётчиком, и кроме номера ничего не означают. Счётчики в коде встречаются так часто, что бывает лень придумывать им названия сложнее чем i

— Выполняйся, пока i  меньше числа листов в книге < sheets.length ;

Любой массив содержит свой встроенный счётчик. Это удобно. Наш массив с листами книги тоже имеет счётчик: у каждого листа в этом массиве есть порядковый номер. Начало счётчика в скриптах нe единица, а ноль.

🤓 Любознательным на заметку:

1, 2, 3 4, 5, 6

0, 1, 2, 3, 4, 5

Счётчик всегда должен иметь конец, только если это не... маленькие панды. GIF-анимации бесконечны. Если анимация с пандами не даёт вам покоя, вот примерный код, который их запускает:

while (true) { // дословно «Пока правда», что и значит «Всегда»

  // Код, который выполняет один круг катания панд

  goPanda();

} // Конец объявления цикла. Прокатимся ещё разок?

Вернёмся опять к объявлению цикла:

for (var i = 0; i < sheets.length; i++)

Наша мясорубка автоматическая, она сама заканчивает работу, когда последний кусок мяса или лука перемолот. А как ей понять, когда это случиться? Откуда ей знать, когда патроны в ленте закончатся:

for (var i = 0; i < 3; i++)

Переведём эту запись так:

Мы получаем число листов, например 3, при помощи свойства массива листов sheets — его длины length:


Вася, Петя, Коля — это массив. Он содержит объекты.

Вася — это объект.

Массивы и обычные объекты отличаются. Вот например, раньше мы встречали file. Это был большой полноценный объект текущего файла. А вот sheets  — это несколько листов в списке. Массив sheets останется списком, даже если в книге всего один лист . Наводкой на то, что это массив является текст метода getSheets. Вспомните, как мы получали массив листов ранее: 

var sheets = file.getSheets();

В конце стоит буква s: getSheets. Это в английском множественное число. Оно и намекает на то, что мы получим массив, а не один лист.

🤓 Для любознательных. Проведите эксперимент:


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

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

 // Назначить лист

 sheet = sheets[i];

 // Добавить в результат имя листа и айди листа

 result.push([sheet.getName(), sheet.getSheetId()]); 

Получили элемент из массива листов по его индексу:

[i]

result.push(["Лист 1", 1234678]);

Попробуем выделить в этом коде то, с чем мы ранее не сталкивались:

Мы ранее объявили пустой массив, строка 7 кода: var result = []

Теперь мы получили в цикле лист sheet из массива листов sheets таблицы file. И самое время «втолкнуть» в результат resultpush — информацию об этом листе.  

Давайте представим, что имя листа и его айди нам известны. Запись может выглядеть так:

result.push(['Мой лист', 123456789]);

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

[['Sheet1'   , 0         ]

 ['Sheet2'   , 1433023430]

 ['Sheet3'   , 1346147852]

 ['Sheet4'   , 1288562066]

 ['Sheet5'   , 1197357750]]

Предлагаю посмотреть, как этот же результат выглядит в таблице.

В таблице есть колонки и строки. В скриптах Google этого нет. Такой же объект как в таблице — массив из строк, и колонок — помогают создать квадратные скобки, и запятые:

[['A1', B1'], ['A2', 'B2']]

↑ В качестве значений тут адреса ячеек. 

Маленькие массивы внутри главного массива — это строки [[строка], [строка], [строка]]. Элементы разделены запятыми. Нам прописывать эти запятые не нужно, потому что строки мы добавляем по одной с помощью команды push.  Массив формируется автоматически.

Значения внутри строк [[1,2,3], [1,2,3], [1,2,3]]— это ячейки. Они тоже разделяются запятыми. Тут мы прямо в коде используем запятую, потому что со строкой вписываем 2 значения сразу: [sheet.getName(), sheet.getSheetId()].

В конце функция вернёт результат: двумерный массив с информацией о листах книги. Функция готова! Она универсальна, то есть работает в любом файле таблиц Google. 

Теперь вы знаете, что внутри мясорубки.

Мы завершили написание кода. Создали функцию. Дальше будем использовать эту функцию в формулах таблицы.

f(x). Составные части формулы

Формулы в таблицах Google. Пересчёт

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

function getSheets()

Чтобы заставить её обновиться, пришлось добавить в неё аргумент уже в самой таблице:

=getSheets(F1)

Этот аргумент нужен самой функции для скрипта, и не влияет на работу кода. Поэтому в скрипте ничего менять не нужно.

Теперь вы можете обновлять формулу. Меняйте значение ячейки [F1], и формула будет пересчитываться. Вы «заставите» движок таблиц подумать еще раз.

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

Формулы в таблицах Google. Регион

Формулы в таблицах имеют региональные особенности. Вторая формула, из ячейки [C2] будет выглядеть иначе в зависимости от региона:

Русский вариант формулы:

=FILTER(ГИПЕРССЫЛКА("#gid=" & B2:B & "&range=A1";A2:A);A2:A<>"") 

Вариант с английскими именами функций:

=FILTER(HYPERLINK("#gid=" & B2:B & "&range=A1",A2:A),A2:A<>"") 

У вас будет русский вариант, если у вас регион — одна из стран восточной Европы, и функции названы по-русски. Различия:

Создатели первых электронных таблиц: Боб Фрэнкстон, Даниэль Бриклин.

🧙🏽‍♂️ Минутка истории. По-русски два с половиной пишутся через запятую: 

2,5

По-английски — через точку: 

2.5

В английской клавиатуре запятую вы можете ввести без зажатия клавиши [Shift]. Это быстрее, чем в русской клавиатуре, в русском языке больше букв, и в клавиатуру запятую не поместили.

Американцы–создатели первых электронных таблиц сделали разделителем между частями формул запятую. Они тогда не подумали о других странах, и других стандартах. 

Оказалось, что в части стран — не только в России, и Украине —  запятую использовать нельзя. 😱 Дробные числа компьютер не увидит: 2,5 из двух с половиной превратится в два числа: два и пять. 

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

, ;

Они же решили, что формулу SUBSTITUTE, которая на русский переводится как «подменить» лучше перевести как ПОДСТАВИТЬ, тем самым подставили всех нас...

Другие ребята, из команды Google решили часть функций таблиц Google перевести, при том взяли все переводы из Экселя, а часть функций не перевели. Например, FILTER — такой функции в Экселе нет, и русского имени у неё тоже нет. В итоге моя формула получилась двуязычной.

🤓 Для любознательных: почему я использую только английскую версию формул и интерфейса в таблицах Google:

Формулы в таблицах Google. Вложенность формул

Вернёмся к формуле:

=getSheets(F1)

и запишем её иначе:

=getSheets(

    F1

  

Такая запись разбивает логические части формулы на отдельные строки. В скриптах Google происходит то же. Вспомните предыдущий раздел, где был описан код функции getSheets() { … }. Части кода разделяют фигурными скобками {}.  В формулах за это отвечают круглые скобки. 

Эта формула простая, но даже её разбивка на части даёт нам подсказку: 

Длинные формулы

Если вы некоторое время работали с формулами, то, наверное, замечали что они бывают очень длинными. Иногда я вижу длинную формулу, которую я не понимаю. Чаще всего это мои собственная формула, написанная годом ранее. Чтобы разобраться в формуле, я разбиваю её на строки. Если вы не умеете это делать, попробуйте такое упражнение:

🤓 Задание для любознательных:

Если вы разобьёте сложную формулу на строки, вы увидите её составные части. У вас получится как-то так:

=FILTER(

   ГИПЕРССЫЛКА(

      "#gid=" & B2:B & "&range=A1",

       A2:A),

   A2:A<>"") 

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

Порядок вычисления всех формул одинаков:

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

Из-за этого некоторые формулы, особенно формулы с условиями if (ЕСЛИ), могут работать даже если в них содержатся ошибки. 

Ошибка не покажется, пока до неё не дойдёт очередь. Например, формула 

=ЕСЛИ(ИСТИНА ; 10 ; ОШИБКА) 

Такая формула сработает! 

Попробуйте сами:

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

Используйте эти знания, чтобы разобраться в длинной формуле. Каждая под-формула работает сама по себе. Мои «раскопки» идут по такому плану:

Давайте попробуем сделать тоже для формулы из примера. Запишу ее в 5 строк:

=FILTER(

   ГИПЕРССЫЛКА(

      "#gid=" & B2:B & "&range=A1",

       A2:A),

   A2:A<>"") 

Вначале возьмём то что внутри и выпишем в виде отдельной формулы:

="#gid=" & B2 & "&range=A1"

Примечание: здесь и далее я массивы типа B2:B заменяю на ячейки B2. Делаю это, чтобы формула сработала в одной ячейке, для теста.

Здесь куски текста в кавычках "#gid=", знак соединения текста &, и ссылка на B2. Заметьте:

Итого на выходе формула ="#gid=" & B2 & "&range=A1" даст результат #gid=0&range=A1:

Этот текст нужен функции, которая стоит на уровень выше — ГИПЕРССЫЛКА (HYPERLINK). Попробуем теперь взглянуть на уровень выше и написать в отдельной ячейке такой кусок:

=ГИПЕРССЫЛКА("#gid=" & B2 & "&range=A1", A2)

Введите эту формулу в ячейку: вы должны получить ссылку. То есть функция HYPERLINK умеет создавать ссылки! Причём она умеет создавать ссылки на ячейки самого файла. Это интересно. 

Продолжайте раскопки. Двигайтесь постепенно к первым функциям формулы. Вы доберётесь до верхнего слоя — функции FILTER

Попробуйте разобрать функцию FILTER на части и понять, как она действует. 

FILTER — это функция массива.  В таблицах Google есть такие прекрасные функции — функции массива. Это такие функции, применив которые в формуле в одной ячейке, вы получите результат в нескольких ячейках сразу. Работают они примерно так:

| =функция массива | → результат правее |

| ↓ результат ниже | ⇘ ниже и правее    |

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

Предлагаю посмотреть 2 видео про формулы с функцией FILTER:

На этом пока всё. Теперь вы знаете, как создать ссылки на листы в таблицах Google.

Спасибо, что дочитали статью до конца. Надеюсь, теперь вы точно разделяете мое мнение:

Онлайн-таблицы — это круто!

Я потратил на создание статьи более 26 часов. Буду признателен, если вы потратите пару секунд, и поделитесь ссылкой на сайт в соцсетях: https://www.cooltables.online

Спасибо!

Макс Махров

Разработчик онлайн-таблиц