Телеграм + Google Таблицы, наши решения

Друзья, привет. Мы с 2017 года ведём наш канал в Телеграм и иногда публикуем свои скрипты, которые с этой платформой взаимодействуют. Все посты – готовые и бесплатные решения, которые можно скопировать, прочитать инструкцию и начать использовать.

Простой Телеграм-бот (гифка установки), логирует все, что ему пишут в Google Таблицу, также из Таблицы можно отправлять сообщения в Телеграм-чаты и каналы

Еще один Телеграм-бот, извлекает из Таблицы данные по запросу и отправляет в Телеграм

Отправлятор 2, отправляет по расписанию диапазоны из Таблицы в Телеграм (pdf / jpg)

Регулярный бэкап Таблиц в формате XLSX в Телеграм

ВЫГРУЖАТОР, достаёт скриптом количество подписчиков чатов и каналов в Телеграм

Скрипт внутри Google Формы,
— берёт последний ответ и отправляет его в Таблицу
— отправляет в Телеграм чат
— отправляет на электропочты

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

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

Ну а COUNTUNIQUEIFS позволяет считать уникальные значения с условиями — например, посчитать, сколько клиентов приобретали у нас консультации.

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

Обновление нашей Таблицы WB: загружаем отчет по реализации из API

Продолжаем разговор и продолжаем добавлять полезное в нашу Таблицу WB.

Отчёт по реализации – главный отчёт продавца Wildberries. Внутри отчёта – прибыль продавца за каждый товар, также те комиссии, которые продавец заплатил площадке и другие поля.

Мы добавили возможность загрузки этого отчёта в нашу Таблицу WB – вводите диапазон дат, за который хотите получить отчёт на листе "Реализация" и нажимайте на кнопку "отчёт по реализации" в меню.

❗Обратите внимание, WB вернёт вам ошибку, если запросите слишком большой диапазон дат, да и в Таблицу вы не сможете вставить слишком много.

Наша Таблица WB (уже умеет загружать остатки FBO и отчёт по реализации)

Краткое описание работы скрипта

Документация метода

Если у вас есть интересные решения с WB, которыми можно поделиться - поделитесь в комментариях. Ждите обновлений и они будут :)

Видеоурок: подсчет и суммирование по условиям

В продолжение темы функций SUMIF(S) и подобных предлагаем вашему вниманию видео по теме. В нем и про основы работы с этими функциями, и про символы
подстановки.

https://www.youtube.com/watch?v=HsBt0_IWoyA

Это один из 90 уроков курса "Гугл Драйв" в МИФе.

Если вам нужно посчитать сумму (или еще что) только в строках, которые отличаются от других выравниванием (печально, но вдруг?) — можно использовать функцию CELL / ЯЧЕЙКА.

Первый аргумент — параметр, второй — ссылка на ячейку.

Параметр "prefix" показывает выравнивание. Функция с таким параметром будет возвращать одно из трех значений:
^ — по центру
' — по левому краю
" — по правому краю

Распознаем текст на изображениях прямо в Google Таблицах

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

Таблица со скриптом для распознавания текста

Как это работает - подаёте на вход PDF, Google Документ, изображение, файл должен лежать на вашем Google Диске.

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

На скриншоте слева изображение, а справа – распознанный результат.

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Добавляем комментарий к формуле

Немного экзотики. Функция с очень коротким названием N / Ч превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль.

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

Например:
=E2*15% + Ч("Вычисляем комиссию как 15% от суммы сделки")

Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри формулы текст есть, а к результату эта часть ничего не добавляет.

Еще один вариант для комментариев в формуле — функция LET.

Комментарии — лишь повод про нее напомнить, так как функционал у нее шире.

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

LET(имя1; значение_имени1; [имя2; значение_имени2]; …; вычисление)

Давайте посмотрим на совсем простой пример — зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET:
=LET(a;50;b;10;a*b)
На выходе будет 500.

В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a:
=LET(a;50;b;10*a;a*b)
На выходе будет 25000.

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

Возвращаясь к нашей теме с "комментариями": можно задать переменную (с любым названием) и присвоить ей текстовое значение.
=LET(переменная; "комментарий"; [другие переменные для вычислений]; ... ; вычисление)

P.S. Функция LET появилась не так давно — что в таблицах, что в Excel. И это значит, что в отличие от Ч/N при скачивании таблицы на локальный диск функция будет работать только в Excel 2021 и Microsoft 365.

У вас есть список событий/записей с датами и именами/названиями событий.
Например, записи клиентов на услугу; пациентов на госпитализацию и т.п.

И вы хотите собрать расписание, склеить все события/записи, которые будут в один день, в одну строку.

Сначала собираем все значения, соответствующие каждой очередной дате — с помощью функции FILTER:
=FILTER(имена/события;столбец с датами=очередная дата нашего расписания)

А потом полученный список остается склеить в одну ячейку с помощью TEXTJOIN. В качестве разделителя выбирайте любой по вкусу. Если хотите, чтобы все было в одной ячейке, но в разных строках, используйте перенос строки, который можно добыть функцией CHAR / СИМВОЛ с кодом 10.
=TEXTJOIN(CHAR(10);;FILTER(...))

Остается добавить сверху IFNA, чтобы заменить ошибки N/A (в случаях, когда ни одного события на дату не нашлось) на ничего.
=IFNA(TEXTJOIN(CHAR(10);;FILTER(...));)

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

PS А вот тут мы писали, как сделать TEXTJOIN по каждой строке с помощью LAMBDA

Друзья, привет!
На связи Ренат, приглашаю вас на практикум по сводным таблицам, который пройдет в июне.

На скриншоте — один из множества слайдов, которые я готовлю к июньскому практикуму по сводным таблицам (самому мощному инструменту для анализа данных в Excel и Таблицах).

Правда, на встречах слушатели этих слайдов не увидят. Вот еще — время тратить на презентации на уроках :)
Все время (3 по 2 часа) проведем в Excel (ну и малость в Google Таблицах), а слайды — это как мини-методичка для участников, чтобы потом освежить в памяти знания.

Еще будут домашки, их разбор (и подарки авторам лучших работ), файлы-примеры до и после, ответы на вопросы.

Приходите, вебинары будут 14, 20 и 23 июня.

Для вас — скидка 37% по промокоду Excel23, которая будет действовать до 5 июня включительно.
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Система учета рабочего времени по локации из телеграм бота.

Сегодня - пост от старожила нашего чата Каната (@akanat), передаём слово ему:

Рано или поздно у руководителя встает вопрос автоматизации отметки прибытия и убытия сотрудников на местах без своего физического присутствия.
Для этого есть специализированные гаджеты, софт. Но у них есть существенный минус - они универсальны, их нельзя настроить, чтобы из этих данных под себя выстраивать систему учета, например, в Google Таблице. За софт нужно платить ежемесячную подписку.
Представляю вашему вниманию решение:
Телеграм бот который по текущей локации определяет на каком объекте вы находитесь и ставит отметку времени и пересылает их в Google Таблицу.

Таблица со скриптами для копии

https://youtu.be/olwb5SFsMVg

Новые формулы в Таблицах

Недавно в Google Таблицы добавили новые мощные формулы, про них у нас есть статья от Михаила Смирнова.

Напомним и вам и себе про эти формулы (ссылки на Таблицу с примером).

TOROW — превращает диапазон в строку
ТОCOL — превращает диапазон в столбец
CHOOSEROWS — позволяет выбрать из диапазона нужные строки
CHOOSECOLS — позволяет выбрать из диапазона нужные столбцы
WRAPROWS — разрывает столбец на строки
WRAPCOLS — разрывает столбец на столбцы
VSTACK — соединяет диапазоны в один по вертикали. Напоминаем про пример применения VSTACK для сбора данных из нескольких таблиц, ссылки на которые хранятся в ячейках
HSTACK — соединяет диапазоны в один по горизонтали
LET :)

Запрашиваем из Таблиц ИНН и получаем название компании

Привет, сотабличники! Сегодня мы для вас подготовили простой летний скрипт.

Кликаете на ячейку с ИНН, запускаете скрипт из меню и видите, что в ячейку с ИНН, в примечание, подставилось название компании из сайта rusprofile.

Весь код с комментариями в комментариях к этому посту :)

Таблица с кодом

Как достать иконки доменов?

Ребята, это старая тема, но мы про неё, вроде, не писали.

Берём ссылку

https://www.google.com/s2/favicons?sz=256&domain_url=

и добавляем в конец домен, например, avito.ru. Получившееся помещаем в функцию IMAGE().

Результат на скрине. И пример таблицы: там на одном листе справочник с доменами и иконками, а на другом по домену достаётся иконка с помощью VLOOKUP() (это быстрее, чем каждый раз использовать IMAGE()).

Наш дорогой Беня твитнул несколько лет назад этот способ. Способу уже больше 7 лет. Ну, вот и мы про него написали.

Ещё можно попробовать дописать к вашему домену /favicon.ico и уже это https://my-domain/favicon.ico напрямую вставить в IMAGE(), чтобы получить более актуальную картинку. Этот способ в том же примере в соседней колонке.

У гугла картинки не все актуальные, а второй способ не найдёт иконку, если она не в favicon.ico. Выбирайте.

Заказать работу у @google_sheets

Мы уже более пяти лет создаём на заказ Google Таблицы, разные полезные скрипты и Telegram ботов.

Несколько примеров

Есть работа? Напишите нашему боту @vas_mnogo_a_ya_bot

Конвертатор (XLSX > Google Таблица)

Привет, задачка из нашего чата. У вас есть папка на Google Диске с XLSX-файлами и вы хотите каждый файл превратить в Google Таблицу.

Как это сделать? Ну, точно можно в каждый файл зайти руками и дальше выбрать "файл > сохранить как Google Таблицу".

Если десять файлов, то мы с этим справимся, а вдруг их будет сто?

Мы сделали для вас Таблицу со скриптом, а код, как обычно, будет в первом комментарии.

1) Копируйте Таблицу, открывайте код и вводите адрес вашей папки с файлами в первую строку кода.

2) Запускайте скрипт и скрипт начинает искать первый файл без "_" в названии.

3) Находит, копирует и превращает копию в Таблицу. А после к исходному файлу в название добавляет "_".

4) И так или до окончания списка файлов или до того, как скрипт завершится по тайм-ауту (6 минут). В этом случае просто запускаем скрипт еще раз.

Таблица с кодом

---
⭐️ Заказ работы
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

А теперь превращаем Таблицы в XLSX, обходя всю заданную папку

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

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

---
⭐️ Заказ работы
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

Сочетания клавиш при работе с формулами

Shift + F1 — отображает и скрывает список аргументов функции

F1 — раскрывает и скрывает подробное описание аргументов

F9 — отображает и скрывает подсказку (вычисление формулы или выделенного фрагмента)

---
⭐️ Заказ работы
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц

Дополнение GPT Copilot для Google Таблиц

Друзья, привет! Короткий обзор дополнения от нашего старожила Каната, слово ему:
---

Недавно столкнулся с прикольным дополнением для Google таблиц, о котором расскажу в видео.

Продемонстрирую несколько кейсов применения предустановленных функций из аддона:

=GPTX() - генерирует текст по запросу;

=GPTX_LIST() - генерирует список по запросу;

=GPTX_TABLE() - генерирует таблицу;

=GPTX_EXTRACT() - извлекает значения по ключу;

@akanat, спасибо!

Оберни колонки: новая (относительно) функция WRAPCOLS

Итак, нам с вами нужно превратить одномерный массив — например, столбец, в котором данные цикличные (время начала мероприятия + N строк с выступающими в нашем примере) — в двумерный, разместив каждый повторяющийся "блок" в отдельный столбец.

Засунем диапазон в WRAPCOLS, вторым аргументом укажем, сколько ячеек отправлять в каждый столбец. Необязательный третий аргумент — как возвращать пустые ячейки из исходника, если они там будут. Иначе будет выводиться ошибка #N/A (#Н/Д).

=WRAPCOLS(A1:A;N; [чем заменить пустые])

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

=WRAPCOLS(FILTER(A1:A;A1:A<>"");N)

P.S. Раз есть функция WRAPCOLS — значит — это кому-нибудь нужно? есть и WRAPROWS.
P.P.S. В Excel (365) при русскоязычном интерфейсе — СВЕРНСТОЛБЦ и СВЕРНСТРОК.