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

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

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

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

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

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

  1. Если у вас большая таблица. Если у вас книга с множеством листов, удобно иметь отдельный лист с оглавлением.

  2. Если вы хотите разобраться в скриптах. Если вы новичок в скриптах Google, и хотите начать их изучение с нуля.

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

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

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

Попробуйте:

  1. Наведите мышью на ячейку с такой ссылкой

  2. Рядом появится окошко со ссылкой. Нажмите на эту ссылку.

  3. Что произойдет?

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

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

🎯 Решение

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

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

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

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

=getSheets()

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


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

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

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

Вставьте код

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

  1. Зайдите в меню Инструменты > Редактор скриптов.

    • В английской версии Tools > Script Editor

  2. В новом окне отроется редактор скриптов с открытой вкладкой Код.gs.

  3. Весь код внутри окна Код.gs замените на код из примера.

  4. После этого сохраните проект [Ctrl]+[S] и дайте ему любое имя.

  5. Дождитесь, пока проект сохранится, после этого окно с кодом можно закрыть.

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

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

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

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

=getSheets()

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

=getSheets(F1)

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

  1. когда копируете формулу, вначале зайдите внутрь ячейки:

    1. дважды щелкните мышью по ней,

    2. или используйте клавишу [F2].

  2. Второй вариант: вставляйте формулу в строку формул (над таблицей), а не в саму ячейку.

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

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

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

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

=getSheets(F1)

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

  1. Станьте в ячейку [F1]

  2. Перейдите в меню [Вставка] > [☑️ Флажок]

При каждом нажатии на флажок в [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<>"")

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

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

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

  1. Открыть таблицу.

  2. Открыть редактор скриптов Инструменты > Редактор скриптов

  3. Вставить туда код.

  4. Сохранить скрипт. Через меню файл, или нажав [Ctrl]+[S]

  5. Придумать проекту любое имя и подтвердить сохранение.

  6. В ячейку [А2] вставьте:

    1. Русский вариант: =ЕСЛИ(ТДАТА();getSheets(F1))

    2. Английский вариант: =if(now(),getSheets(F1)) .

  7. в ячейку [C2] вставьте:

    1. Русский вариант: =FILTER(ГИПЕРССЫЛКА("#gid=" & B2:B & "&range=A1";A2:A);A2:A<>"")

    2. Английский вариант: =FILTER(HYPERLINK("#gid=" & B2:B & "&range=A1",A2:A),A2:A<>"")

  8. Тестируйте формулу:

    1. Добавьте новые листы в книгу. Для этого нажмите на знак в нижней левой части экрана таблицы.

    2. Проверьте вашу формулу. Чтобы заставить её обновить список листов, нажмите на флажок ☑ в ячейке [F1].

  9. Добавить заголовки и форматы по желанию.

  10. Готово!

🤓 Объяснение

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

  1. Скопировать файл, к которому уже привязан код.

  2. Привязать код к файлу — это самому открыть редактор скриптов, скопировать код примера, и сохранить проект.

  3. Создать библиотеку с кодом, и привязывать только её. Этот третий способ мы пока упустим.

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

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

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

  • [Инструменты] > [Редактор скриптов] (Tools > Script Editor)

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

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

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

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

  1. Название проекта. Это название для вас, чтобы вы могли удобно ориентироваться, если вы работаете с несколькими проектами. У вас может быть несколько проектов в разных таблицах Google, и не только. Как правило, к одной таблице привязывают один проект, и все скрипты находятся в нём.

  2. Навигация. По умолчанию вы попадаете на страницу с кодом 〈 〉

  3. Кнопка запуска функции.

    • Запускать функцию из кода можно разными способами. В примере из этой статьи мы делаем на листе формулу, и в ней запускаем функцию из скрипта.

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

  4. Выбор функции. Если функций в скрипте несколько, вы вначале выбираете функцию для запуска, у потом нажимаете на треугольник (3) для запуска функции.

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

    • Пока вам это не надо, весь код находится в одном файле.

    • Если кода станет много, удобно его разделять на отдельные вкладки.

    • Кроме файлов в новом редакторе вы видите раздел для подключенных библиотек и приложений.

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

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

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

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

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

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

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

  1. пройдёмся очень подробно по составным частям скрипта.

  2. Вы узнаете, какие принципы заложены в код, как самостоятельно программировать на скриптах Google.

  3. Обращайте внимание на абзацы, выделенный знаком ☝️: это информация по специфике работы со скриптами Google.

Поехали!

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

  • function — кодовое слово, оно создает новую функцию скрипта.

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

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

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

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

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

function() { return 0; }

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

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

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

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


  • getSheets() — это имя нашей функции. "Get sheets" в переводе на русский значит «получить листы». Вы можете придумать функции другое имя. Вот моя логика для создания имени функции:

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

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

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

  1. назвать функцию по-русски: получитьЛисты.

  2. Не забудьте на листе тоже переименовать функцию с getSheets на получитьЛисты.

  3. Будет ли работать?


  • getSheets() — обратите внимание на скобки после имени функции. Они пустые, значит функция не нуждается во входящих данных.

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

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

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

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

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

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

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

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

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


  • getSheets() { } — поговорим о красивых фигурных скобках.

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

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

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

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

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

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

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

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

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

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

  • var file = — тут кодовое слово var — это от английского "variable", переводится «переменная».

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

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


  • var file = — теперь обращаю внимание на имя переменной. Тут похожая ситуация, как с именем функции. Имя переменной целиком зависит от вашей фантазии, и от ограничений движка: никаких пробелов и специальных символов, начинать имя только с буквы.

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

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

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

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

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

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

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


  • var file = — посмотрите на знак равенства. Он означает что мы хотим сразу присвоить переменной значение. Знак равенства служит вместо слов «содержит значение», слово var — вместо слов «Объявить переменную с именем». Читаем весь кусок кода var file =:

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

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

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

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

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

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

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

  • ☝️ var file = SpreadsheetApp.getActive(); — справа от знака равенства мы даём значение переменной. Тут значение состоит из 4 частей. Объясним их подробнее:

    • ☝️ SpreadsheetApp — это основа основ в программировании таблиц Google. Это объект, к которому я обращаюсь чаще всего. Он как склад, который содержит всё что вам нужно знать в коде о табличках.

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

  1. Зайдите в меню Инструменты > Редактор скриптов.

  2. Введите буквы Sp и затем

  3. 🎩+🐰 нажмите [Tab]

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

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

  1. в свободной строке кода введите слово App

  2. У вас получится список, на который можно ориентироваться.

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

  4. Вывод: изучать программирование и писать код желательно одновременно. Сам редактор скриптов подскажет вам, как правильно.

    • ☝️ SpreadsheetApp. — точка. Точка для кода служит соединительным элементом между объектом и его меньшими частями.

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


    • ☝️ SpreadsheetApp.getActive() — метод getActive() вернет объект текущего файла.

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

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

var file = SpreadsheetApp.getActive();

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


    • var file = SpreadsheetApp.getActive(); — точка с запятой говорит о том, что строка кода закончилась. Для правильной работы скрипта точка с запятой не нужна, достаточно начать с новой строки. Я привык писать ; в коде, поэтому всегда их использую. А можно это и не делать, решать вам.


Читаем всю фразу 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. Из книги получаем её листы. Когда несколько переменных вытекают одна из другой — это цепочка переменных:

  • ☝️ Цепочка переменных. Вот строки 3 и 5 кода:

var file = SpreadsheetApp.getActive();

var sheets = file.getSheets();

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

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

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

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

  1. Введите этот код вручную, не копируя его:

    • var file = SpreadsheetApp.getActive();

    • var sheets = file.getSheets();

  2. Вы увидите, что после ввода переменной file и точки после неё, редактор опять предложит вам подсказку.

  3. Всплывающая подсказка — это очень удобно.

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


  • ☝️ A.b.c — цепочка переменных в одну строку кода. Конечно, код A.b.c — не настоящий, это упрощенный пример цепочки кода. Далее мы покажем как сделать из нашего примера цепочку переменных в одну строку.

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

  1. Откройте редактор скриптов с кодом из примера.

  2. Воспользуйтесь поиском по коду: [Ctrl]+[F]. В окно поиска введите текст: file.

  3. Заметьте: переменная file с файлом последний раз встречается в строке 5, где мы из файла получаем список листов этого файла.

    1. Раз переменная не встречается дальше в тексте кода, от нее можно избавиться.

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


  • ☝️ Вложенность.

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

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


  • ☝️ Объектная модель.

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

    1. Приложение табличек — это главный объект: SpreadsheetApp.

    2. Чтобы получить всех его подчинённых, после него вы ставите точку.

    3. После точки получите большой выпадающий список.

      • Это огромный инвентарь, но не теряйтесь. Я сам всего в нём не знаю, а пользуюсь только несколькими элементами этого списка.

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


  • ☝️ Выбрать правильный элемент.

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

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

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

SpreadsheetApp.getActive()

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

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

  1. getActive() это метод, а не свойство потому что у метода, как и у функции, есть скобки (),

    • в случае с методом getActive() переменные не нужны, и скобки всегда пустуют,

    • а есть методы, которым нужны переменные на вход.

  2. Все методы есть в справке. Например, в этой справке вы найдете все свойства и методы SpreadsheetApp

      • Properties

      • Methods

Метод

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

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

Вызов метода

Вася, принеси (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!

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

  1. Пойти на склад с ящиками SpreadsheetApp.

  2. На складе я должен взять нужный мне ящик getActive().

  3. Мне нужны детали из этого ящика: getSheets().

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

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

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

var result = [], sheet;

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

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

  1. Массив [] — получаем через квадратные скобки, внутри которых ничего нет. Пока это пустой массив. Позже добавим в него элементы. Массив станет не пустым: [Sheet0, Sheet1. Sheet3].

  2. sheet — переменная без значения. После неё нет знака равенства со значением. Это «пустая» переменная, пока.

  3. var result = [], sheet — объявили две переменные через запятую.

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

  1. Создайте новый скрипт.

    • 🎩+🐰 Для этого вместо адреса сайта вставьте текст script.new. Нажмите ввод [Enter]. Или перейдите по ссылке: https://script.new

    • Вы получите новый скрипт, который не привязан к таблице. Это отдельно стоящий скрипт.

  2. В окне кода:

    • удалите старый код и добавьте: function test() { }

    • Внутри фигурных скобок объявите пустую переменную: var vopros;

    • Добавьте ещё одну строку и введите код: Logger.log(vopros);

  3. Сохраните скрипт и проект [Ctrl]+[S], дайте проекту любое название.

  4. Сверху нажмите на треугольник ▶ чтобы запустить код из редактора.

    • Вы увидите на экране окно результата.

    • Подождите, пока результат появится: секунд 5.

    • Код вернёт результат на экран: значение переменной vopros.

    • Чему оно равно?

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

  • var result = []. Массив [].

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


  • var result = [], sheet. Еще раз обращаем внимание на роль запятой в этом куске кода. Мы через запятую перечисляем похожие вещи в коде. Так мы можем написать var только один раз в начале строки, и объявить несколько переменных после var.

Тут переменных две: 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-анимация зациклена. То есть в ней тоже есть элементы цикла:

  1. Объявление цикла: в случае с пандами нужен бесконечно работающий цикл, поэтому объявление в этом примере простое:

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

  1. Тело цикла — один круг, который совершают панды:

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

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

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

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

  • Объявление цикла — строки 9 и 14

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

    • for — «для» — специальное слово, которым начинается цикл.

    • () — скобки. Внутри них мы объявляем, что мы будем перечислять в цикле.

    • var i = 0; — первая часть объявления цикла. Объявляем счётчик i — будем менять его при каждом шаге цикла. Цикл меняет значение этой переменной начиная с нуля.

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

    • i < sheets.length; — вторая часть объявления цикла. Говорим циклу:

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

    • i++ — третья часть объявления цикла. Такая запись — два плюса — говорит счётчику увеличиваться на единицу с каждым новым кругом.

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

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

  1. Счётчики в программировании отличаются от того, к чему мы привыкли.

    1. Обычные люди считают до шести так:

1, 2, 3 4, 5, 6

    1. В скриптах Google счётчик из 6 элементов выглядит так:

0, 1, 2, 3, 4, 5

  1. Эти номера называются индексы, они всегда стартуют с нуля, а не с единицы.

  2. Поэтому в объявлении цикла переменная счётчика вначале равна нулю: var i = 0.

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

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

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

goPanda();

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

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

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

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

      • < — меньше. Знаки сравнения в скриптах похожи на математические. То есть порядковый номер «патрона» будет меньше общего числа патронов в ленте. Помним, что в программировании номера патронов стартуют с нуля, поэтому знак меньше. Для примера с тремя патронами 0, 1, 2 номер патрона должен быть меньше их общего числа: меньше трёх. Если заменить sheets.length на 3, объявление цикла станет понятнее:

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

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

  1. возьми все элементы стартуя с индекса 0,

  2. прибавляй к счётчику i по единице каждый круг,

    1. Первый круг — i=0

    2. Второй круг — i=1

    3. Третий круг — i=2

  3. Считаем, пока индекс не станет равен трём.

  4. Три уже не меньше верхней границы: трёх. Условие не выполняется — стоп машина! Останови цикл.

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

      • sheets — массив с листами.

      • length — это свойство любого массива, в переводе с английского «длина». Свойство length подскажет, сколько в массиве элементов.


      • Массивы и объекты.

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

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

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

var sheets = file.getSheets();

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

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

        1. Введите такой код чтобы получить пустой массив: var a = [];

        2. С новой строки введите следующее: a.

          1. Какой список вы получите?

          2. Будет ли в этом списке свойство length?

          3. Если есть length, это, скорее всего, массив.


  • Тело цикла:

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

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

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

sheet = sheets[i];

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

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

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

  • sheet = sheets[i]; — строка 10 кода, тут уже знакомые элементы. Переменная под названием лист sheet по очереди принимает значения разных листов книги. В неё, конечно, записываются не сами листы, а их отображение на языке скриптов. Порядковый номер по правилам работы с массивами вписывается в квадратные скобки:

[i]

  • result.push([sheet.getName(), sheet.getSheetId()]); — в переменную с результатом мы добавили новый элемент — информацию об имени листа sheet.getName(), и айди листа sheet.getSheetId(). Упрощенно это выглядит так:

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

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

  • sheets[i] — используем переменную счётчика i. В цикле эта переменная будет меняться от нуля до последнего элемента. Если, например, листов в книге 5, то счётчик будет таким: 0 → 1 → 2 → 3 → 4.

  • sheets[i] — смотрим на квадратные скобки. [] — это стандарт языка. Тут опять всё просто: надо запомнить, что к элементам массивов мы обращаемся через квадратные скобки.

  • result.push([sheet.getName(), sheet.getSheetId()]); — слово push в переводе «толкни».

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

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

  • result.push([sheet.getName(), sheet.getSheetId()]); — жирным выделены методы листа из переменной sheet.

    • getName()— вернёт имя листа, а

    • getSheetId()— вернёт айди листа.

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

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

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

[['Sheet1' , 0 ]

['Sheet2' , 1433023430]

['Sheet3' , 1346147852]

['Sheet4' , 1288562066]

['Sheet5' , 1197357750]]

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

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

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

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

  • result.push([sheet.getName(), sheet.getSheetId()]); — посмотрите на квадратные скобки и запятые. Они работают так:

  1. У нас был пустой массив result = [] открытый квадратными скобками.

  2. Массив result был одномерный.

  3. Добавили в него ещё массив — элемент в квадратных скобках: result.push([]).

  4. Получится двумерный массив [[]]. Двумерный массив соответствует структуре самой таблицы. Это массив, элементы которого — одномерные массивы.

Маленькие массивы внутри главного массива — это строки [[строка], [строка], [строка]]. Элементы разделены запятыми. Нам прописывать эти запятые не нужно, потому что строки мы добавляем по одной с помощью команды 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<>"")

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

  1. Разделитель частей формулы не запятая, а точка с запятой.

  2. Названия некоторых из функций переведены на русский.

  3. Есть и другие отличия. Они произошли от того, что в мире нет единого стандарта записи дробных чисел.

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

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

2,5

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

2.5

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

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

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

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

,;

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

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

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

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

    • 🎩+🐰 В меню Файл > Настройки таблицы вы можете выбрать пункт: использовать названия функций на английском языке.

    • Там же в настройках таблицы вы выбираете регион. По умолчанию регион будет вашим родным для новых таблиц. Когда вы копируете таблицу, вы копируете её вместе с настройками региона. У меня был регион США.

  2. Если мне нужна справка или помощь по какой-то функции, мне будет легче найти информацию на английском.

    • Некоторые вещи наоборот лучше ищутся на русском, но большинство технических задач решено на английском.

  3. Я раньше получаю обновления.

    • У меня стоит весь интерфейс на английском. Когда в русской версии еще не было новых шрифтов, в английской они уже появились.

    • Если вы тоже хотите поменять интерфейс на английский, вам нужно будет порыться в настройках аккаунта. Где именно рыться, ищите по запросу Google Change Default Language. Выбор языка повлияет на все сервисы, включая почту Gmail, и поисковик.

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

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

=getSheets(F1)

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

=getSheets(

F1

)

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

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

  1. вначале Движок попытается вычислить то, что находится в ячейке F1.

  2. После этого сверит это с тем, что было раньше, и

  3. если в F1 что-то изменилось, формула будет пересчитана.

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

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

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

  1. Выберите длинную формулу для тренировки. Сгодится наша вторая формула из примера, которая находится в ячейке [C2]: =FILTER(ГИПЕРССЫЛКА("#gid=" & B2:B & "&range=A1",A2:A),A2:A<>"")

  2. Скопируйте мой пример с формулой, если у вас его еще нет.

  3. Попробуйте разбить формулу из [C2] на строки, и добавить отступы. Например, так, как это вышло у меня. Вам помогут следующие приёмы:

    1. Станьте в клетку, где находится формула. Вы увидите её на панели формул, которая находится над таблицей.

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

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

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

      • 🎩+🐰. Чтобы добавить новую строку внутри формулы, используйте сочетание клавиш: [Ctrl] + [Enter]

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

=FILTER(

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

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

A2:A),

A2:A<>"")

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

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

  • сначала выполняется первая под-формула, но когда движок натыкается на то, что ему неизвестно,

    • он начинает выполнять внутренние формулы, и повторяет процесс снова,

      • пока не дойдёт до последней вложенной функции и не вычислит её.

    • Когда движок вычислит то, что внутри, он постепенно вычислит всё что ему надо для результата.

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

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

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

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

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

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

  1. введите её в ячейку.

  2. Результат будет 10, пока вы не поменяете кое-что в формуле...

  3. Поменяйте истину на ложь, чтобы увидеть ошибку.

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

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

  1. беру часть большой формулы — формулу поменьше

  2. выписываю её в отдельную ячейку со знаком = перед ней,

  3. так я получаю «под-формулу». Эта под-формула должна работать сама по себе.

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

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

=FILTER(

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

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

A2:A),

A2:A<>"")

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

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

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

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

  • Текст в формулах пишется всегда в двойных кавычках.

  • Знак конкатенации или амперсанд, или английское И (&) помогает склеить 2 куска текста.

Итого на выходе формула ="#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.

Спасибо!

Макс Махров

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