SensaiKudisai

SensaiKudisai

на Пикабу
Telegram: ottodice
поставил 36 плюсов и 7 минусов
проголосовал за 0 редактирований
сообщества:
1122 рейтинг 285 подписчиков 98 комментариев 12 постов 7 в горячем
22

Swtich case в гугл таблицах или не самый очевидный ВПР (GS8)

UPD:

https://exceljet.net/excel-functions/excel-switch-function

https://support.google.com/docs/answer/7013690?hl=ru

Swtich case в гугл таблицах или не самый очевидный ВПР (GS8) Google, Таблица, Электронные таблицы

Пост до UPD

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

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

1, 21 и т.д. пикабушник

2,3,4, 22, 23, 24 и т.д. пикабушника

5, 6, 7, 8, 9, 10 и т.д. пикабушников

Т.е для всех чисел, которые заканчиваются на 1 - окончание [] (кроме 11)

Для всех чисел, которые заканчиваются на 2,3 и 4 - окончание [а].

Для всех чисел, которые заканчиваются на 5, 6, 7, 8, 9, 0 - окончание [ов].

В книжках по программированию для большинства языков этот кейс решается функцией switch case. Но в экселе и ГТ нет такой функции. Предлагаю заменить ее ВПРом внутри функции.

Логика следующая - мы смотрим остаток от деления на 10 и в зависимости от результата возвращаем то или иное окончание.

Swtich case в гугл таблицах или не самый очевидный ВПР (GS8) Google, Таблица, Электронные таблицы

Осталось только решить вопрос с 11. Здесь лучше всего использовать деление на 100 и, т.к. варианта тут всего 2 - 11 или все остальное - нам подойдет обычный if.

Итог:

Swtich case в гугл таблицах или не самый очевидный ВПР (GS8) Google, Таблица, Электронные таблицы

Здесь видно, что результатом, который возвращает ВПР может быть и формула.

Покажу еще один пример. Это уже из боевого. Когда используются фильтры по датам - иногда удобно использовать понятные конструкции ("Текущая неделя", "Текущий месяц") и дать при этом возможность пользователю выбрать произвольную дату.

Мы ограничимся тремя вариантами - "Текущий месяц", "Прошлый месяц" и "Произвольная дата".

Swtich case в гугл таблицах или не самый очевидный ВПР (GS8) Google, Таблица, Электронные таблицы

Если пользователь выбирает "Текущий месяц" или "Прошлый месяц", то ВПР внутри фильтра возвращает ему значение даты, которая задается формулой "КОНЕЦМЕСЯЦА(СЕГОДНЯ())". В случае, если он выбирает "Произвольная дата", то ВПР возвращает ошибку и срабатывает функция "ЕСЛИОШИБКА", которая возвращает значение даты, выбранной пользователь в ячейках F2 и F3.

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

Ссылка на таблицу:

https://docs.google.com/spreadsheets/d/1Q6xXFxfSWytAlcfY823i...

Показать полностью 2
37

D-функции в гугл таблицах (GS7)

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

Сейчас решил поделиться недавним своим открытием.

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

Это аналоги обычных функций sum, count, counta, product и т.д. с тем лишь отличием, что с данными оно себя ведет как с базой данных и сам синтаксис функций похож на упрощенные SQL-запросы.

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

Покажу на примере:

Есть таблица с данными. Назову ее целиком DATA:

D-функции в гугл таблицах (GS7) Google, Таблица, Электронные таблицы, Длиннопост

Теперь попробуем с помощью D-функции dcounta  (БСЧЁТА в русской версии) получить кол-во продаж у второго сейла:

D-функции в гугл таблицах (GS7) Google, Таблица, Электронные таблицы, Длиннопост

Первый аргумент - таблица, с которой мы будем работать. Может быть задана и как a1:c6.

Второй - столбец, с которым мы работаем.

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

Для этого попробую получить сумму всех продаж первого сейла после 10.06:

D-функции в гугл таблицах (GS7) Google, Таблица, Электронные таблицы, Длиннопост

Здесь я в явном виде записал массив с условиями. Они пишутся вертикально - сверху имя столбца с условием, снизу - само условие.

Можно записать это внутри формулы. Чтобы задать массив в формуле нужно вписать значения в фигурные скобки. Для добавления строки нужно использовать ; а для столбца , (\ в русской версии таблиц):

D-функции в гугл таблицах (GS7) Google, Таблица, Электронные таблицы, Длиннопост

И сама формула будет выглядеть:

D-функции в гугл таблицах (GS7) Google, Таблица, Электронные таблицы, Длиннопост

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

D-функции в гугл таблицах (GS7) Google, Таблица, Электронные таблицы, Длиннопост

И теперь вариант с dsum:

D-функции в гугл таблицах (GS7) Google, Таблица, Электронные таблицы, Длиннопост

Проще и писать такие функции и читать формулы с этими функциями, т.к. столбцы заданы не их диапазонами, а с помощью их имен.

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

Ссылка на таблицу:

https://docs.google.com/spreadsheets/d/1qjMNiv7vonopcRiKFJu4...

Ссылка на документацию:

https://support.google.com/docs/answer/173497

Показать полностью 5
50

Мой опыт работы аналитиком

Доброго времени суток, дорогие подписчики.

В комментах меня попросили написать о работе аналитика, решил написать об этом пост.

В бизнесе нет точного определения того, что должен делать аналитик. Их много видов, в каждой компании аналитики выполняют разные задачи и самым размытым при этом остается бизнес аналитик.

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

Мой случай ближе всего к эникейщику.

Для начала я попробую собрать всё, чем в теории может заниматься аналитик в компании.

1 - написание бизнес процессов. Есть определенные стандарты визуализации бизнес-процессов, т.н. нотации. Это вот эти вот штуки:

Мой опыт работы аналитиком Аналитика, Профессия, Длиннопост

Этих нотаций, как и языков программирования, великое множество. Вот одной из задач аналитиков является как раз формализация бизнес процессов в такие диаграммы, формализация их, проверка всех "узлов" на наличие ответственных, отчётности и узких горлышек. Отдельное веселье - для каждого юнита (СЕО, разработчик, сейл, офис-менеджер) один и тот же БП перерисовывается в зависимости от его уровня погружения. Как правило для высших чинов в компании их рисуют совсем верхнеуровнево, для технарей - максимально детально, для остальных - максимально просто.

2 - раз уж заговорили про нотации - описание продуктов и их структуры. Здесь принцип похожий, даже нотации иногда используют одинаковые и для БП и для описания продукта. Тут тоже самое - декомпозировать продукт и его элементы таким образом, чтобы можно было передать его "куски" разрабам. Часто эту же функцию выполняют архитекторы. Тут зависит от сложности самого продукта и что именно нужно формализовать.

3 - Эксель / ГТ во всех их проявлениях. В большом кол-ве компаний под аналитиком понимается именно Excel-man, а чаще - woman. И чаще всего (и я сочувствую коллегам, которые работают в экселе) - это сбор данных и создание отчётов. В ГТ чуть проще. Я, например, большую часть времени занимаюсь как раз автоматизацией отчётности. Проще говоря - у нас есть 2 СРМ и туча чатов в тлг. Вот моя задача сделать так, чтобы остальным сотрудникам компании нужно было делать меньше ручных действий и чтобы каждое действие сотрудника фиксировалось и проходило все этапы до дашборда СЕО в автоматическом формате.

4 - обслуживание технических систем - расчёт ЗП, контроль за сейлами чтобы они корректно вели базу (иногда под эту задачу нанимают отдельного человека с должностью "аналитик продаж"), ручная состыковка того, что не удалось или пока не дошли руки автоматизировать. Ну и поддержка пользователей этих систем.

5 - маркетинговая аналитика. Здесь я много писать не буду, т.к. свечку не держал и в теме почти не разбираюсь.

6 - анализ рынка. Собственно - сбор информации о конкурентах и ее последующий анализ. Тут начинается веселье с инструментами. Как правило именно здесь и пригождаются парсеры, питоны, R-ы и прочая радость. Информация разношёрстная, обновляется часто, руками каждый раз собирать ее за***шься. Собственно чем лучше аналитик умеет обращаться со всей этой радостью - тем больше полезной инфы он может накопать для компании.

7 - аналитик-советчик. Требует хорошего понимания продукта/сферы/бизнеса. Нужно не только уметь собирать цифры и делать из них дешборды, но и иметь уровень погружения +- такой же как у СЕО. Обучить этому куда сложнее, чем научить человека кодить на R (по моему опыту), а литературы нужно не меньше. Если у вас такой человек в компании есть - считайте вам повезло. Это гибрид владельца бизнеса и СЕО, но не несущий ответственности. Как правило такой человек будет отгружать вам ценные идеи и сам знает куда и что ему копать. Часто - автономный юнит.

8 - Мектрик-мен. Что-то среднее между аналитиком и супервайзером. Его задача следить за КПИ остальных и вовремя "зажигать красную лампу", если у кого-то проседает КПИ, либо проседает какая-то глобальная метрика, типа NPS (показатель лояльности клиента) или RTR (коэффициент возвратов). Во многих компаниях эта функция у руководителей отделов или у HR-ов.

9 - Человек-интерфейс. Самая грустная ситуация для аналитика - когда его используют только как сборщика и формализтора данных. Есть люди, которые находят дзен и в этом, но профессиональный рост таких людей очень долгий. А если это политика компании - то рост вообще останавливается. По сути это новая версия старых младших бухгалтеров, которые должны ТОЛЬКО считать какие-то цифры и ТОЛЬКО выдавать их по запросу. По сути аналитик не вовлечен в бизнес и его руководитель или СЕО делает львиную часть работы аналитика. Происходит это либо из-за недоверия СЕО/руководителя, либо его проблем с делегированием, либо неспособностью вырастить/найти себе толкового аналитика.

10 - Дешбордист.

Мой опыт работы аналитиком Аналитика, Профессия, Длиннопост

Если при виде скринов из BI, Data Studio или Tableu у тебя потеют ладошки - ты на 80% латентный аналатик. По сути помесь аналитика с дизайнером (или когда хотел быть художником, но тебя отправили учиться на физмат). Дешборды - это отдельный вид искусства. При этом их создание требует и неслабых технических навыков. По сути эксель, sql и прочие радости должны быть уже как родные, раз аналитик переходит на этот уровень.

11 - великий и ужасный data scienсe.Здесь тоже мало что могу сказать, это мне пока не по грейду.


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


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

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

Показать полностью 1
62

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6)

Привет, дорогие подписчики!

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

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


Итак, нам нужно глобально сделать 3 вещи:

1 - забрать с какого-нибудь сайта прогноз погоды

2 - подключить бота и привязать его к таблице

3 - настроить триггер для отправки.


Посмотрев несколько сайтов с погодой - выбор пал на Яндекс, ввиду удобной структуры сайта.

Отсюда я забрал прогноз на дождик в ближайшее время:

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

Подробнее о том как спарсить данные с сайта в таблицу в этом посте: Парсинг данных с сайтов в гугл таблички  (GS1)

Тут приведу только формулу:

=importxml("https://yandex.ru/pogoda/moscow?via=hnav";"//p[@class='maps-widget-fact__title']")

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

И отсюда я забрал сам прогноз погоды:

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

Т.к. данные на сайте представлены в виде таблички, то удобнее использовать =importhtml()

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

Теперь подключаем бота. Как это сделать - читай тут: Подключение телеграм бота к гугл табличкам (GS3)


Важно! В прошлых постах мы всегда сами активировали бота из ТЛГ и скрипт всегда знал chat_id куда нужно отправлять сообщение. В этот раз айди нужно будет сохранить в таблицу в качестве переменной.

Логика простая - пишем простую команду, которую будет запускать бот (назовем ее /link). По этой команде бот будет в док записывать значение нужного нам айдишника.

Запишу его просто в ячейку f1.

Кусок кода:

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост
Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост
Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

Добавляем это значение в код.

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

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

Как это выглядит в чате:

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

Теперь автоматизируем это, чтобы оно улетало в чат каждое утро.

Для этого заходим в триггеры проекта:

Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост
Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост
Чат с прогнозом погоды и триггеры в гугл таблицах (GS6) Google, Погода, Длиннопост

После сохранения он будет каждый день в 9 утра отправлять сообщение в чат.

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


Вот и всё, уважаемые чатлане. Как всегда - жду ваших комментариев)

Показать полностью 10
130

Учёт финансов с помощью гугл таблиц и телеграмм бота (GS5)

Теплого времени суток, дорогие подписчики. Как обещал ранее - пилю пост про учёт финансов в гугл табличках (далее - ГТ).

Сам пользуюсь такой системой уже больше двух лет и по очень похожей системе веду тайм-трекинг.

Изначальная идея тайм-трекинга через тлг + гт принадлежит не мне. Я получил в своё время уже готовый продукт и его дорабатывал (существенно дорабатывал).

Ближе к делу.

Первое что нужно будет сделать - подключить бота к доке. Как это сделать - читай пост Подключение телеграм бота к гугл табличкам (GS3)


Второе - дать боту возможность читать все сообщения, а не только адресованные ему команды.

Учёт финансов с помощью гугл таблиц и телеграмм бота (GS5) Google, Финансы, Длиннопост

Вот код для бота. Он достаточно простой. Всё что он делает - на листе Data Stream создает строку с датой отправки, юзернеймом и текстом.

function doPost(e)

{

var update = JSON.parse(e.postData.contents);

var DOC = SpreadsheetApp.openById("1jVRB2QOasOXaXhm3Q99ZUlgnLpS_td3O3olQ2HGRguk");

var DS = DOC.getSheetByName("Data Stream");

//нам нужен только тип "сообщение"

if (update.hasOwnProperty('message'))

{

var msg = update.message;

var chat_id = msg.chat.id;

var text = msg.text;

var msg_array = msg.text.split(" ");

var date = (msg.date/86400)+25569.125; //.125 справедлива для МСК. Если разница по времени другая - будет другое значение. Чтобы узнать - напиши разницу в часах в гугл таблице и измени формат на Число. Он напишет часы в численном эквиваленте.

var user = msg.from.username;

DS.appendRow([date, user, text]);

}

}


Не забываем после этого заново опубликовать приложение!

Учёт финансов с помощью гугл таблиц и телеграмм бота (GS5) Google, Финансы, Длиннопост
Учёт финансов с помощью гугл таблиц и телеграмм бота (GS5) Google, Финансы, Длиннопост

Важно приучить себя к определенному виду записи - сначала откуда потратил, потом - на что потратил, потом сумму и потом комментарий. Например

ТКС ЕДА 850 купил гречки

СБЕР Сереже  5000 заплатил штраф


Создаем новую страницу и в a2 пишем следующую формулу:

=ARRAYFORMULA(ЕСЛИ('Data Stream'!C2:C>0;split('Data Stream'!C2:C;" ");))

Аррей формула будет пробегать по всему диапазону c2:с соседнего листа и, если ячейка в диапазоне не пустая, то будет разбивать строку в ней по пробелам. Результат видите ниже.

Учёт финансов с помощью гугл таблиц и телеграмм бота (GS5) Google, Финансы, Длиннопост

Выделяем всё и создаем сводную таблицу

Учёт финансов с помощью гугл таблиц и телеграмм бота (GS5) Google, Финансы, Длиннопост

Настраиваем ёё как на экране и получаем профит.

Учёт финансов с помощью гугл таблиц и телеграмм бота (GS5) Google, Финансы, Длиннопост

На этом все) Старался описать самый минимальный функционал чтобы любой чатланин мог как можно быстрее запустить сие чудо. Ссылка на табличку: https://docs.google.com/spreadsheets/d/1jVRB2QOasOXaXhm3Q99Z...


Если в комментариях будут пожелания - можно прикрутить более сложную отчётность и нотифаи. Напр, если потратил на маски больше 5000 - чтобы приходило уведомление в тлг.

Показать полностью 5
149

Запись на ноготочки через телеграм бота и гугл таблицу (GS4)

Под прошлым постом (Подключение телеграм бота к гугл табличкам (GS3)) прилетел запрос от уважаемого чатланина @Kamikadze.man,

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

Что ж, сказано сделано. Сделал чуть больше интервал - 30 минут, для читабельности поста.


Важно! По ходу разработки бота принял решение "срезать угол" в одном важном месте. Оно довольно критично, но, признаюсь честно, желание выпустить пост сегодня пересилило желание сделать всё на 100%. Дело в том, что даты, которые гугл скрипт забирает из таблички имеют оч неудобный формат. И вот как раз на этапе приведения этого формата я и срезал угол. Сказываться это будет на том, что пользователь вместо даты 26.05 должен вводить только число 26. И сам код написан в формате МВП, т.е. он минимально работоспособен и не учитывает поведение юзера. Т.е. рассчитан только на то, что пользователь будет действовать четко по инструкции. Если эти два обстоятельства подтолкнут читателя к мысли, что автор -- пацак и заслуживает минуса - значит это будет мне уроком на будущее.


Первым делаем создаем новую гугл таблицу (ГТ). Вот ссылка на неё:

https://docs.google.com/spreadsheets/d/1SOt9ng7UZ3oK6fKXYRPa...

Создаем и подключаем к ней бота. Как это сделать - первая ссылка в посте.


Считаем время приёма полтора часа. Если мастер хочет уйти домой в 19, то в 17:30 можно назначить последний прием.

Что должен делать бот?

1 - Спрашивать у клиента в какой день он хотел бы прийти к мастеру, проверять - если свободные окна в это время.

2 - Получить у клиента время начала приёма и записать его в таблицу.

3 - Сделать это время занятым и не предлагать его следующему клиенту.

Начнём с первой функции.

Самая сложная часть - бот должен вести переписку с клиентом. Т.е. бот задает вопрос, а клиент на него отвечает. Чтобы каждый раз не пользоваться командами (отправкой через /) нужно переключить бота в этот режим. Делается это у botfather:

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

Наша первая цепочка (Ю: - юзер, Б: - бот)

Ю: Записаться

Б: На какое время хотите записаться?

Ю: Дата

Б: Список свободных окон

Ю: Время

Б: Запись прошла успешно


Чтобы реализовать этот диалог нам нужно хранить состояние каждого чата в таблице. В шапку таблицу добавляем техническое поле Statement, а пользователей будем распознавать по Chat ID.


После того как пользователь отправляет команду "Записаться" -  мы отправляем ему текстовое сообщение и меняем Стейтмент на "Dates", т.е. следующее сообщение от пользователя будет с датой.

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

+ Добавляем в бот команду "Отмена", которая отменяет текущую запись и сбрасывает стейтмент.


Итак к табличке.

Размечаем лист с клиентами:

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

И лист под календарь

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

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

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

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


Теперь как это работает.

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

И результат в доке:

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

Второй прогон:

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

Занятые окна он уже не показывает) И итог:

Запись на ноготочки через телеграм бота и гугл таблицу (GS4) Google, Ногти, Автоматизация, Длиннопост

Ссылка на доку и код (Инструменты / Редактор скриптов). Код получился объёмным и не вижу смысла добавлять его в сам пост.

Отдельно код просто в текстовом файле: https://docs.google.com/document/d/1FOg5YRIauH0C13u-imdTpezU...

https://docs.google.com/spreadsheets/d/1SOt9ng7UZ3oK6fKXYRPa...



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

Показать полностью 8
148

Подключение телеграм бота к гугл табличкам (GS3)

Всем ку
Комбинация из ТЛГ и ГТ, простота подключения КМК делает из ГТ офигенный инструмент автоматизации.
Тайм-менеджмент, таск-менеджмент, учёт финансов, объявления, пересылка событий - всё это создается и довольно быстро настраивается. Главное - это становится полезным не только в бизнесе, но и для обычных чатлан.
Пост ориентирован прежде всего на людей, которые только познают ГТ и на первых порах не требует знания гугл скриптов. В этом посте будет только первичная настройка и подключение. В следующих постах рассмотрю всё что описал выше (как трекать время, задачи, финансы и т.д. и как это настроить под себя).

Итак, первое что нам нужно - создать себе ТЛГ бота. Обращаемся к @BotFather

Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост

Нам потребуется АПИ токен, он в середине этого сообщения: 1240599492:AAEOpj-G4C0hO3DFGuQ7RkMyX65RxsGFCqo

Дальше нам нужна пустая ГТ. Создаем, заходим в Инструменты / Редактор скриптов.
Скрин для английской версии.

Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост

Вставляем туда следующий код:
function doPost(e)
{
var update = JSON.parse(e.postData.contents);
var DOC = SpreadsheetApp.openById("1WvGYnynJkX2srI1G1DD-7b8Qrm2WiWR6QOxzDJRKxXM");
//нам нужен только тип "сообщение"
if (update.hasOwnProperty('message'))
{
var msg = update.message;
var chat_id = msg.chat.id;
var text = msg.text;
var msg_array = msg.text.split(" ");
var date = (msg.date/86400)+25569.125;
var user = msg.from.username;
if (msg_array[0] == "/hello")
{
send("Hello World", chat_id)
}
}
}
function send (msg, chat_id)
{
//Отправляет сообщения в тлг. На вход функции дать сообщение и ID чата, в который нужно провести отправку
var payload = {
'method': 'sendMessage',
'chat_id': String(chat_id),
'text': msg,
'parse_mode': 'HTML'
}
var data = {
"method": "post",
"payload": payload
}
var API_TOKEN = '1240599492:AAEOpj-G4C0hO3DFGuQ7RkMyX65RxsGFCqo'
UrlFetchApp.fetch('https://api.telegram.org/bot' + API_TOKEN + '/', data);
}


Выделенное жирным:
1 - ID гугл таблицы, находится после /d/ :

Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост

2 - АПИ токен гугл бота, который мы доставали выше.

По коду - в нем две функции - первая принимает сообщение от бота и работает с единственной командой "/hello". Вторая - отправляет сообщения в чат. В данном случае - в чат из которого пришло сообщение.

Осталась последняя часть - зарегистрировать вебхук. Тут нам поможет эта ссылка:
https://api.telegram.org/bot{API_TOKEN}/setWebHook?url={CURRENT_WEB_APP_URL}
Вместо первых фигурных скобок вставляем всё тот же токен бота, а вместо вторых фигурных скобок нам нужно вставить ссылку на приложение, которые  мы только что сделали, но еще не опубликовали.

Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост
Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост

Важно - поставить доступ для анонимов и для каждой новой версии выставлять Project version новый или new.
При деплое оно запросит разрешение - даём, после чего получаем заветную ссылку:

Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост

В итоге у нас должно получиться следующее:
https://api.telegram.org/bot1240599492:AAEOpj-G4C0hO3DFGuQ7R...
Эту ссылку мы просто вставляем в браузер и переходим по ней. Если не получается - подрубаем ВПН. Получаем следующее:

Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост

Вебхук зарегистрирован, всё ок. Пойдем тестить бота.

Подключение телеграм бота к гугл табличкам (GS3) Телега, Google, Бот, Длиннопост

Подключение бота к табличке успешно произведено.

ЗЫ Бота я удалил, так что этот АПИ-токен уже не действует.

Показать полностью 7
79

Фильтры и ВПРы в ГТ (GS2)

Шалом, комрады.

Рад, что первый пост оказался полезным. Ссылку на него оставлю тут. Парсинг данных с сайтов в гугл таблички  (GS1)


Хочу рассказать о функции, которой я пользуюсь каждый свой рабочий день. Это функция =filter(). Фильтр фильтрует данные по заданным условиям.

Пишется как =filter(что нужно отфильтровать; условие1;условие2;...)

Пример:

У нас есть список продаж по манагерам, нам нужно отфильтровать по конкретному манагеру.

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

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

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

В русской версии - Данные / Проверка данных

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост
Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост
Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

Бывает так, что за продажу отвечают сразу несколько менеджеров. Как тогда достать данные?

Тут нам пригодится функция REGEXMATCH, которая проверяет, содержится ли регулярное выражение (кусок текста) в тексте или в массиве строк. Для наглядности проверю содержание Максимов во всех строчках.

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

И фильтр тогда будет выглядеть:

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

И еще одна замечательная часть фильтра - это его комбинация с функцией ВПР (VLOOKUP), а если быть точным - с функцией ЕНД()  - она проверяет наличие специфической ошибки при попытке проВПРить значение. Проще говоря - она даёт однозначно понять - есть ли искомое значение в диапазоне, в  котором мы ищем. Понимаю что сложно, давайте покажу на примере.

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

Здесь при помощи ВПР мы вытаскиваем день рождения сейла. Чтобы проверить, есть ли конкретный сейл в списке - используем функцию ЕНД() / ISNA() в англ. версии

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

Функция проверяет наличие ошибки. Т.е. если она находит сейла в списке - возвращает FALSE.

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

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

Для чего еще может быть полезна такая комбинация?

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

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

Фильтры и ВПРы в ГТ (GS2) Google, Excel, Длиннопост

Вот и все, уважаемые чатлане)

Если есть конкретные пожелания к следующим темам - пишите в комментах =3


UPD: ссылка на табличку: https://docs.google.com/spreadsheets/d/1WvGYnynJkX2srI1G1DD-...

Показать полностью 10
Отличная работа, все прочитано!