ОТПРАВЛЯТОР: отправляем ваши отчеты в теле письма по расписанию

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

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

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

Как всё работает:
Заполняете лист "настройки", одна строка в нём - одно правило отправки диапазона. Вам нужно ввести таблицу, откуда берём данные, лист и диапазон, кому отправляем письмо, как часто его отправляем и тему письма

Дальше вы можете либо руками запустить скрипт (из "/ меню скриптов /"), либо поставить скрипт test на ежедневное выполнение, скажем, от 12 до 13.

Как заставить скрипт запускаться каждый день:
инструменты > редактор скриптов > кликаем на часы > "добавление триггера" > выбираем функцию test > триггер по времени > по дням > выбираем нужный час

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

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

Если всё получилось - в столбце "дата и время последней отправки" появляется дата и время когда письмо было отправлено.

==
Идейный вдохновитель и спонсор этого решения - Сергей М из нашего чата, спасибо ему 😎👏

ДВИГАЕМ ВРЕМЯ ФОРМУЛАМИ

– как получить дату следующего понедельника
– как перейти на два месяца назад
– как прибавить три часа
– как отбросить время и оставить только дату
– как округлить время до часа

И подобные кейсы в прекрасном справочнике от участника нашего чата Михаила Смирнова, спасибо ему 👏.

На скриншоте – не всё, смотрите Таблицу.

==
Еще полезное про даты:
> Выводим ряд чисел или дат. Одной формулой.
> Сегодня хороший день - 43 873.

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

SmartFill в Google Таблицах - мгновенное заполнение данных с помощью автоматически формируемой формулы.

В Таблицах появилось несколько интересных вещей. Расскажем о каждой, сегодня начнем с аналога "Мгновенного заполнения" (Flash Fill) Excel (вызывается автоматически либо по сочетанию Ctrl+E либо из ленты Главная - Заполнить - Мгновенное заполнение).

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

Теперь в Google Таблицах есть похожая функция. Называется она SmartFill. Разница только в том, что она не заполняет ячейки значениями, а предлагает формулу, которая будет тянуть данные по вашему шаблону.
Для ее активации не нужно что-то нажимать. Вводите в пустом столбце данные из соседнего столбца в том виде, в котором вам нужно их извлечь (например, в столбце A у вас названия товаров, а в столбце B вы начинаете вводить только первое слово - название бренда). И Таблицы сами через несколько введенных значений распознают паттерн и предложат заполнить весь столбец формулой, которая будет по этому паттерну данные извлекать.

В примере с ФИО подсказка появилась в процессе ввода четвертого имени из столбца с ФИО (в Excel обычно со 2-го значения)
Далее можно либо сразу согласиться и заполнить ячейки (галочка), либо посмотреть формулу (там будут функции на английском), либо отказаться и продолжить заполнять вручную (крестик).

Формула может быть довольно монструозная. Но тем не менее будет решать задачу во многих случаях. Вот такой конструкцией предлагается доставать имена (второе слово из ячейки):
=ПСТР(A2;НАЙТИ(" ";A2) + 1;НАЙТИ(СИМВОЛ(160);ПОДСТАВИТЬ(A2;" ";СИМВОЛ(160);2)) - 1 - (НАЙТИ(" ";A2)))

Думаем, многие наши читатели знают, как сделать проще 🙂 Кстати, можно отправить отзыв (обратную связь) на формулу. Мы это проделали с данной формулой 🙂 Посмотрим, будут ли изменения.

С задачей по извлечению имени и фамилии (без отчества) опция тоже справилась (и формула даже короче - смотрите файл-пример).

Так или иначе, всегда можно быстро сохранить формулы как значения (выделяем диапазон -> Ctrl+C -> Ctrl+Shift+V).

Мы также попробовали опцию на других данных — индексах, числах и т.д. Пока кажется, что она не столь сообразительна, как собрат из Excel, и не хочет работать на малом количестве данных, и находит паттерны тоже только на 3-5 раз. Но будем надеяться, что она будет улучшаться со временем. Как Таблицы в целом 🙂

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

Парсим YOUTUBE скриптами

Друзья, по ссылке - короткая и наглядная статья от Ромы Игнатова.

Внутри две темы:
– пишем скрипт, который парсит страницу ролика на ютубе и достаёт просмотры / лайки / дизлайки
– кладём скрипт в библиотеку (и как её вообще создать)

Будут вопросы – приходите в наш чат: @google_spreadsheets_chat и задавайте, там есть и Роман :)

Статья
Документация, про библиотеки

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Статистика по столбцам - еще один новый инструмент Google Таблиц

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

Инструмент живет по адресу: Данные -> Статистика по столбцам
Горячие клавиши для инструмента: (Alt+D) + L

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

В панели 3 блока:
- График. Можно выбрать количество (будет гистограмма с количеством значений) или распределение (гистограмма с автоматически выбранными интервалами и количеством значений, входящих в эти интервалы)
- Периодичность - как часто в столбце встречаются те или иные значения. При наведении курсора на значение все ячейки с ним будут выделяться более ярким оттенком.
- Описание. Это статистика. Для текстовых столбцов будет только кол-во строк, уникальных значений и пустых ячеек. Для столбцов с числовыми данными - еще и сумма, среднее, медиана, минимум и максимум. Напомним, что сумму, среднее, мин и макс также можно увидеть внизу справа на строке состояния (но не по столбцу целиком, а по выделенному в данный момент диапазону).

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

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

отправлятор таблиц в телеграм чаты
бесплатно и по расписанию

друзья, мы создали решение для вас, оно:

берёт диапазон Таблицы →
превращает в картинку →
отправляет в чаты (еще и по расписанию)

вам больше не надо платить за zapier, всё бесплатно и крутится на мощностях вашего аккаунта

как это можно использовать?
– регулярно отправлять картинку с зарплатой своим сотрудникам, каждому - свою
– отправлять боссам текущий план / факт, обновляемый каждый день
– показывать сотрудникам выполнение метрик и сколько им нужно поднажать до премии

как перенести и настроить?
1) копируем таблицу
2) заполняем лист "//": откуда берём диапазон, на какие chat_id отправляем, в какие дни недели и по каким часам
3) в редакторе скриптов вставляем токен своего бота
4) ставим скрипт на запуск каждые полчаса – пусть запускается и проверяет, есть ли строки, которые нужно обработать

как зарегистрировать бота в Telegram и узнать chat_id для отправки?
– бот регистрируется через пользователя @botfather, он же вам отправит токен, чтобы вставить его в скрипт
– chat_id группы / пользователя можно узнать с помощью @myidbot

таблица с решением: здесь
инструкция побольше (как создать триггер и бота, узнать chat_id): вот тут

p. s. а прекрасный сниппет конвертации в PNG мы взяли отсюда

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

===

📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📘 КАНАЛ: @google_sheets / оглавление
📗 ЧАТ: @google_spreadsheets_chat

Инструмент "Анализ данных" в Google Таблицах

Если "Статистика по столбцам", о которой мы писали недавно, только появилась в Таблицах, то "Анализ данных" существует давно. Это тоже инструмент для быстрого анализа данных с несколькими опциями:
- добавить визуальное чередование строк (оно также доступно в меню "Формат" - "Чередование строк")
- расчет суммы всех значений, среднего, минимума и максимума и количества значений в диапазоне.
- несколько графиков (обычный линейный график, гистограмма) с динамикой (распределение строить не умеет, в отличие от статистики по столбцам). Если в данных несколько столбцов, построить и комбинированную диаграмму (гистограмма по одному показателю, график по другому)

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

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

Инструмент живет по адресу:
- нижний правый угол, иконка с плюсом (при наведении курсора появляется надпись "Анализ данных"
- Alt + Shift + X

===

📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📘 КАНАЛ: @google_sheets / оглавление
📗 ЧАТ: @google_spreadsheets_chat

Спарклайн с расчетом данных прямо в формуле

Дано: данные по продажам товарных позиций по месяцам. Много строк с товарами и столбцы-месяцы. Итоговых сумм у нас нет. Но при этом мы хотим посмотреть динамику визуально - по всем товарам по месяцам.

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

Сумма по каждому столбцу (месяцу) - СУММ(B2:B), СУММ(C2:C) и так далее.
После объединяем их в один массив:
{СУММ(B2:B) \ СУММ(C2:C) \ СУММ(D2:D) \ СУММ(E2:E) \ СУММ(F2:F) \ СУММ(G2:G)}

И остается этот виртуальный диапазон данных (массив) использовать как аргумент в функции, формирующей спарклайн. Вторым аргументом будет тип спарклайна (charttype) - столбчатый (column). Тип спарклайна тоже задаем в массиве, экономим место на рабочем листе 😉

=SPARKLINE({СУММ(B2:B) \ СУММ(C2:C) \ СУММ(D2:D) \ СУММ(E2:E) \ СУММ(F2:F) \ СУММ(G2:G)} ; {"charttype" \ "column"})

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

Решить задачу можно разными способами, например, так:
- с помощью СЧЁТЗ определить, сколько у нас заполнено столбцов
- с помощью SEQUENCE затем сформировать номера этих столбцов, от первого столбца с данными (в примере это второй столбец на листе)
- подставить это все в АДРЕС, чтобы получить адреса ячеек вида B1, C1 и т.д.
- из АДРЕСа достать номера заголовков с помощью регулярного выражения (достаем только латинские прописные буквы).
- все это собрать в запрос для функции QUERY вида sum(B), sum(C), sum(D) и т.д.
- с помощью ИНДЕКСа взять только вторую строку из выдачи QUERY (так как спарклайн умеет отображать только числа, то заголовки из выдачи QUERY будут ему мешать).
- все это засунуть в функцию SPARKLINE.

Ух! Если у вас будут идеи альтернативных решений этой задачки - пишите, мы с радостью ими поделимся 😉 В файле с примером есть пошаговый разбор.

===

📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📘 КАНАЛ: @google_sheets / оглавление
📗 ЧАТ: @google_spreadsheets_chat

GOOGLE DATA STUDIO, пример отчёта и рассказ о его создании

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

Статья, два примера дашборда (для десктопа и для телефонов/планшетов), а еще ссылки на полезные материалы: https://telegra.ph/Google-Data-Studio-na-primere-nebolshoj-zadachi-11-22

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

превращатор листа Таблицы в xlsx

привет! наш сегодняшний скрипт превращает активный лист таблицы в xlsx файл

что можно поменять в коде:
1 строка – в какую папку сохранять файл (или в корень google диска, если аргумент не заполнен)
10 строка – как созданный файл будет называться
14 строка – всплывающее окно с ссылкой на созданный файл, если надоест - можете закомментировать

самая главная часть скрипта – функция getBlob, если приглядитесь к ней, то сможете понять, что поменять, чтобы сохранять не только в XLSX, а еще в других форматах (примеры на gist)

весь код отдельно в pastebin
таблица со скриптом

===
📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

крутейшая таблица с подсказками про query

слово автору:

Привет, я Паша Мрыкин, эксперт по сквозной аналитике в Calltouch и не смотря на мою любовь к коробочным решениям - я люблю работать с данными в Google Таблицах.

Что вы сможете сделать с помощью этого файла:
1. Найти примеры применения QUERY в своей работе.
2. Подсмотреть примеры формул.
3. По знаку "?" увидеть объяснение для каждого из шагов. Рекомендую заглянуть в каждую подсказку, т.к. там могут быть объяснения неочевидных моментов.

> ТАБЛИЦА ПРО QUERY

==
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

СКРИПТАМИ ОПРЕДЕЛЯЕМ, КОГДА ОСВОБОДИТСЯ ДОМЕН (развернуто)

как определить, когда освободится домен? – идём на https://who.is/whois/, вставляем в окно адрес нашего сайта и нажимаем на лупу

вы переместитесь на страницу с адресом https://who.is/whois/+тот сайт, который вы ввели и на странице появится информация, из которой нас интересует строка с paid-till (дата, по которую оплачено доменное имя)

всё понятно и самое главное – это легко можно провернуть скриптами таблиц и сразу для сотен доменных имён

мы создали таблицу с решением для проверки, она работает так:

в таблице в столбце A - доменные имена, которые нужно проверить

в столбце B – флажки, флажок стоит - проверяем, не стоит - догадайтесь :)

первая строка - сайты, на которых доменные имена будут проверяться (помимо who.is есть еще несколько)

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

и друзья, сегодняшнего поста не было бы без Дениса – спасибо ему

ТАБЛИЦА С РЕШЕНИЕМ

СКРИПТАМИ ОПРЕДЕЛЯЕМ, КОГДА ОСВОБОДИТСЯ ДОМЕН (кратко)

1) формируем ссылку: адрес сайта для проверки + наш домен
2) переходим на неё и с помощью регулярки достаём дату, по которую домен оплачен
3) получилось – переходим к следующей строке, не получилось – пробуем другой домен для проверки

ТАБЛИЦА СО СКРИПТОМ

p.s. магия в том, что ссылок может быть несколько тысяч и они обработаются за несколько запусков скрипта

вот такие возможности у бесплатных таблиц + скриптов от google :)

(этот же пост подробнее)

==
🔪 НАШ КУРС НА SKILLBOX (Таблицы и скрипты, Excel и VBA)
📘 Канал: @google_sheets
📕 Оглавление канала
📗 Чат: @google_spreadsheets_chat