Как сделать таблицу в Телеграм боте

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

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

Ниже представлен скрин из чата с ботом.

Дисклеймер: если вы здесь в первый раз, пожалуйста ознакомьтесь с первым постом, где более детально разобраны скрипты в гугл таблицах (тыц).

Начнем с создания контейнера и написания скрипта в нем. Создаем новый Spreadsheet.

В качестве забираемых значений укажу 4 вопроса на листе Questions.

И варианты ответов для каждого вопроса на листе Answers. Данные из двух таблиц связаны по ИД – первая колонка.

Как создать последовательные сообщения/ вопросы в телеграм боте и записать ответы в Гугл таблицу

В таблицу Users буду вносить ответы от пользователей, но об этом позже.

Переходим к скрипту. Укажу в качестве глобальных переменных следующие значения:

const doc = SpreadsheetApp.getActive(); const questionsSheet = doc.getSheetByName(«Questions»); const answersSheet = doc.getSheetByName(«Answers»); const usersSheet = doc.getSheetByName(«Users»);

Так как скрипт мы разворачиваем в том же документе (контейнере), то нам достаточно использовать метод getActive(). Если вы ссылаетесь на другой Spreadsheet, то можно на него сослаться методами .openByUrl() или .openById().

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

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

function sendQuestions()

Запустить функцию можно из меню, выбрав имя функции и кликнув Run

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

Разберем строку в функции по частям.

const questionsArr = questionsSheet.getRange(1,1,questionsSheet.getLastRow(), questionsSheet.getLastColumn()).getValues()

Здесь мы объявляем переменную для записи в нее возвращаемого массива. Далее указываем лист, из которого будем забирать значения questionsSheet. Для листа выбираем метод .getRange() и указываем ячейки, к которым функция должна обратиться.

Перевожу написанное в скобках метода getRange на понятный язык:

getRange(номер строки начала диапазона , номер столбца начала диапазона , номер строки конца диапазона , номер столбца конца диапазона)

Методами листа .getLastRow() и .getLastColumn() получаем номер последней строки и столбца, которые будут равны 4 и 2 соответственно. При этом методы возвращают последние столбец и строку, в которых указано значение (ячейка не пуста) или есть валидация.

Как создать чат-бот в Telegram с выводом информации из Google таблицы

В то же время можно указать в скобках questionsSheet.getRange(«A1:B4»).

Возвращаемся к функции и к методу диапазона .getValues(). Здесь обратим внимание на то, что существует также метод .getValue(), применять который следует, если мы обращаемся к одной ячейке, например questionsSheet.getRange(3,4) или questionsSheet.getRange(«D3»).

getValues() при этом возвращает двумерный массив, а getValue() — значение.

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

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

По задумке бот отправляет все вопросы одновременно с кнопками. Сначала попробуем просто отправить вопросы без вариантов ответов.

Обратимся к функции send().

function send(msg, chat_id) < const payload = < ‘method’: ‘sendMessage’, ‘chat_id’: String(chat_id), ‘text’: msg, ‘parse_mode’: ‘HTML’ >const data = < ‘method’: ‘post’, ‘payload’: payload, ‘muteHttpExceptions’: true >UrlFetchApp.fetch(‘https://api.telegram.org/bot’ + token + ‘/’, data); >

В функцию мы передаем текст отправляемого ботом сообщения и ид чата, в который это сообщение будет отправлено. Внутри функции объявляем объект payload и указываем передаваемые параметры. Я использую только необходимые в рамках задачи параметры (method, chat_id, text, parse_mode), но их может быть и больше (ссылка на апи телеграмма для метода sendMessage).

В переменную data передаем payload (передаваемые параметры для метода апи «post») и указываем сам метод post.

В строке UrlFetchApp мы обращаемся к АПИ телеги по ссылке (таким же образом можно обратиться к любому другому доступному АПИ). В этой же строке мы передаем значение переменной token и data. Про token чуть позже.

Вернемся к функции sendQuestions() и вызовем из нее send() для каждого элемента массива questionsArr.

Чтобы обратиться к каждому элементу можно воспользоваться циклами типа for или while. Но я предпочитаю использовать методы массивов и стрелочные функции, которые умещаются в одну строку.

Таким образом, вместо конструкции

for (let i=0; i

я могу написать

questionsArr.forEach(e => send(e[1],chat_id))

Итак, в качестве текста сообщения я забираю из вложенного массива элемент на позиции [1] (на позиции 0 указан ид вопроса).

Нам не достает только чат ид, который я укажу в качестве аргумента функции sendQuestions() и получу следующее:

function sendQuestions(chat_id) < const questionsArr = questionsSheet.getRange(1,1,questionsSheet.getLastRow(), questionsSheet.getLastColumn()).getValues(); Logger.log(questionsArr); questionsArr.forEach(e =>send(e[1],chat_id)); >

Наконец, функцию sendQuestions(chat_id) мы вызовем, когда пользователь запустит бота по команде /start. Пропишем эту логику.

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

function doPost(e) < const update = JSON.parse(e.postData.contents); let msgData = <>if (update.hasOwnProperty(‘message’)) < msgData = < id : update.message.message_id, chat_id : update.message.chat.id, user_name : update.message.from.username, text : update.message.text, is_msg : true >; > >

Результатом функции является объект msgData с ключами id, chat_id, user_name, text, is_msg. Возвращаемые ключи вы можете определить сами, для простоты я указала только те, которые нужны для задачи.

Здесь же я передам в функцию отправки вопросов значение ключа chat_id.

function doPost(e) < const update = JSON.parse(e.postData.contents); let msgData = <>if (update.hasOwnProperty(‘message’)) < msgData = < id : update.message.message_id, chat_id : update.message.chat.id, user_name : update.message.from.username, text : update.message.text, is_msg : true >; > sendQuestions(msgData.chat_id); >

Как создать бота и получить токен уже описывалось здесь. Берем этот токен и записываем в глобальную переменную token. Далее деплоим приложение (смотреть ссылку выше) и записываем URL веб приложения в переменную appLink.

И наконец функция api_connector() для установки веб хука.

Запускаем эту функцию по кнопке Run.

После успешной установки вебхука запускаем бота в телеграме. В ответ получаем вопросы

Источник: habr.com

Telegram-оповещения на базе Power BI: создайте своего бота в Телеграм

Telegram — один из самых удобных способов получать оповещения. Если вам некогда заходить в отчёт, бывает важно видеть последние цифры в одном кратком сообщении. Особенно если источников данных много и заходить во все сервисы нет времени.

Но если вы умеете делать отчёты в Power BI и больше ничего, как тогда настроить оповещения в Telegram? Функционал получения данных в Power BI вас вполне устраивает — он довольно удобен. Можно ли обойтись им или нужны другие инструменты?

На самом деле можно! Вы можете создать бота в Telegram на базе Power BI, который каждый день будет присылать вам оповещения.

Схема достаточно проста:

• получаете данные из разных источников в Power BI
• рассчитываете нужные цифры из каждого источника,
• формируете из цифр, букв и смайликов ёмкое сообщение (сообщение можно сделать разным в зависимости от полученных чисел),
• создаёте бота в Telegram и разрешаете ему публикацию,
• отправляете сообщение от имени бота себе в личку или в Telegram-канал (открытый или закрытый канал — решайте сами),
• форматируете код, чтобы набор данных обновлялся онлайн,
• выгружаете отчёт в Power BI Service и настраиваете расписание обновлений.

Пройдёмся по схеме более детально

Какие данные получить — решать вам, этот пункт разбирать не вижу смысла.
Что касается цифр — важно понимать, что в Telegram отправится не отчёт, а краткое сообщение. Поэтому столбцы или строки здесь не подойдут. Важно вычислить одно или несколько значений, которые и будут потом отправлены в сообщении. Удобно, что в Power Query уже есть кнопки для вычисления суммы, среднего и других общих величин.

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

Number.From() // получить число например из текста, Text.From() // получить текст из числа или даты, Date.From() // получить дату из текста или числа.

Еще несколько важных функций, которые могут помочь отфильтровать данные и посчитать расхождения:

Date.AddDays(Date.From(DateTime.LocalNow()) , -1) // вчерашний день в формате даты (если хотите фильтровать данные за вчера), Number.Abs() // вычисление модуля числа (если требуется только положительная часть).

Важно понимать, что вчерашнего дня (или любого другого дня) может просто не быть в статистике. Это важно предусмотреть, например с помощью конструкции try-otherwise.

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

Если хотите больше узнать о функциях и преобразованиях в Power Query — у меня на канале есть целый плейлист для новичков на эту тему: https://www.youtube.com/playlist?list=PL3du-Tm1nAm6SSQOCpryquOx-6aasPARM

Когда вы посчитали нужные числа, добавьте для них новые переменные, где эти числа будут в формате текста. Тут как раз поможет Text.From(). Почему для чисел нужны отдельные текстовые переменные? Дело в том, что на основе чисел удобно писать условия (например больше / меньше / равно), но сообщение мы формируем из текстовых переменных. Поэтому для условий и для сообщения нам нужны разные переменные.

Сформировать сообщение — самое простое. Для этого вы объединяете текстовые переменные значком 10000 then «Большой расход» else «Маленький расход»

Переменная «РасходВчера» должна быть числом, а переменная message — будет текстом. Соответственно сегодняшнее сообщение (message) будет зависеть от вчерашнего расхода.

Создать и настроить бота — тоже несложно. Достаточно выполнить пункт 1 инструкции: https://habr.com/ru/post/262247/. Если вы хотите отправлять сообщения от бота себе, то вам потребуется запустить диалог с ним. Для этого отправляете боту команду /start. Тоже самое может сделать и другой пользователь.

Бот сможет отправлять пользователю сообщения только если тот запустит бота командой /start.

Не забудьте сохранить токен бота, он понадобится для отправки любых сообщений. Помимо токена для отправки сообщения от бота пользователю или в канал нужно знать ID пользователя или канала.

ID пользователя можно уточнить у такого же бота — userinfobot. При запуске userinfobot отправляет пользователю его ID, имя и фамилию. Именно ID нам и понадобится для отправки сообщений пользователю от бота. Сохраняем его вместе с токеном.

Другой вариант — отправка сообщений в Telegram-канал. Создайте закрытый или открытый Telegram-канал и добавьте туда бота. Дайте боту права администратора и возможность постить сообщения. После чего отправьте какое-то сообщение в этот канал от себя и перейдите по ссылке:

https://api.telegram.org/bot1234567:AGFnH-98pywSLbAuMTDGbpOLMRiY/getupdates

Токен бота в ссылке замените на свой.

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

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

Теперь сама отправка сообщений — содержимое переменной «post» выглядит так:

try Json.Document(Web.Contents( «https://api.telegram.org/bot1234567:AGFnH-98pywSLbAuMTDGbpOLMRiY/sendMessage», [Query = [chat_id=»123456789″,text=message], Content = Text.ToBinary(«»)] ))[ok] otherwise false

Назовём этот код «отправка сообщения». Токен и ID пользователя/канала заменяете на свои, а message — это текстовая переменная сообщения, которую мы вычислили ранее. Если бот не смог отправить сообщение — он выдаст false, если смог — true.

Рекомендую ставить этот шаг последним и завершать запрос словами «in post». Не забывайте, что между шагами в Power Query всегда ставится запятая. Именно запятая, а не перенос строки является разделителем шагов в Power Query.

Тут нас ждёт самое сложное — нужно заставить запрос обновляться онлайн.
Я выявил 2 важных условия, которые помогли мне настроить онлайн-обновление:
• бот должен представлять собой 1 запрос в Power Query
• бот должен представлять собой 1 шаг в Power Query.

Да, это неприятно, но иначе он будет постоянно ругаться и требовать перестроить эту комбинацию данных.

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

let a = 1, b=a*2, c=a*b*3 in c
let x = 1, y=x*2, z=x*y*3 in z

Теперь мы начинаем строить самого бота и копируем туда их код, занося каждый из запросов в свою переменную и разделяя шаги запятыми.
Назовём этот кусок кода «реализация бота»:

let first = let a = 1, b=a*2, c=a*b*3 in c, second = let x = 1, y=x*2, z=x*y*3 in z, message = Text.From( first) query» в таблицу:

let query = реализация бота, tab = #table(1, >) in tab

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

Уберём наши обозначения и посмотрим что в итоге получилось:

let query = let first = let a = 1, b=a*2, c=a*b*3 in c, second = let x = 1, y=x*2, z=x*y*3 in z, message = Text.From( first) https://api.telegram.org/ bot1234567:AGFnH-98pywSLbAuMTDGbpOLMRiY /sendMessage», [Query = [chat_id=»123456789″,text=message], Content = Text.ToBinary(«»)]))[ok] otherwise false in post, tab = #table(1, >) in tab

Выгрузка в Power BI Service. На этом этапе я рекомендую сделать копию pbix файла. В этой копии вы оставляете только 1 запрос — запрос бота. Именно его мы и будем выгружать в веб.

Если вы не пишете код вручную, для расчётов вы использовали другие запросы. Пусть эти запросы будут под рукой — они понадобятся при доработках. Но в веб я рекомендую выгружать файл pbix, содержащий 1 запрос, в котором находится всего 1 шаг. Так вы точно не ошибётесь.

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

Результат

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

Бот присылает ровно 1 сообщение в день и обновляется корректно. Если вы заметите проблемы с обновлением при таком построении запросов, скорее всего это проблемы с другими источниками в файле Power BI.

Еще раз перечислю важные моменты:

• Бот должен находиться в 1 запросе и 1 шаге (без ссылок на другие запросы),
• Из Power Query в модель лучше выгружать именно таблицу,
• В Power BI Service нужно пропустить тестирование подключение для Telegram,
• Чтобы застраховаться от отсутствия данных за какой-то день используем try-otherwise.

Пишите в комментариях удалось ли вам построить бота и оставляйте скрины сообщений, которые он отправляет;)

Источник: directprobi.ru

Гугл таблица как бд для телеграм бота

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

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

Ниже представлен скрин из чата с ботом.

Дисклеймер: если вы здесь в первый раз, пожалуйста ознакомьтесь с первым постом, где более детально разобраны скрипты в гугл таблицах (тыц).

Начнем с создания контейнера и написания скрипта в нем. Создаем новый Spreadsheet.

В качестве забираемых значений укажу 4 вопроса на листе Questions.

И варианты ответов для каждого вопроса на листе Answers. Данные из двух таблиц связаны по ИД – первая колонка.

В таблицу Users буду вносить ответы от пользователей, но об этом позже.

Переходим к скрипту. Укажу в качестве глобальных переменных следующие значения:

const doc = SpreadsheetApp.getActive(); const questionsSheet = doc.getSheetByName(«Questions»); const answersSheet = doc.getSheetByName(«Answers»); const usersSheet = doc.getSheetByName(«Users»);

Так как скрипт мы разворачиваем в том же документе (контейнере), то нам достаточно использовать метод getActive(). Если вы ссылаетесь на другой Spreadsheet, то можно на него сослаться методами .openByUrl() или .openById().

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

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

function sendQuestions()

Запустить функцию можно из меню, выбрав имя функции и кликнув Run

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

Разберем строку в функции по частям.

const questionsArr = questionsSheet.getRange(1,1,questionsSheet.getLastRow(), questionsSheet.getLastColumn()).getValues()

Здесь мы объявляем переменную для записи в нее возвращаемого массива. Далее указываем лист, из которого будем забирать значения questionsSheet. Для листа выбираем метод .getRange() и указываем ячейки, к которым функция должна обратиться.

Перевожу написанное в скобках метода getRange на понятный язык:

getRange(номер строки начала диапазона , номер столбца начала диапазона , номер строки конца диапазона , номер столбца конца диапазона)

Методами листа .getLastRow() и .getLastColumn() получаем номер последней строки и столбца, которые будут равны 4 и 2 соответственно. При этом методы возвращают последние столбец и строку, в которых указано значение (ячейка не пуста) или есть валидация.

В то же время можно указать в скобках questionsSheet.getRange(«A1:B4»).

Возвращаемся к функции и к методу диапазона .getValues(). Здесь обратим внимание на то, что существует также метод .getValue(), применять который следует, если мы обращаемся к одной ячейке, например questionsSheet.getRange(3,4) или questionsSheet.getRange(«D3»).

getValues() при этом возвращает двумерный массив, а getValue() — значение.

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

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

По задумке бот отправляет все вопросы одновременно с кнопками. Сначала попробуем просто отправить вопросы без вариантов ответов.

Обратимся к функции send().

function send(msg, chat_id) < const payload = < ‘method’: ‘sendMessage’, ‘chat_id’: String(chat_id), ‘text’: msg, ‘parse_mode’: ‘HTML’ >const data = < ‘method’: ‘post’, ‘payload’: payload, ‘muteHttpExceptions’: true >UrlFetchApp.fetch(‘https://api.telegram.org/bot’ + token + ‘/’, data); >

В функцию мы передаем текст отправляемого ботом сообщения и ид чата, в который это сообщение будет отправлено. Внутри функции объявляем объект payload и указываем передаваемые параметры. Я использую только необходимые в рамках задачи параметры (method, chat_id, text, parse_mode), но их может быть и больше (ссылка на апи телеграмма для метода sendMessage).

В переменную data передаем payload (передаваемые параметры для метода апи «post») и указываем сам метод post.

В строке UrlFetchApp мы обращаемся к АПИ телеги по ссылке (таким же образом можно обратиться к любому другому доступному АПИ). В этой же строке мы передаем значение переменной token и data. Про token чуть позже.

Вернемся к функции sendQuestions() и вызовем из нее send() для каждого элемента массива questionsArr.

Чтобы обратиться к каждому элементу можно воспользоваться циклами типа for или while. Но я предпочитаю использовать методы массивов и стрелочные функции, которые умещаются в одну строку.

Таким образом, вместо конструкции

for (let i=0; i

я могу написать

questionsArr.forEach(e => send(e[1],chat_id))

Итак, в качестве текста сообщения я забираю из вложенного массива элемент на позиции [1] (на позиции 0 указан ид вопроса).

Нам не достает только чат ид, который я укажу в качестве аргумента функции sendQuestions() и получу следующее:

function sendQuestions(chat_id) < const questionsArr = questionsSheet.getRange(1,1,questionsSheet.getLastRow(), questionsSheet.getLastColumn()).getValues(); Logger.log(questionsArr); questionsArr.forEach(e =>send(e[1],chat_id)); >

Наконец, функцию sendQuestions(chat_id) мы вызовем, когда пользователь запустит бота по команде /start. Пропишем эту логику.

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

function doPost(e) < const update = JSON.parse(e.postData.contents); let msgData = <>if (update.hasOwnProperty(‘message’)) < msgData = < id : update.message.message_id, chat_id : update.message.chat.id, user_name : update.message.from.username, text : update.message.text, is_msg : true >; > >

Результатом функции является объект msgData с ключами id, chat_id, user_name, text, is_msg. Возвращаемые ключи вы можете определить сами, для простоты я указала только те, которые нужны для задачи.

Здесь же я передам в функцию отправки вопросов значение ключа chat_id.

function doPost(e) < const update = JSON.parse(e.postData.contents); let msgData = <>if (update.hasOwnProperty(‘message’)) < msgData = < id : update.message.message_id, chat_id : update.message.chat.id, user_name : update.message.from.username, first_name : update.message.from.first_name, text : update.message.text, date : update.message.date/86400+25569.125, is_msg : true >; > sendQuestions(msgData.chat_id); >

Как создать бота и получить токен уже описывалось здесь. Берем этот токен и записываем в глобальную переменную token. Далее деплоим приложение (смотреть ссылку выше) и записываем URL веб приложения в переменную appLink.

И наконец функция api_connector() для установки веб хука.

Запускаем эту функцию по кнопке Run.

После успешной установки вебхука запускаем бота в телеграме. В ответ получаем вопросы

Продолжение следует…

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

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

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

Продолжение постараюсь подготовить в разумные сроки и показать, как массивы с ответами на вопросы превратить в кнопки. Будет интересно!

Источник: savepearlharbor.com