В этом посте рассмотрим в деталях, как непосредственно использовать гугл таблицы в качестве базы данных.
Попробуем с нуля написать бота, который забирает вопросы квиза с вариантами ответов из таблицы и записывает ответы назад.
Ниже представлен скрин из чата с ботом.
Дисклеймер: если вы здесь в первый раз, пожалуйста ознакомьтесь с первым постом, где более детально разобраны скрипты в гугл таблицах (тыц).
Начнем с создания контейнера и написания скрипта в нем. Создаем новый 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() {
const questionsArr = questionsSheet.getRange(1,1,questionsSheet.getLastRow(), questionsSheet.getLastColumn()).getValues()
Logger.log(questionsArr)
}Запустить функцию можно из меню, выбрав имя функции и кликнув 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.length; i++) {
send(questionsArr[i][1],chat_id)
}я могу написать
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() для установки веб хука.
function api_connector() {
const appLink = "Ваш URL";
UrlFetchApp.fetch("https://api.telegram.org/bot"+token+"/setWebHook?url="+ appLink);
}В этой функции мы опять же отправляем запрос на сервер по адресу https://api.telegram.org и дополнительно указываем метод и/или параметры. В данном случае используется метод setWebhook и параметр url, куда мы передаем значение переменной appLink.
Запускаем эту функцию по кнопке Run.
После успешной установки вебхука запускаем бота в телеграме. В ответ получаем вопросы
Весь код целиком:
const doc = SpreadsheetApp.getActive();
const questionsSheet = doc.getSheetByName("Questions");
const answersSheet = doc.getSheetByName("Answers");
const usersSheet = doc.getSheetByName("Users");
const token = "Ваш токен"
function api_connector () {
const App_link = "Ваш URL";
UrlFetchApp.fetch("https://api.telegram.org/bot"+token+"/setWebHook?url="+App_link);
}
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);
}
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);
}
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))
}Продолжение следует...
Здесь я пожалуй прервусь, т.к. не хочу писать методичку для лабы. Попробуйте решить эту маленькую задачку, поиграйте с методами для указания диапазонов и методами массивов.
Как всегда, рада обратной связи, дайте знать, если пишу слишком сложно\ неразборчиво\ без деталей..
Все вопросы по реализации можете адресовать напрямую в мой телеграм.
Продолжение постараюсь подготовить в разумные сроки и показать, как массивы с ответами на вопросы превратить в кнопки. Будет интересно! ?