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

Передача данных из «Google Таблиц» в Telegram – это просто!

Вы думаете, что настроить передачу данных из Google Sheets в Telegram – это сложно? На самом деле, всё гораздо проще, чем можно себе представить!

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

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

Кому и зачем нужна интеграция «Телеграмма» с «Гугл Таблицами»?

Рассмотрим только две ситуации, хотя на самом деле их огромное множество. Допустим, у вас есть интернет-магазин, вы пока не используете CRM и фиксируете информацию о заказах и клиентах в «Гугл Таблице». Вам может понадобиться распределить поступающие заказы между ответственными менеджерами. Часто при этом передача данных осуществляется вручную. Если заказов 10-20, то справиться можно, хотя это и потребует дополнительных временных затрат. Но как быть, если в день поступает 50, 100 и больше заказов и идут они из разных источников? Трудно ничего не пропустить и не допустить ошибку.

Другой пример, вы работаете на фрилансе и получаете задачи от клиента в «Гугл Таблицах», чтобы ничего не пропустить нужно постоянно заглядывать в файл или ждать, когда о новых задачах сообщит заказчик. Если оба замотались — пропущена задача и снова потеряно время.

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

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

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

Как связать работу «Google Таблиц» и Telegram с помощью ApiX-Drive?

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

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

Настраиваем Google Sheets

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

Указываем, о каком действии будут отправляться уведомления в «Телеграмм». Нам нужно, чтобы приходила информация о новых заказах, поэтому выбираем «Загрузить строки (новые)».

Теперь подключаем аккаунт, в котором находится таблица с заказами. Если вы выполнили вход в профиль любого сервиса «Гугла» — система сама распознает, ваш аккаунт, вам останется только предоставить ей доступ к данным.

Выбираем файл, с которым будем связывать Telegram. ApiX-Drive подгрузит все таблицы с вашего «Гугл Диска», указываем нужную. Если в ней много листов — понадобится указать конкретный лист, с которого системе нужно брать данные.

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

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

Свяжите сервисы между собой без программистов за 5 минут!

Используейте ApiX-Drive для самостоятельной интеграции разных сервисов между собой. Доступно 350+ готовых интеграций.

• Автоматизируйте работу интернет магазина или лендинга
• Расширяйте возможности за счет интеграций
• Не тратьте деньги на программистов и интеграторов
• Экономьте время за счет автоматизации рутинных задач

Бесплатно протестируйте работу сервиса прямо сейчас и начните экономить до 30% времени! Перейти

Чтобы это настроить, нажимаем «Добавить условие фильтрации».

В «Поле для фильтрации» указываем столбец, в котором вы записываете цены. В пункте «Действие» выбираем «больше равно», затем появится поле «Значение», сюда записываем цифру 200.

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

Обратите внимание, что есть возможность одновременно подключать сразу несколько фильтров по разным значениям, сочетая их между собой. При этом можно задействовать два варианта условий. «И» – если вам нужно, чтобы выполнялось условие всех фильтров одновременно; «ИЛИ» – когда достаточно срабатывания лишь одного из фильтров.

Так, в первом случае можно настроить передачу уведомлений, когда заказ больше или равен 200 долларов И получен с сайта. Во втором же случае достаточно, чтобы выполнялось то ИЛИ другое условие.

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

Настраиваем Telegram

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

Выбираем его в списке и добавляем нужное нам действие действие — «Отправить сообщение».

Теперь подключаем аккаунт «Телеграмм». Для этого необходимо найти в «Телеграмме» бота ApiX-Drive: @apix_drive_bot. Отправьте ему сообщение, в которое скопируйте строку подключения, ее вам на этом этапе уже сгенерировала система. Бот отреагирует на это вот таким сообщением:

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

Системные данные — это информация о том, когда произошло событие. Если вам важно, чтобы менеджер получил время и дату, когда заказ был добавлен в таблицу — добавляем эти значения. Лучше в столбик, так удобнее будет форматировать и просматривать.

Когда закончили с системными, переходим к данным из «Гугл Таблиц». Чтобы менеджеру приходила не только сумма, но и информация о заказе, выбираем нужные колонки, например, добавим еще ФИО клиента, у нас эти данные в колонке В.

Включаем интеграцию

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

Добавим в «Гугл Таблицу» новый заказ в графу 7, с которой мы настроили прием уведомлений:

И вот в «Телеграмм» пришло сообщение о новом заказе:

Теперь добавим заказ со значением 200 долларов. И для чистоты эксперимента добавляем еще заказ с меньшей суммой, например, 150 долларов:

Уведомление о заказе на 200 пришло пришло, а вот на 150 – нет. Все работает!

Заключение

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

У ApiX-Drive очень широкие возможности, можно выстраивать целые структуры, позволяющие автоматизировать практически что угодно. Например, зачем вносить данные о новых заказах в Google Sheets вручную, если можно сделать так, чтобы они автоматически погружались с сайта магазина. Получается уже более интересная схема – сначала данные автоматически приходят в таблицы, а затем уже отправляются в мессенджеры менеджерам.

И подобных идей можно придумать множество – все зависит от потребностей вашего бизнеса! Например, почему бы не настроить отправку уведомлений из Trello в Telegram?

Читайте также о других интеграциях ApiX-Drive

• Как я настроила получение уведомлений из «Трелло» в Telegram
• Передаём лиды из Facebook в Telegram, Google Sheets и вообще куда угодно
• Интеграция Google-таблиц и Viber: настраиваем автоматическую передачу данных
• Как настроить передачу обращений из ManyChat в Google Sheets
• Интеграция Google Sheets и amoCRM: как настроить автоматическую передачу данных

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

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

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

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

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

Начнем с создания контейнера и написания скрипта в нем. Создаем новый 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.

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

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

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

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

Далее речь пойдет о создании первого бота и написании скрипта в Apps Script. Поехали!

Создание телеграм-бота

Откуда вообще берутся боты в телеграме? Опытному пользователю можно пропустить это пункт, ну а новичкам настало время познакомится с @BOTFATHER

Находим отца всех ботов в поиске телеги и видим, что этот бот вообще из себя представляет.

После старта этого бота мы увидим список команд.

Здесь нам нужна команда /newbot, чтобы создать нашего первого бота.

Порядок действий незамысловатый: создаем бота помандой /newbot, даем ему вменяемое имя и задать ему username с обязательным окончанием bot. В результате получаем API токен нашего бота, который и будем дальше использовать.

Можете проверить работоспособность созданного бота перейдя по следующему URL:

https://api.telegram.org/bot2011183802:AAEW7ZNRVvlr1TG1N0DNkRB9G4FmvkBUUUU/getMe

выделенное жирным замените на апи своего бота

Запрос вернет примерно это:

Подключение гугл-таблицы

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

Нам нужен пункт меню Инструменты/Tools -> Редактор скриптов/ Script editor.

Должно получиться вот это:

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

Начнем с объявления глобальных переменных. У нас их будет только две — API бота и App_link.

const API = "2011183802:AAEW7ZNRVvlr1TG1N0DNkRB9G4FmvkBUUUU"; //в кавычки впишите свой апи const App_link = ""; //значение переменной пока оставляем пустым

Напомню, что АПИ бота мы получили в диалоге с botfather.

Далее напишем функцию send, которая отправляет сообщение в чат с нашим ботом.

function send (msg, chat_id) < let payload = < 'method': 'sendMessage', 'chat_id': String(chat_id), 'text': msg, 'parse_mode': 'HTML' >let data = < 'method': 'post', 'payload': payload >UrlFetchApp.fetch('https://api.telegram.org/bot' + API + '/', data); > 

Функция send() отправляет запрос для общения со строними приложениями, в нашем случае — с телегой. Подробнее в документации Class UrlFetchApp.

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

Далее нужно предусмотреть механизм запуска функции send() . Осуществим это из тела другой функции doPost():

function doPost(e) < let update = JSON.parse(e.postData.contents); //нам нужен только тип "сообщение" if (update.hasOwnProperty('message')) < let msg = update.message; let chat_id = msg.chat.id; let text = msg.text; let user = msg.from.username; if (text == "/hello") < send("Hello World", chat_id) >> > 

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

Здесь мы сравниваем текст полученного ботом сообщения с текстом «/hello» и вызываем функцию send() при совпадении. Это все действия нашего бота.

Сначала сохраним скрипт и задеплоим проект. Кликаем на кнопку Deploy -> New deployment.

В результате откроется окно New deployment, где нужно кликнуть по шестеренке и выбрать Web app.

В поле Who has access выбираем Anyone, что позволит другим пользователям вносить правки в проект и деплоить без вашего участия.

И кликаем Deploy.

При первом запуске приложение попросит авторизации. Проделываем это упражнение.

Здесь нам нужен URL в самом конце. Копируем его по кнопке Copy и вставляем в качестве значения глобальной переменной App_link, значение для которой мы оставили изначально пустым.

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

Добавим следующий код к нашему скрипту:

function api_connector () < UrlFetchApp.fetch("https://api.telegram.org/bot"+API+"/setWebHook?url full-width ">

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

Сообщение «/hello» успешно распознано, на что наш бот ответил «Hello world».

Скрипт целиком ниже:

• telegrambot
• google spreadsheets
• telegram
• javascript

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

Тема Телеграм-ботов только ширится, связка «Телеграм-бот и таблица Гугл» тоже. Если вы разрабатываете дополнения для 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’ как бы говорит триггеру, что данные на листе изменены не программным путём, а пользователем.

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

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

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

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