Nadjuscha 6 апр 2023 в 16:59

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

Средний

11 мин

10K

Туториал

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

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

Сначала я бы хотела сделать пол шага назад и показать, в каком виде мы получаем информацию из телеграма, когда мы кликаем по кнопке, отправляем сообщение боту или любое другое действие. Любая активность пользователя возвращается в виде 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, 
      id=3.11157431E8
    }, 
    date=1.680698505E9, 
    chat={
      username=brainwashed_from_rock, 
      first_name=Nadya, 
      type=private, 
      id=3.11157431E8
    }
  }
}

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={
    id=1336410990935140117, 
    chat_instance=-2700514822492676651, 
    message={
      text=Say my name..., 
      chat={
        type=private, 
        username=brainwashed_from_rock, 
        id=3.11157431E8, 
        first_name=Nadya
      }, 
      message_id=121.0, 
      date=1.680698507E9, 
      from={
        is_bot=true, 
        username=Testforpost_Bot, 
        id=2.077040493E9, 
        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, 
      id=3.11157431E8
    }
  }
}

Очевидно, что пакеты различаются и порядок парсинга тоже изменится. Дополним функцию 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)  {
  const vals = [msgData.chat_id, msgData.user_name, msgData.text, msgData.data, msgData.date]
  usersSheet.appendRow(vals)
}

В переменную 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)  {
  const vals = [msgData.chat_id, msgData.user_name, msgData.text[0], msgData.data, msgData.date]
  usersSheet.appendRow(vals)
}

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

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

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

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

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

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) {
  const answersArr = answersSheet.getDataRange().getValues()
}

Воспользуюсь методом массива .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 => [{"text":el[1], "callback_data":el[1]}])

  const keyboard = {
    "inline_keyboard": arr
  }

  return keyboard
}

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

function sendQuestion(msgData, nextState) {
  const questionsArr = questionsSheet.getDataRange().getValues();

  if (nextState <= questionsArr.length) {
    const questionArr = questionsArr.find(e => 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 при этом запишутся значения в условном виде: {username="brainwashed_from_rock", progress=1,....}. Теперь из этого объекта можно достать текущий прогресс:

function getNextState(rowMap) {
  const currentState = rowMap.get("progress");
  const nextState = Number(currentState) + 1;

  return nextState
}

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

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 я прописала исключение и включила туда ту же самую логику, как и для кнопки. На этом все!

Заключение

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

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

UPD: ссылка на код

Теги:

Хабы:

Если эта публикация вас вдохновила и вы хотите поддержать автора — не стесняйтесь нажать на кнопку

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

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

План минимум

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

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

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

Заключение

Публикации

Истории

Работа

Ближайшие события