Google Таблицы

Сообщение в Телеграм чат при любом редактировании столбца.

Посмотрите на скриншот - такой вопрос пришел в наш чат.

Показываем минимальный код, с помощью которого задачку можно решить. Код вставляется в редактор скриптов Таблицы (Расширения > Apps script), после функцию send нужно положить на триггер изменения Таблицы (Расширения > Apps script > Триггеры > создать Триггер > при редактировании Таблицы > функция send).

В коде нужно заполнить botToken, ввести один или несколько chatIds, на которые будут отправляться сообщения (можете ввести и username канала). Чтобы узнать chatId - используйте @idBot в телеграм.

Бот сможет отправлять сообщение только если он в чате или на канале с правами отправлять сообщения либо если пользователь уже ему писал и после этого не блокировал.

Как искать ошибку, если код не работает: Расширения > Apps script > Количество выполнений > кликаем на неудачное выполнение, читаем ошибку и исправляем.

Код: pastebin.com/dBevx4L3

Чат: @google_spreadsheets_chat
Канал: @google_sheets

Отправлятор / Удалятор постов и сообщений Телеграм, апдейт

Наш подписчик Михаил сделал новую версию Таблицы с Отправлятором.

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

Таблица

Всё крутится на библиотеке на GAS для Telegram Bot API: github.com/Guf-Hub/TGBot

@nosaev_m, спасибо! 😎

Данные с разных листов, на которых разная структура

Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?

Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.

Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).

Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данные

MATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)

ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)

OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.

А логика формулы в общем виде такая:

=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))

Таблица с примером

ВПР-им с разных листов

Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.

Обычная ссылка на другой лист выглядит так:
='Москва'!A:B

Нам нужно подставлять внутри апострофов названия разных листов.

Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
="'" & ячейка с названием листа & "'!диапазон"

Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
=INDIRECT("'" & ячейка с названием листа & "'!диапазон")

И отправляем это внутрь ВПР'а как второй аргумент:
=VLOOKUP(значение для поиска; INDIRECT("'" & ячейка с названием листа & "'!диапазон"
) ; номер столбца ; 0)

Ссылка на таблицу с примером

Каналы, которые мы читаем (иногда ведем) и рекомендуем

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

Но прежде всего приглашаем в наш табличный чат. Это как бы и не канал, зато можно зайти, спросить и получить помощь. И, наоборот, помочь другим:
https://t.me/google_spreadsheets_chat

От Жени
Орёл и кошка - этот канал ведут мои друзья, пара, которая релоцировалась в Турцию с котами. Про жилье, еду и местный колорит.

Например:
– Как улететь с котом в Армению: t.me/orel_i_koshka/20

– Влажный бургер! Семейный бизнес, превратившийся в туристический феномен Стамбула: t.me/orel_i_koshka/73

От Рената

CEO Readz - сооснователь, совладелец и в прошлом CEO издательства МИФ Артем Степанов про деловые книги.

Николай Павлов (Планета Excel) - главный маэстро Excel в России теперь есть в Телеграме. Ура! Николая, его статьи и книги категорически рекомендую.

Личный канал - это как моя личная страничка в соцсети. Ни слова про таблицы. Бег, книги, футбол и прочая жизнь. Заглядывайте в гости

Функция ОКРУГЛТ / MROUND - округление с заданной точностью

Друзья, хотим рассказать вам об этой весьма полезной функции округления.
Она позволяет округлять числа с заданной точностью: до ближайшего числа, кратного заданному во втором аргументе.

Допустим, вы хотите округлить цены, чтобы они всегда заканчивались на 0, тогда вторым аргументом функции будет 10.
А если надо, чтобы цены заканчивались на 0 или 5 - то 5 (число будет округляться до ближайшего кратного 5).

=MROUND(число;точность)

Таблица с примером

Отправлятор / Удалятор постов и сообщений Телеграм
(на гифке - удаление сообщений)

Друзья, вы часто спрашиваете про Телеграм ботов и поэтому принимайте.

С помощью нашей Таблицы вы сможете и отправлять и удалять ботом сообщения как в каналах (для этого бот должен быть админом канала с правами), так и в личных чатах с пользователями (для этого бот должен уже контактировать с пользователем в личном чате, по крайней мере кликнуть /start)

❗️ Полная инструкция
🚜 Таблица
⚙️ Код отдельно

Наш чат

Отправлятор / Удалятор постов и сообщений Телеграм
(на гифке - отправляем сообщения, в т. ч. делаем реплай)

Два скрипта от нашего подписчика
– создаем список листов
– вставляем выпадающие списки по этому списку

Друзья, недавно в нашем чате Александр Кулешов предложил два скрипта для решения двух проблем:

1. первый скрипт создаёт список листов Таблицы и вставляет его на лист, помимо списка листов вставятся чекбоксы (выключенные);

2. второй скрипт проходится по этому списку листов и если пользователь напротив названия листа активировал чекбокс – скрипт вставляет в выбранный столбец этого листа выпадающие списки из листа "значения";

Таблица с примером (делайте копию, чтобы попробовать): тут

Код скриптов отдельно: pastebin.com/XZ70VzmU

Спасибо Александру, заходите в наш чат и изучайте скрипты и Таблицы, на нашем канале множество примеров – оглавление 😎

Проектная диаграмма - Условное форматирование

Как сделать нечто вроде проектной диаграммы за счет заливки ячеек условным форматированием:
1. Сначала с помощью SEQUENCE формируем последовательность дат - от начала первого этапа до окончания последнего. Здесь в столбце B - даты начала этапов проекта, в C - окончания.
=SEQUENCE(1;МАКС(C:C)-МИН(B:B)+1;МИН(B:B);1)

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

3. Создаем правило условного форматирования. Наша задача красить ячейку, если дата в ее столбце входит в период из ее строки. Можно сделать у ячеек границы какого-нибудь цвета, это будет более четко выделять отдельные дни (в примере желтые границы, и синяя заливка в условном форматировании).

Формула будет выглядеть так:
=AND(первая дата>=начало первого этапа;первая дата<=окончание первого этапа)

=AND(D$1>=$B2;D$1<=$C2)

Первая строка закреплена $, так как мы всегда смотрим на дату в этой строке (это заголовки). А у начала и окончания этапов закреплены столбцы, чтобы при смещении вправо мы в любом случае сравнивали каждую дату с началом и окончания этапов. Строки 2 не закреплены, так как в следующих строках мы уже смотрим на даты следующих этапов.

P.S. Если хочется исключить выходные дни, можно добавить условие (номер недели < 6, то есть не СБ или ВС) в условное форматировании:
=И(D$1>=$B2;D$1<=$C2;ДЕНЬНЕД(D$1;2)<6)
=AND(D$1>=$B2;D$1<=$C2;WEEKDAY(D$1;2)<6)

Дальше - только полет вашей фантазии. Можно отдельным правилом красить выходные другим цветом. Каким-нибудь грустным, если все-таки предполагается там работать 😥

Ссылка на таблицу с примером (Сделать копию)

Еще про диаграмму Ганта: Диаграмма Ганта с помощью одной формулы

Сумма прописью по-английски

Друзья, сегодняшний скрипт позволяет получить сумму прописью по-английски в формате "ONE THOUSAND ONE HUNDRED USD DOLLARS AND 50 CENTS" для 1100,50.

Спасибо за скрипт Людмиле из нашего чата, за основу она взяла вопрос на stackoverflow с примером подходящего скрипта и немного доработала его.

Скрипт будет работать и как пользовательская функция – смотрите скриншот. Просто вставьте код в редактор скриптов, далее введите в таблице =words() и сошлитесь на ячейку с числом.

Таблица с кодом
Код отдельно

PS Также по этой теме есть дополнение для Google Таблиц "numbertext" от Александра Иванова

Топ-20 упоминаний ссылок нашего канала за полгода (1.11.21 - 30.04.22)

★ Чат: @google_spreadsheets_chat
★ Больше постов в оглавлении нашего канала: goo.gl/HdS2qn

Друзья, @vitalich выгрузил все сообщения из нашего чата и составил топ самых упоминаемых постов нашего канала. Присмотритесь – вполне возможно, найдете полезное для себя.

1. Полный справочник по QUERY t.me/google_sheets/616

2. СОБИРАТОР 4.0 t.me/google_sheets/661

3. Считаем сумму по каждой строке / столбцу в формуле массиве t.me/google_sheets/502

4. Народный Telegram бот❗️ Отправляем сообщения прямо из Таблицы t.me/google_sheets/556

5: DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы t.me/google_sheets/843

6: 🚜 Собиратыр-тыр-тыр 🚜 t.me/google_sheets/626

7: OR / AND в функции FILTER t.me/google_sheets/434

8: 8 советов о том, как ускорить работу вашего документа в Google Таблицах t.me/google_sheets/143

9: Сравнение списков в Таблицах. Способ 3 — сравниваем функциями FILTER / MATCH t.me/google_sheets/515

10: ​​В правиле условного форматирования можно использовать данные другого листа t.me/google_sheets/338

11: Telegram бот, который записывает всё, что видит в Google Таблицу t.me/google_sheets/541

12: ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты t.me/google_sheets/719

13: отправлятор таблиц в телеграм чаты. бесплатно и по расписанию t.me/google_sheets/604

14: ОТПРАВЛЯТОР 2.0. Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию t.me/google_sheets/643

15: Скрипт: создаём в Таблице триггер и скрипт, который не увидят редакторы и владелец t.me/google_sheets/753

16: Проблемы с IMPORTRANGE() t.me/google_sheets/822

17: Отображаем функцией =IMAGE() картинки из Google Диска в ячейке t.me/google_sheets/467

18: Важный скрипт. Связанные выпадающие списки из кэша t.me/google_sheets/408

19: VLOOKUP(ВПР) + SORT t.me/google_sheets/13

20: Как одной функцией ВПР (VLOOKUP) подтянуть данные сразу из трех столбцов t.me/google_sheets/57

P.S. @vitalich, спасибо в очередной раз!

Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела

Дано: на одном листе список сотрудников и их отделы/департаменты/что-то еще. На другом - календарный/временной план, в котором мы расставляем сотрудников на определенные даты, смены, временные слоты.
Нужно: красить одним цветом всех сотрудников одного отдела, другим цветом - сотрудников другого, и тэ дэ.

Решение: создаем правило условного форматирования, ВПР-им каждого сотрудника (первую ячейку форматируемого диапазона, в нашем примере это B2), находим его отдел и проверяем, в зависимости от значения отдела применяем ту или иную заливку.

Чтобы условное форматирование работало с диапазоном с другого листа, на диапазон ссылаемся через функцию INDIRECT / ДВССЫЛ:
=ВПР(B2;ДВССЫЛ("'Список сотрудников'!A:B");2;0)="Оплот"

Можно ссылаться на именованный диапазон:
=ВПР(B2;ДВССЫЛ("Список");2;0)="Замок"

Таблица с примером

Возвращаем ссылку на диапазон с помощью функций

Есть несколько волшебных функций, которые будут возвращать ссылку на ячейку, если разделить их двоеточием.
То есть мы можем задать начало и конец диапазона функциями/формулами.

Например, мы можем находить значение с помощью сочетания INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ. Если после поставить двоеточие, то вместо значения будет возвращаться ссылка на ячейку.

В следующем варианте ищем два значения - превращаем каждое из значения в ссылку за счет двоеточия, получаем на выходе диапазон и его суммируем

=SUM(INDEX(диапазон суммирования;MATCH(что ищем-1;диапазон поиска;0)):INDEX(диапазон суммирования;MATCH(что ищем-2;диапазон поиска;0)))

Магия работает в Excel и Google Таблицах со следующими функциями:
CHOOSE / ВЫБОР
IF / ЕСЛИ
IFS / ЕСЛИМН
INDEX / ИНДЕКС
INDIRECT / ДВССЫЛ
OFFSET / СМЕЩ
SWITCH / ПЕРЕКЛЮЧ
VLOOKUP, HLOOKUP, LOOKUP / ВПР, ГПР, ПРОСМОТР
XLOOKUP / ПРОСМОТРX (удовольствие, доступное в Microsoft 365 / Excel 2021)

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

Таблица с примерами

Диалоговое html-окно 2. Сделаем переключатель (radio button).

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

Выведем в Таблице HTML-окно с Яблоко / Банан / Арбуз и кнопкой "отправить". Нажатие на кнопку отправит выбор во внешний скрипт, а он, в свою очередь, выведет этот выбор в Таблицу. Также нажатие на кнопку закроет окно (за это отвечает google.script.host.close())

Весь код, который нужен

1) Часть Google Apps Script (вставляем на лист gs)
function showDialog() {
var html = HtmlService.createTemplateFromFile('Page2').evaluate()
.setWidth(200) //размеры в пикселях
.setHeight(200);
SpreadsheetApp.getUi()
.showModalDialog(html, "@google_sheets"); //второй аргумент - заголовок окна
}

function script1(v) {
SpreadsheetApp.getActive().toast(v);
}

2) Часть html (вставляем на html-лист "page" в редакторе скриптов)
<p>Выбирайте</p>
<input type="radio" name="gender" value="Яблоко">Яблоко
<br>
<input type="radio" name="gender" value="Банан">Банан
<br>
<input type="radio" name="gender" value="Арбуз">Арбуз
<br>
<button type="button" onclick="displayRadioValue(), google.script.host.close()">Отправить!</button>

<script>
function displayRadioValue() {
var ele = document.getElementsByName('gender');
for(i = 0; i < ele.length; i++) {
if(ele[i].checked) {
break;
}
}
google.script.run.script1(ele[i].value);
}
</script>

Таблица с примером
HTML в GAS
Radio Button

Встраиваем видео в Таблицу

Оглавление нашего канала: тыц
Наш чат: тыц-тыц