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

Любимый ВБ, формируем ссылки на изображения товара

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

Вот вам функция прямо из кода страницы WB, которую мы немного адаптировали (спасибо Роману) и теперь она может работать как пользовательская функция (смотрите скриншот) и в формуле массива.

Это нужно сохранить в редакторе скриптов:

function getImageUrl(nmid) const basket= 143:'01', 
287:'02', 
431:'03', 
719:'04', 
1007:'05', 
1061:'06', 
1115:'07', 
1169:'08', 
1313:'09', 
1601:'10', 
1655:'11', 
1919:'12', 
2045:'13', 
99999:'14' 
> 
return `https://basket-$.filter(v=>Math.floor(nmid/100000)<=v)[0]]>.wb.ru/vol$/part$/$/images/big/1.jpg`

function images(nmds) return nmds.map(n => getimageUrl(n));
>

И запускаем это чудо на листе Таблицы (номенклатуры у нас в B2:B):

��‍�� Кто желает – перепишите под обычную формулу в Таблице, авторам лучших вариантов +7 кармы в нашем чате 🙂

Please open Telegram to view this post

Google Таблицы

Чат для патронов Мы давно хотели попробовать и вот наконец решились. Мы создаём чатик, в котором можно будет задавать вопросы по нашим скриптам, по формулам и по прекрасным Google Таблицам в целом, подписка на чат будет стоить 1000 рублей в месяц. Что даёт…

Что мы делаем в нашем патреон чате?

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

�� (здесь про наш патреон-чат, условия и какие темы мы в нём обсуждаем)

�� В комментариях будет код из скриншота.

8.2K views edited 11:26

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

Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE (или последние/первые цифры с помощью функций RIGHT / LEFT), они не будут готовы к употреблению сразу — это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке — там ноль, хотя внешне вроде бы числа извлеклись правильные.

Как превратить текст в число в Google Таблицах (и в Excel тоже)?

1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения

=--REGEXEXTRACT(. )

2. С помощью функции VALUE/ЗНАЧЕН.

=ЗНАЧЕН(REGEXEXTRACT(. ))

3. С помощью еще какой-нибудь математической операции, не меняющей значение, например, умножения на единицу

=REGEXEXTRACT(. )*1

P.S. Если вы сразу используете число в вычислении, допустим, вам нужно его прибавить к другому — не добавляйте +, а потом два минуса. Достаточно два минуса — и текст преобразуется в число, и сложение случится.
Например:
=A2—B2

Схватка двух. ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP. Старая и новая функции для объединения таблиц (поиска текста и чисел)

=VLOOKUP(что ищем; таблица, в которой поиск идет в первом столбце; номер столбца, из которого забираем данные; [режим поиска])

=XLOOKUP (что ищем; в каком столбце ищем; из какого столбца забираем; [на что заменяем ошибку]; [ищем текст/число/текст с подстановочными символами] ; [ищем сверху или снизу])

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

— XLOOKUP по умолчанию ищет текст (точное совпадение), а VLOOKUP — ближайшее наименьшее число.

— В режиме поиска числа (пятый аргумент, равный единице или минус единице) XLOOKUP не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;

— У XLOOKUP есть отдельный необязательный (четвертый) аргумент для замены ошибок (когда ничего не найдено) на другое значение. А в случае VLOOKUP для этого дела надо добавлять отдельную функцию IFNA.

— VLOOKUP умеет работать с символами подстановки (* и ?) по умолчанию, а XLOOKUP — нет. Чтобы использовать символы подстановки в XLOOKUP, нужно задать пятый аргумент match_mode равным 2 (по умолчанию 0 — точный поиск).

— VLOOKUP умеет только вертикально (столбцы), для горизонтального поиска используется HLOOKUP / ГПР. XLOOKUP может работать и со строками, и со столбцами.

— VLOOKUP всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое), а XLOOKUP умеет и снизу вверх (то есть найдет последнее) — для этого задаем последний аргумент search_mode равным -1.

— В Excel XLOOKUP есть только в 2021 / 365, то есть при скачивании таблицы в формате XLSX функция не будет работать в 2019 и более ранних версиях Excel.

�� Другие наши посты можно найти в оглавлении канала.

Google Таблицы

Друзья, ВПР — могущественная функция, чего только с помощью неё нельзя сделать.

На нашем канале мы написали много постов про ВПР, давайте вспомним их:

— Последний аргумент ВПР или ВПР с интервальным просмотром = 1: t.me/google_sheets/13

10.3K views edited 16:58

XLOOKUP — двойной, пожалуйста

Поиск по двум критериям (в строках и столбцах) — обычно это решается через сочетание INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ.

Но ПРОСМОТРОМ (да не простым, а икс) тоже можно.

В нашем примере ищем в матрице оценку на основе двух оценок — профессиональных и поведенческих компетенций.
Сначала одной функцией получаем массив значений для профессиональной оценки (первого критерия) — у нас это C3:C6 (оранжевое) для первого сотрудника в списке.

Потом другой уже в этом массиве ищем значение, соответствующее второму критерию — поведенческой оценке (красное).

=ПРОСМОТРX(критерий1;где ищем критерий1; ПРОСМОТРX(критерий2;где ищем критерий2;двумерный массив))

Убираем пустые ячейки из столбца / диапазона элегантно

Друзья, а у нас ведь не только сложные формулы, но и простые.

Нужно убрать пустые ячейки из диапазона и превратить его в строку или в столбец?

На помощь придут новые функции:


=torow(диапазон; 1)
=tocol(диапазон; 1)

Поэтому теперь вам необязательно использовать конструкцию вида
=filter(диапазон; один столбец из диапазона <>«») , всё можно сделать проще.

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

Друзья, если читали или смотрели хорошие материалы по Таблицам / скриптам – а поделитесь ссылками в комментариях.

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

9.2K views edited 10:04

Друзья, а мы обновили модель, которая рисует картинки в нашем ИИ-боте, теперь там dall-e-3 .

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

8.8K views edited 14:04

Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.

Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.

Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть в статье.

ПРОСМОТР (LOOKUP), да не X

Несколько слов о старой функции LOOKUP / ПРОСМОТР. Все сказанное актуально и для Google Таблиц, и для Excel (и для отечественного Р7.

Немного формульно-датового многоэтажного ада под конец года. Выдаем одной формулой все недели года в формате «30 янв-5 фев»

В деле почти все функции Google Таблиц ��LET, LAMBDA, QUERY, SEQUENCE, NETWORKDAY.INTL, MIN, DATE, MONTH, YEAR, DAYS, WEEKNUM, WEEKDAY, BYROW, TRANSPOSE, CHOOSECOLS, TEXT, LEFT, REGEXREPLACE, IF, ARRAYFORMULA в тех или иных лютых комбинациях

Друзья, наверняка у вас есть отчеты и планы, где в заголовках идут недели. Если для вас привычен формат с первым и последним днем недели, забирайте формулы от нас и маэстро нашего чата (Михаил и Игорь, спасибо!) — они выдадут все недели года одной строкой в таком формате.

Три варианта в таблице по ссылке.

— в первом случае недели идут с первого понедельника, последняя неделя будет с первыми днями следующего года («30 дек — 5 янв»). Выдаются недели за текущий год (можете поменять YEAR(TODAY()) на фиксированный год, если нужно)
— во втором случае недели тоже с первого понедельника, последняя неделя до 31 декабря («30-31 дек»), год выбирается в ячейке
— в третьем случае все недели (включая ту, что до первого понедельника), последняя до 31 декабря, год в ячейке

С наступающим Новым годом! Пусть в этих самых отчетах у вас все будет хорошо в следующем году ��Спасибо, что читаете нас!

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

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

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

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

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

Начнем с создания контейнера и написания скрипта в нем. Создаем новый 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, text : update.message.text, is_msg : true >; > sendQuestions(msgData.chat_id); >

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

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

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

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

С какими проблемами вы можете столкнуться и как их обойти

Тема Телеграм-ботов только ширится, связка «Телеграм-бот и таблица Гугл» тоже. Если вы разрабатываете дополнения для Google Sheets, то пишете код на Google App Script (GAS). Поэтому наверное, и мозги для этих ботов пишете на GAS.

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

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

getUpdates VS setWebhook?

Неважно, на каком языке вы пишете своего бота — на GAS, PHP, Pyton или чём-то ещё. В любом случае вы используете Телеграм API.

API сейчас поддерживает два способа обработки обновлений ботов: getUpdates и setWebhook.

getUpdates — это механизм pull, setWebhook — push.

Например, вы 1 раз в час запускаете getUpdates и получаете все сообщения, написанные в чате за этот час. В GAS можно настроить временной триггер с таким интервалом. Но это значит, что для нашего примера с антиспам-ботом спам будет целый час безнаказанно висеть в чате. Нехорошо это, желательно подстрелить спам сразу на подлёте.

Поэтому надо использовать setWebhook. Как написано в Чудесном путеводителе Марвина по всем вещам Webhook:

Webhook по сравнению с getUpdates

избавляет вашего бота от необходимости часто запрашивать обновления.
избегает необходимости в каком-либо механизме опроса в вашем коде.

setWebhook() + fetch(url)

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

1.. Пишем в редакторе GAS простейший скрипт:

function doPost(e) < var contents = JSON.parse(e.postData.contents); var chat_id = contents.message.from.id; // код извлечения данных и записи на лист >

Всё, в переменной contents у нас апдейт. С помощью дальнейшей обработки из него можно извлечь всё, что нужно. Например, chat_id — идентификатор отправителя сообщения, text — текст сообщения.

Дальше можно эти данные записать на лист, переслать кому-то и т.д.

2. Разворачиваем скрипт как веб-приложение.

При этом указываем параметры “Запуск от имени: От моего имени, У кого есть доступ: Все” и копируем ссылку на веб-приложение (webAppUrl).

3. Формируем ссылку.

Для этого надо соединить токен бота и урл веб-приложения следующим образом:

var token = "1234567890:ABCDEFGHIJKLMNOPQRSTUVWXYZ"; var webAppUrl = "https://script.google.com/macros/s/XXXXXXXX/exec"; var telegramUrl = "https://api.telegram.org/bot" + token;

4. Фетчим урл и устанавливаем webhook:

function setWebhook() < var url = telegramUrl + "/setWebhook?url has-medium-font-size">Можно и безо всякого скрипта перейти по этому url в браузере и увидеть

Это объект JSON, разобрав который, можно извлечь все 3 поля по отдельности — ok, result и description. Затем их можно использовать в условной логике, показывать юзеру и всё такое.

Подводные камни

Итак, у нас есть связка «Телеграм-бот и таблица Гугл», которая моментально записывает все сообщения из Телеграм-чата в таблицу. Дальше можно искать в них ключевые слова и что-то делать с этими сообщениями.

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

Всем удобны Google-сервисы. И таблицы они вам предоставляют, и среду разработки скриптов, и мощные сервера по всему свету, на которых эти скрипты работают, и многие другие вкусные плюшки. Однако есть подводные камни, и один них — квоты и ограничения Google. “Службы Apps Script имеют ежедневные квоты и ограничения на некоторые функции. Если вы превысите квоту или ограничение, ваш скрипт выдаст исключение и выполнение остановится”. Например, вызовы URL Fetch ограничены 20,000 в день, а ограничение на одновременное выполнение скрипта — 30 юзеров. Это означает, что если число пользователей бота станет достаточно большим, он перестанет работать.

И что же делать?

Например, можно купить собственный сервер или арендовать его и обрабатывать всю логику на нём. Это стоит дополнительных денег и времени, поэтому логично подумать про что-то другое.

Выход из положения

Разработчику может прийти в голову следующее.

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

Заинтересованный пользователь может это сделать в 3 клика. Надо только дать ему шаблон таблицы с внедрённым в него скриптом. Он сделает себе копию таблицы, развернёт скрипт как веб-приложение и вставит webAppUrl в нужную ячейку таблицы.

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

И очень маловероятно, что у этого пользователя в его Телеграм-группе будет больше 20,000 сообщений в день.

Всё, вроде бы, должно заработать. Не тут-то было!

Снова подводные камни

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

Как распространять наш скрипт, чтобы о нём узнали заинтересованные пользователи? Можно писать статьи на тематических площадках, давать рекламу в Google, Яндекс и т.п.

Это всё замечательно, но грех не использовать и бесплатные инструменты.

Говоря о плюшках Google, нельзя не упомянуть Google Workspace Marketplace. Там разработчики Google публикуют дополнения для продуктов Google — для таблиц, документов, почты и пр.

Таким образом, идея реализации связки «Телеграм-бот и таблица Гугл» вырисовывается такая:

1. Делаем шаблон таблицы Google.

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

2. Переносим из кода всю логику обработки сообщений Телеграм-чата в дополнение и публикуем его на Google Workspace Marketplace.

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

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

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

И опять

Проблема в том, что вы НИКАК не можете передать данные непосредственно в дополнение. Т.е. вам по вебхуку пришло сообщение, но вы в обработчик, который, напомню, вынесен в дополнение, это сообщение передать не можете.

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

Вы даже не можете передать в дополнение какой-то тег или сигнал типа “Алё, адд-он! Сообщение пришло! Запускай fetch(url) и читай!”.

Низзя. Не знаю почему, то ли из соображений безопасности, то ли по ещё каким-то причинам. Об этом можно почитать тут: How to transfer data from webapp to addon. В посте как раз рассматривается связка «Телеграм-бот и таблица Гугл».

Может прийти в голову мысль: “Мы же пишем сообщения на лист. Ставь триггер onEdit() или onChange() и, как только сообщение запишется в таблицу, триггер сработает, считает данные и всё обработает, как надо”.

Тут есть 2 закавыки.

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

В отличие от ситуации, если данные передавать, минуя листы, непосредственно из fetch() в код дополнения. Сервера у Google шустрые, и такие операции занимают миллисекунды.

Вторая закавыка заключается в том, что триггеры onEdit() или onChange() НЕ срабатывают при изменении данных на листе программным путём.

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

Подробнее об этом тут: Event Objects.

Выход есть!

На вопрос в посте на Стеке всё же нашёлся ответ. Автор даже назначил баунти за правильный ответ, но самое лучшее, что тогда ему подсказали, это использовать облачный сервис Google. Да, среди вкусных плюшек есть и такая. И это наверняка мощная штука.

Вот только она платная при превышении определённых показателей.

Да и хороших мануалов по нему я не видел.

По GAS таких ресурсов полно. Это и Google Apps Script, и mdn web docs, и ещё куча.

По Google Cloud же таких ресурсов намного меньше.

Поэтому, когда через полгода в пост пришёл Alan Wells и написал про Sheets API и USER_ENTERED, то сразу всё прояснилось.

Схема такая:
1. Включаем Sheets API в сервисах веб-приложения. Вы можете убедиться, что всё правильно, если посмотрите код файла appsscript.json. Если кто забыл, то для этого в настройках скрипта чекните флажок “Показывать файл манифеста appsscript.json в редакторе”.

В нём должен присутствовать следующий код:

"dependencies": < "enabledAdvancedServices": [< "userSymbol": "Sheets", "serviceId": "sheets", "version": "v4" >] >

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

2. Обычно мы как пишем данные в таблицу?

Используем setValue(value). Типичный код:

var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange("B2"); cell.setValue(100);

Код должен выглядеть примерно так:

var SHEETID = 'XXXXXXX'; function doPost(e) < var data = e.postData.contents; var rowValues = [ [data, ""], ]; var request = < 'valueInputOption': 'USER_ENTERED', 'insertDataOption': 'INSERT_ROWS', 'data': [ < "range": "TEMP!A2:B2", "majorDimension": "ROWS", "values": rowValues, >, ], >; var response = Sheets.Spreadsheets.Values.batchUpdate(request, SHEETID); >

Ключевыми здесь являются два момента:

А. Запись на лист производим с помощью batchUpdate().

Б. Параметр ‘USER_ENTERED’ как бы говорит триггеру, что данные на листе изменены не программным путём, а пользователем.

Связка «Телеграм-бот и таблица Гугл» работает

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

Подробнее обо всём об этом можно почитать тут: Телеграм-Помощник: Описание и руководство. Там же есть ссылка и на дополнение, и на таблицу со скриптом. Можете всё это свободно использовать.

И буду признателен за конструктивную критику и советы!

Warning: shell_exec(): Cannot execute a blank command in /var/www/u0484342/public_html/baublys.ru/wp-content/themes/hestia/comments.php on line 2

3 комментария

Я · 15 сентября, 2023 в 5:24 пп

var response = Sheets.Spreadsheets.Values.batchUpdate(request, SHEETID);
Что такое Sheets и как его получить?

Boris · 8 ноября, 2023 в 6:12 дп

В редакторе Apps Script нужно добавить сервис Sheets.

Фрагменты (snippets) в браузере — Гугл Таблицы и Формы · 27 октября, 2022 в 9:13 пп

[…] Предыдущая статья: Телеграм-бот и таблица Гугл: решение некоторых проблем […]

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Гугл таблица как БД для телеграм бота (запись в таблицу)

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

Полезное отступление

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

Например, мое сообщение /start отправляет HTTP POST запрос к веб-приложению (веб-приложение это наш скрипт). Скрипт, в свою очередь, с помощью Simple trigger doPost(e) вернет пакет как в снипете ниже.

< update_id=2.96528127E8, message=< text=/start, entities=[Ljava.lang.Object;@73afdadb, message_id=116.0, from=< first_name=Nadya, language_code=ru, is_bot=false, username=brainwashed_from_rock, >, date=1.680698505E9, chat= < username=brainwashed_from_rock, first_name=Nadya, type=private, >> >

e в doPost(e) означает event. Далее обращаемся к документации и изучаем методы для event. Находим e.postData.contents и используем его для парсинга 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 >; > sendQuestions(msgData.chat_id); >

В функции я объявляю переменную update, в которую записываю содержание пакета. Далее объявляю объект msgData, проверяю есть ли у переменной update свойство ‘message‘ и записываю в msgData нужные мне значения.

Помимо свойства message могут быть callback_data, edited_message, forwarded_message и так далее. При этом возвращаемый пакет будет содержать разные ключи и вложенность и, соответственно, в парсинге необходимо предусматривать все кейсы, которые нас интересуют.

У нас есть кейс с кнопками, нажатие на какую-либо из них возвращает пакет вида

< update_id=2.96528133E8, callback_query=< chat_instance=-2700514822492676651, message=< text=Say my name. chat=< type=private, username=brainwashed_from_rock, first_name=Nadya >, message_id=121.0, date=1.680698507E9, from=< is_bot=true, username=Testforpost_Bot, first_name=Test for post >, reply_markup= < inline_keyboard=[Ljava.lang.Object;@39c491d8 >>, data=Финн парнишка, from= < username=brainwashed_from_rock, language_code=ru, first_name=Nadya, is_bot=false, >> >

Очевидно, что пакеты различаются и порядок парсинга тоже изменится. Дополним функцию doPost(e) обработкой нажатия кнопки:

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, date : (update.message.date/86400)+25569.125, is_msg : true >; > else if (update.hasOwnProperty('callback_query')) < msgData = < id : update.callback_query.message.message_id, chat_id : update.callback_query.message.chat.id, user_name : update.callback_query.from.username, first_name : update.callback_query.from.first_name, text : update.callback_query.message.text, date : (update.callback_query.message.date/86400)+25569.125, data : update.callback_query.data, is_button : true >//sendQuestions(msgData.chat_id); >

Сам объект msgData можно дополнять любыми необходимыми вам значениями. В моем примере, помимо некоторых данных из пакета, я добавила идентификаторы is_msg и is_button.

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

План минимум

Функцию doPost(e) я оставлю только для парсинга и вызову в ней в последней строке новую функцию dataHandler(msgData). Обозначим ее пока приблизительно, проверив какое событие (event) вернулось — сообщение или кнопка.

function dataHandler(msgData) < if (msgData.is_msg) < sendQuestions(msgData.chat_id); >else if (msgData.is_button) < saveData(msgData) >>

Если бот получил сообщение, отправляем вопросы, если кнопку, сохраняем ответы. Структура таблицы для сохранения ответов следующая:

Функция сохранения ответов saveData(msgData):

function saveData(msgData)

В переменную vals я записываю сохраняемые данные в том порядке, в котором они будут выведены в таблице. На лист usersSheet вставляю новую строку с помощью метода appendRow() и указанием вставляемого массива. Результат выполнения функции приведен на скрине выше во второй строке таблицы.

Далее будем проверять правильность ответов и возвращать пользователю результат. Здесь нам нужно сопоставить сообщение с вопросом из таблицы, затем вопрос из таблицы — с ответами из таблицы Answers и проверить выбранный из 4 вариантов ответ на корректность.

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

В функции sendQuestions(chat_id) вместо строки send(e[1], chat_id, keyboard) напишу две следующие:

const question = e[0]+'/'+questionsArr.length+': '+e[1] send(question, chat_id, keyboard)

Таким образом, в объекте msgData после нажатия кнопки мы можем спарсить ид вопроса и записать его в таблицу с ответами, изменив функцию saveData(msgData):

function saveData(msgData)

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

*Если дата записана в виде магических чисел, измените формат ячеек в соответствующей колонке.

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

После ответа бот показывает правильность ответов в тексте кнопок, а в тексте сообщения выводит ответ пользователя и корректность данного ответа.

Для начала обозначим функцию, которая редактирует уже отправленное сообщение:

function editMsg(msg, chat_id, msg_id, keyboard) < const payload = < 'method': 'editMessageText', 'chat_id': String(chat_id), 'message_id': String(msg_id), 'text': msg, 'parse_mode': 'HTML' >if (keyboard) payload.reply_markup = JSON.stringify(keyboard) const data = < 'method': 'post', 'payload': payload, 'muteHttpExceptions': true >UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/', data); >

Функция использует метод editMessageText, обязательными параметрами являются text, chat_id и message_id. Дополнительно мы можем указать другие параметры, в том числе клавиатуру.

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

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

Начнем с ответов из таблицы, записав их в переменную answersArr, что мы уже делали в функции отправки вопросов.

function editMessage(msgData)

Воспользуюсь методом массива .filter(), оставив варианты ответа только для текущего вопроса:

const answersArr = answersSheet.getDataRange().getValues().filter(e => e[0] == msgData.text[0]);

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

Сохраним в переменную curAnswerArr строку из таблицы с ответами, которая соответствует данному пользователем ответу:

const curAnswerArr = answersArr.find(e => e[1] == msgData.data);

Здесь в уже отфильтрованном массиве из 4х вариантов ответов я выбираю один, текст которого равен тексту в кнопке.

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

let newText = new String(); if (curAnswerArr[2]) newText = msgData.text + '\n\n✅Wow\nТвой ответ: '+msgData.data; else newText = msgData.text + '\n\n❌Nope\nТвой ответ: '+msgData.data;

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

И наконец прописываем строку с вызовом функции editMsg() и передаем новый текст сообщения, ид чата, ид сообщения и null для клавиатуры.

editMsg(newText,msgData.chat_id,msgData.id,null);

Вся функция saveData(msgData) представлена ниже:

function editMessage(msgData) < const answersArr = answersSheet.getDataRange().getValues().filter(e =>e[0] == msgData.text[0]); const curAnswerArr = answersArr.find(e => e[1] == msgData.data); let newText = new String(); if (curAnswerArr[2]) newText = msgData.text + '\n\n✅Wow\nТвой ответ: '+msgData.data; else newText = msgData.text + '\n\n❌Nope\nТвой ответ: '+msgData.data; editMsg(newText,msgData.chat_id,msgData.id, null); >

На этом этапе деплоим и проверяем, что все работает:

Клавиатура под сообщением пропала, т.к. в editMsg вместо клавиатуры мы передали null.

Редактирование клавиатуры оставлю читателю для самостоятельного изучения. Может один из моих старых постов поможет ��

План максимум (advanced)

Перейдем к вопросам в комментах:

как отправлять вопросы последовательно (следующий вопрос после ответа на предыдущий);
как отправить свой ответ.

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

*Я не буду погружаться в детали, как делала до этого, иначе чтиво затянется на часы. Прошу понять и принять, что эта часть скорее про дополнительные фичи.

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

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

Тогда можно вынести ид вопросов в отдельные колонки. Изменю таблицу Users так, чтобы колонка C стала колонкой progress, что отражает на каком этапе квиза данный пользователь. Названия колонок E-H соответствуют ид вопросов.

*Используйте функцию Transpose для быстрого добавления названий колонок.

Отправка вопросов последовательно

Вернемся к функции отправки вопросов, скопируем ее и переименуем копию в sendQuestion, оригинальная функция вам еще может пригодиться. Также я вынесу формирование клавиатуры в отдельную функцию createKeyboard() и получу следующее:

function sendQuestion(msgData) < const questionsArr = questionsSheet.getDataRange().getValues(); const questionArr = questionsArr.find(e =>e[0] == 'ид вопроса').flat(); const question = questionArr[0]+'/'+questionsArr.length+': ' +questionArr[1]; const keyboard = createKeyboard(questionArr); send(question, msgData.chat_id, keyboard) //const vals = [msgData.chat_id, msgData.user_name, 1, msgData.date] //usersSheet.appendRow(vals); > function createKeyboard(questionArr) < const answersArr = answersSheet.getDataRange().getValues(); let arr = answersArr.filter(el =>el[0] == questionArr[0]) arr = arr.map(el => []) const keyboard = < "inline_keyboard": arr >return keyboard >

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

function sendQuestion(msgData, nextState) < const questionsArr = questionsSheet.getDataRange().getValues(); if (nextState e[0] == nextState).flat(); const question = questionArr[0]+'/'+questionsArr.length+': ' +questionArr[1]; const keyboard = createKeyboard(questionArr); send(question, msgData.chat_id, keyboard) > else < const msg = 'Поздравляю! Квиз пройден' send(msg, msgData.chat_id, null) >>

Если вопрос был последним, отправляем поздравительное сообщение.

Разбираемся с этим nextState. Так как все ид просто увеличиваются на 1, то следующий вопрос это последний отправленный ид + 1. А текущее состояние или progress, нужно забрать из таблицы, если пользователь нажал на кнопку.

Добавлю функцию getRow(chat_id), которая по чат ид находит последнюю попытку на вкладке usersSheet.

function getRow(chat_id) < const usersArr = usersSheet.getDataRange().getValues(); const ind = usersArr.findIndex(e =>e[0] == chat_id); if (ind < 0) return null else < const rowArr = usersArr[ind] const rowMap = new Map(); rowMap.set('ind', rowInd) return rowMap; >>

Я решила создать объект Map и внести туда данные о пользователе в виде ключ — значение, что также позволяет сохранить определенную последовательность этих пар.

В rowMap я внесу все ключи и присвою им соответствующие значения. Также нам нужно просматривать массив всех пользователей не с начала, а с конца, т.к. самый «свежий» старт квиза записывается в конец таблицы, поэтому воспользуюсь методом массива .reverse().

function getRow(chat_id) < const usersArr = usersSheet.getDataRange().getValues(); const headerUsersArr = usersArr.shift().flat(); usersArr.reverse(); const ind = usersArr.findIndex(e =>e[0] == chat_id); if (ind < 0) return null else < const rowInd = usersArr.length - ind; const rowArr = usersArr[ind] const rowMap = new Map(); rowMap.set('ind', rowInd) rowArr.forEach((e,i) =>rowMap.set(headerUsersArr[i], e)); return rowMap; > >

headerUsersArr это шапка таблицы, то есть названия колонок. В rowMap при этом запишутся значения в условном виде: . Теперь из этого объекта можно достать текущий прогресс:

function getNextState(rowMap)

Функция для обновления строки в таблице. Я записываю юзернейм (т.к. может менятся), ответ на текущий вопрос, ид следующего вопроса, дату.

function setChatsVals(msgData, progress, rowMap) < rowMap.set("username", msgData.user_name); rowMap.set(rowMap.get("progress"), msgData.data); rowMap.set("progress", progress); rowMap.set("date", msgData.date); const rowArr = new Array(); const iterator = rowMap.values(); rowMap.forEach(() =>rowArr.push(iterator.next().value)); const ind = rowArr.shift(); usersSheet.getRange(ind+1,1,1,rowArr.length).setValues([rowArr]); >

И наконец записываю в нужную строку измененный и трансформированный в массив объект Map.

Теперь изменим dataHandler(msgData) под новую логику:

function dataHandler(msgData) < if (msgData.is_msg) < if (msgData.text == '/start') < saveData(msgData); sendQuestion(msgData,nextState=1); >> else if (msgData.is_button) < editMessage(msgData) const rowMap = getRow(msgData.chat_id); const nextState = getNextState(msgData, rowMap); sendQuestion(msgData, nextState); setChatsVals(msgData, nextState, rowMap); >>

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

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

Отправить свой вариант ответа

Теперь мы можем добавить возможность давать свободный ответ. Пример чата и таблицы ниже.

Изменим setChatsVals(msgData, progress, rowMap, flag):

function setChatsVals(msgData, progress, rowMap, flag) < flag == 'is_button' ? rowMap.set(rowMap.get("progress"), msgData.data) : rowMap.set(rowMap.get("progress"), msgData.text); rowMap.set("username", msgData.user_name); rowMap.set("progress", progress); rowMap.set("date", msgData.date); const rowArr = new Array() const iterator = rowMap.values() rowMap.forEach(() =>rowArr.push(iterator.next().value)) const ind = rowArr.shift(); usersSheet.getRange(ind+1,1,1,rowArr.length).setValues([rowArr]) >

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

И немного отредактируем dataHandler(msgData):

function dataHandler(msgData) < const rowMap = getRow(msgData.chat_id); if (msgData.is_msg) < if (msgData.text == '/start') < saveData(msgData); sendQuestion(msgData,nextState=1); >else < const nextState = getNextState(rowMap); sendQuestion(msgData, nextState); setChatsVals(msgData, nextState, rowMap, 'is_msg'); >> else if (msgData.is_button) < editMessage(msgData) const nextState = getNextState(rowMap); sendQuestion(msgData, nextState); setChatsVals(msgData, nextState, rowMap, 'is_button'); >>

Здесь в условии is_msg я прописала исключение и включила туда ту же самую логику, как и для кнопки. На этом все!

Заключение

Основная задача, которую я ставила в самом первом посте, а именно «Гугл таблица как база данных», на мой взгляд, выполнена. Мы можем перекидавать данные из гугл таблицы в телеграм чат и из чата в гугл таблицу.

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

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