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

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

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

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

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

}

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

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

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

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

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