Оглавление телеграм-канала в Google Таблицах

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

Рассмотрим на примере оглавления канала "Бегать просто", который я веду.

Ссылка на оглавление

Итак, у нас есть внутренний файл - список публикаций. У него есть доступ только у нас - партнеров канала. Ничего хитрого: в нем я вставляю название публикации, ссылку на нее, автора и дату:

А в оглавление публикации попадают автоматически, и там отображаются только названия, даты и темы. А ссылка сливается с названием - при щелчке на название вы попадаете в публикацию. Незачем здесь разделять название и ссылку, как в плане публикаций.

Как формируется этот список?

Первые два столбца - с помощью QUERY и IMPORTRANGE:

=QUERY(IMPORTRANGE("ссылка";"A2:F");"SELECT Col1, Col6 WHERE Col4 <>''";0)

где ссылка - ссылка на файл с планом публикаций.

Загружаем весь список публикаций, берем только столбец с датой и столбец с тегом. Условие - четвертый столбец (ссылка) не пустой. То есть все публикации со ссылками.

Третий столбец - чуть сложнее.

=ARRAYFORMULA(ГИПЕРССЫЛКА(QUERY(IMPORTRANGE("ссылка";"A2:F");"SELECT Col4 WHERE Col4 <>''";0);QUERY(IMPORTRANGE("ссылка";"A2:F");"SELECT Col2 WHERE Col4 <>''";0)))

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

В качестве первого аргумента берем все ссылки из столбца 4 исходного файла.

В качестве второго - заголовки из второго столбца.

Загружаются столбцы все так же, с помощью QUERY и IMPORTRANGE. По условию, что в столбце с ссылками не пусто.

Результат: в ячейках заголовки всех статей, но уже с ссылками:

Это не все. В оглавлении есть второй лист - "по темам":

Он реализован с помощью двух формул.

Первая выводит список тем:

=ТРАНСП(UNIQUE('Хронология'!B2:B))

То есть берет все темы из столбца B предыдущего листа, оставляет только уникальные (UNIQUE) и поворачивает на 90 градусов (ТРАНСП), чтобы темы шли по столбцам, а не по строкам в одном столбце. Если появится новая тема, для нее автоматически создастся новый столбец.

Ну а далее под каждой темой протягиваем функцию FILTER:

=FILTER('Хронология'!$C$1:$C;'Хронология'!$B$1:$B=A$1)

Которая забирает все заголовки из столбца C только по одной тематике - ну а тематики мы только что вывели в первую строку. Оттуда и берем.

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