June 23, 2021

Напишем-ка бота!

Телеграм бот, который бегает за данными в Таблицу @t.me/google_sheets

Друзья, привет!

Сейчас на моём экране прекрасный датасет – "Отзывы, поступившие через виджет обратной связи АИС "Мониторинг Госcайтов", я скачал его как CSV из https://data.gov.ru/ и открыл в Таблице.

Cсылки не работаёт, приложение мёртвое, зато у меня айфон :)

В Таблице – 16 000 строк и 16 же столбцов: отзыв, информация о нём и информация про ответ.

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

Мы будем использовать Таблицу с отзывами - запросим конкретные строки или выбранные даты, ну а вы сможете применить это к вашему делу: узнавать остатки на складе, продажи и курс любимой криптовалюты.

В чём вообще проблема с ботами? На самом деле – это сложно, причём не только писать с 0, а даже пытаться адаптировать решение, которое полностью готово и к которому есть инструкция. Самый частый вопрос в нашем чате – всё сделал по инструкции, но у меня не заработало (на самом деле не всё :)

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

Поехали

Телеграм нам нужен для того, чтобы создать бота (https://t.me/botfather) и получить его токен (там же).

Немного теории: как работает бот на вебхуках

1) Мы пишем серверную часть в Таблице / GAS скриптах и разворачиваем приложение, получаем его ссылку;

2) Далее к этой ссылке мы привязываем токен нашего бота (создаём вебхук);

3) Далее, когда бот кому-то пишет или пишут боту- объект с информацией сообщения отправляется на адрес развернутого приложения;

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

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

Ну, к коду – пусть пользователь отправляет в бота строку с датой, бот принимает это сообщение, берёт данные, фильтрует по дате, соединяет и возвращает пользователю в нескольких сообщениях результат из Таблицы.

Первая функция – загрузка данных из Таблицы.

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

function getBigData() {

return SpreadsheetApp.openByUrl('https://docs.google.com/s')

.getSheetByName('обратная связь')

.getDataRange().getValues();

}

Вторая функция – фильтрация

Окей, представим, что нам нужно отобрать данные c 1 февраля по 2 февраля, фидбек старый - поэтому ставим 2015 год.

И у нас сразу сложный случай – работа с датами в JavaScript. Есть много способов, я сделаю так - приведу даты к МС и уже числа сравниваем внутри фильтра.

y[6] - это седьмая колонка данных, в этой колонке как раз дата отзыва, она седьмая, а на ней написано 6 потому, что элементы в массивах в JS начинаются с 0

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

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

Мы с помощью =split делим строку на две части и аккуратно вставляем в конструктор даты.

Смотрите на журнал внизу - вставлено правильно, а вот если бы я удалил T у времени - была бы ошибка и январь :)

Целиком вторая функция, фильтрация данных по датам

function mixerBigData(msg) {

msg = msg.split(',');

date1 = new Date(('2015-' + msg[0] + 'T00:00:00'));

date2 = new Date(('2015-' + msg[1] + 'T00:00:00'));

Logger.log([date1, date2])

return data = getBigData().filter(y => (new Date(y[6]).getTime() >= date1.getTime())

&& (new Date(y[6]).getTime() <= date2.getTime()))

}

Третья функция - оставляем только нужные столбцы

Нам не нужны все отобранные данные - оставим только дату, обратную связь и ответ на обратную связь, это столбцы 7, 10 и 11.

И объединяем отзывы через перенос строки, а столбцы через тире.

function mergeBigData() {

return mixerBigData("02-01,02-05")

.map(t => t[6] + '––––' + t[9] + '––––' + t[10])

.join('\n\n')

}

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

Четвертая функция - doPost()

Функция называется doPost(), она будет развёрнута как приложение и в её аргумент e будет попадать объект от нашего бота.

Итак, по блокам, - парсим пришедший объект и достаём то, что нам нужно - само сообщение, chatId отправителя и username отправителя в телеграме

function doPost(e) {

//парсим объет, который пришёо

e = JSON.parse(e.postData.contents)

const msg = e['message']['text'];

const fromId = e['message']['from']['id']

const fromUsername = e['message']['from']['username']

Этот блок логирует полученный объект целиком в нашу таблицу, на лист "логи", добавляя таймстемп. Удобная штука, можно посмотреть, кто писал в бота и попытаться разобраться с ошибками.

//логируем в таблицу

const ss = SpreadsheetApp.openByUrl(urlSs);

ss.getSheetByName('логи').appendRow([new Date(), JSON.stringify(e)])

Дальше я придумал что-то вроде проверки, чтобы наша команда запускалась только у авторизованных пользователей (их, то есть свой три раза, юзернем я добавил в массив trueList).

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

Последнее - мы делим сообщение на отдельные сообщения по 4096 символов - максимальная длина сообщения для телеграм API.

//список юзернеймов телеграма, которым вы разрешили использовать функционал бота

const trueList = ['namokonov', 'namokonov', 'namokonov'];

//проверяем, правильный ли юзернейм написал

if (trueList.includes(fromUsername)) {

// проверяем присланное сообщение, пусть метрикой будет наличие запятой

if (/\,/.test(msg)) {

//кажется всё ок - отправляем пользоователя ответ

//собираем наше сообщение и отправляем по 4096 символов –

//(максимум для отправкм в телеграме

var messages = mergeBigData(mixerBigData(msg))

sendMessage(fromId, message = messages.slice(0, 4095))

}

};

И пятая функция - функции - это сниппеты для отправки сообщения в телеграм и для создания / удаления вебхука

Я их поместил на лист telegram Bot Api, на нём вам нужно будет заполнить токен бота и ссылку на веб-приложение.

Гифка-инструкция по установке с нуля

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

Когда будете говорить, что у вас не работает - смотрите гифку :)

https://drive.google.com/file/d/1eFScmxC5mXxWV_TOL-GZUWRFv9XRkfkp/view?usp=sharing

И Таблица со всем кодом

Код отдельно

Ребята, если будут вопросы – не пишите ночью в личку :) У нас для этого есть чатик:

https://t.me/google_spreadsheets_chat

P.S. в коде Таблицы и в pastebin изменил обращение к Таблице - теперь ходим в текущую и активную Таблицу и из неё забираем данные и в неё пишем логи.

МЫ:

Канал “Google Таблицы” в Телеграме

Оглавление канала — все статьи

Наш чат в Телеграме

Донаты