"Database" in Google Sheets for Telegram Bot

Almost all bots use some kind of data storage. Databases are most often used, but sometimes their use can be excessive, especially if you do not need ACID transactions and want to change data manually in the simplest interface possible.

You can use Google Sheets to store information.

Today we will analyze an example of a telegram bot for conducting tests, where questions and answers are stored in different tabs of one Google spreadsheet. Why one? Again, to facilitate access for potential users.

Google Spreadsheet Structure

Before we start working with a Google spreadsheet in our bot, we need to decide what the structure of our document will look like so that we can write a parser that we will use.

In our example, we will work with tests in which there are 4 answer options for each question (1 correct, 3 incorrect).
So we need to store the following information about the question:

1. Непосредственно текст вопроса
2. Четыре варианта ответа на вопрос
3. Указание, какой из ответов является правильным

It was decided to use the following structure:

In the column A — the text of the question is located
In the column B – the correct answer
In columns C,D,E – incorrect answers

This structure will allow us, on the one hand, to clearly indicate which answer is correct, and on the other hand, we will avoid duplicating answers.
In addition, you can use Google Sheets to implement data validation checks. For example, to ensure that there are no repetitions among the incorrect answers, or that none of the incorrect answers match the correct answer.

And there can be as many of these sheets, with tables of this format, each page is its own separate test. You can divide them, as we will see later, by subjects (Chemistry/History), or you can even do several tests within one subject.

Since we want to use our table to store answers, let's look at the format of this page as well:

In our table we will store the user ID, question, user answer, correct answer and timestamp.
Why? After all, this is clearly duplication of information; the text of the question can be used to obtain an answer from another sheet? The point is that if the sheet is deleted or corrected, then the data in the answer table will also be corrected, which can change, for example, the final score.

It is important to say that the bot will only be used to collect responses, all checks can be implemented by Google.

Interacting with a Table from Python

Our Python program will consist of two parts: the first is objects and methods for working with Google Sheets, and the second is the bot itself. Of course, we will use the first part in our bot, but first, let's figure out how to work with Google Sheets from Python.

There are many libraries available for working with the Table API, we will use gspread. You can read more about it on the website with official documentation.

Before we start working, we need to create an account and get keys for interaction.
Here detailed instructions
on how to do this. As a result, you will have .json file of the following type:

{
  "type": "service_account",
  "project_id": "pythonapi-433820",
  "private_key_id": "7080a92d01c73eaf214379bb171093",
  "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
  "client_email": "api-123@pythonapi-433820.iam.gserviceaccount.com",
  "client_id": "473 … hd.apps.googleusercontent.com"
}

Now you can connect.

First, let's create a file config.pyin it we will store configuration information: the path to our .json — file, link to the file and token for the bot:

CREDENTIALS_FILENAME = "credentials.json"
QUESTIONS_SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/15dL4-HSC7VjjnQHnppJMQZ"
BOT_TOKEN = "6941340264:BCGscBGpPZIuI-1sOyIgv-rzPgkyrhNt12c"

Now let's move directly to the code of our parser. First, let's import all the libraries we need:

import gspread
from config import CREDENTIALS_FILENAME, QUESTIONS_SPREADSHEET_URL
from random import shuffle
from datetime import datetime

And then we'll create a class that will represent our table in Python:

class Quizzer:
    def __init__(self, question_spreadsheet_url=QUESTIONS_SPREADSHEET_URL):
        self.account = gspread.service_account(filename=CREDENTIALS_FILENAME)
        self.spreadsheet = self.account.open_by_url(question_spreadsheet_url)
        self.topics = {
            elem.title: elem.id for elem in self.spreadsheet.worksheets()
        }
        self.answers = self.spreadsheet.get_worksheet_by_id(self.topics.get("Results"))

First, we create the necessary class fields:

self.account — the account that will be used to access the table. Pulled from our file
self.url — the path to the file we will work with
self.spreadsheet — the file itself (already open)
self.topics — dictionary of pairs “Sheet Header”: “Sheet ID” — all sheets in our file
self.answers – an open sheet with the heading “Results” where we will write down the answers

Now we implement the necessary methods:

Getting a list of tests. The list of tests is all the pages of the file except the page with the heading “Results”
```
def get_topics(self):
    return {key: value for key, value in self.topics.items() if key != "Results"}
```

Getting a list of questions in the test. We will receive raw data from the page, you will need to convert it into a convenient format

def get_question_by_topic(self, topic_name):
    if topic_name in self.topics:
        worksheet = self.spreadsheet.get_worksheet_by_id(self.topics.get(topic_name))
        return worksheet.get_all_records()
    return []

def questions_and_answers(self, topic_name):
    questions = self.get_question_by_topic(topic_name)
    result = []
    for elem in questions:
        answers = [elem["correct_answer"], elem["wrong_answer_1"], elem["wrong_answer_2"], elem["wrong_answer_3"]]
        shuffle(answers)
        new_format = {
            "question": elem["question"],
            "correct_answer": elem["correct_answer"],
            "answers": answers
        }
        result.append(new_format)
    return result

So first in the method get_question_by_topic we got the raw data using the method get_all_recordsand then in the method questions_and_answers We collected a list of correct and incorrect answers, shuffled it, and then returned the list of questions in the format in which we intended to use it.

And in addition to receiving information, it is also necessary to record the answers in the table. So we will implement another method for recording:

    def write_answer_to_result_cell(self, user_id, question, answer, correct_answer):
        index = len(list(filter(None, self.answers.col_values(1)))) + 1
        self.answers.update(f"A{index}:E{index}", [[
            user_id, question, answer, correct_answer, f"{datetime.now()}"
        ]])

Basically, this is where the functionality for interacting with Google Sheets is implemented. And we can move on to writing the bot itself.

Telegram bot

To implement the telegram bot, we will use an asynchronous engine – aiogram. You can read more about implementing bots using this library in in this textbook.

First, let's import all the necessary libraries:

import asyncio

from aiogram import Bot, Dispatcher, F, Router, types
from aiogram.filters.command import Command
from aiogram.fsm.context import FSMContext
from aiogram.fsm.state import State, StatesGroup

from config import BOT_TOKEN
from questionExtractor import Quizzer

asyncio — library for working with an asynchronous approach
aiogram — directly the library for creating bots, we extract the basic concepts and necessary functionality for
working with the State Machine
config – our configuration file, BOT_TOKEN — a token for interacting with the bot. Can be obtained via 'BotFather'
questionExtractor — our module for interaction with Google Sheets

Then we create the necessary basic objects:

router = Router() – a router for distributing messages to handlers
bot = Bot(token=BOT_TOKEN) — the bot itself
dp = Dispatcher() – message manager
quizzer = Quizzer() — an object for interacting with the table

and then we create a class that describes all possible states of our bot:

class CurrentQuiz(StatesGroup):
    start = State()
    choosing_test = State()
    question = State()

start — the bot has just been launched (or restarted) — you need to select a test
choosing_test – the test selection process – sending the first question
question – sending questions and receiving answers

Before directly processing incoming messages, we write two auxiliary functions.

One will be used to create a custom keyboard from a list of possible answers:

def create_keyboard(options):
    """Функция для создания клавиатуры из списка возможных вариантов"""
    return types.ReplyKeyboardMarkup(keyboard=[[types.KeyboardButton(text=f"{elem}")] for elem in options])

And the second one is for forming a message with a question and a keyboard. It is important to say that we store all our current state in the state. The state is a dictionary where we can store some information, and this dictionary can be passed between handlers.

In the field current_question – we store the question we want to send, and in the field choosing_test — list of questions for the current test.

async def ask_question(message: types.Message, state: FSMContext):
    """Функция для отправки вопроса с формированием клавиатуры ответов"""
    data = await state.get_data()
    question = data["current_question"]
    keyboard = create_keyboard(question["answers"])
    await message.answer(question["question"], reply_markup=keyboard)
    await state.update_data(current_question=question)
    await state.update_data(choosing_test=data["choosing_test"][1:])

In our function we get the current question from the state, send it to the user (by creating a keyboard with the answer), and then update the data in the state.

Now it's time to write custom message handlers.

# Обработчик на команду старт. Стейт CurrentQuiz.start
@router.message(CurrentQuiz.start)
@router.message(Command("start"))
async def cmd_start(message: types.Message, state: FSMContext):
    keyboard = create_keyboard(quizzer.get_topics().keys())
    await message.answer("Привет, я бот Quizzer. Вот доступные темы для тестов. Выбери любую", reply_markup=keyboard)
    await state.set_state(CurrentQuiz.choosing_test)

This is the handler for the first command that the user sends when launching a new bot — /start. In it, we get a list of topics from our table, and then offer the user to select one of them.

# Обработчик стейта выбора теста
@router.message(CurrentQuiz.choosing_test, F.text.in_(quizzer.get_topics().keys()))
async def start_quizz(message: types.Message, state: FSMContext):
    chosen_test_title = message.text
    choosing_test = quizzer.questions_and_answers(message.text)

    await state.update_data(
        choosing_test=choosing_test,
        current_question=choosing_test[0]
    )

    await message.answer(f"Выбрана тема: {chosen_test_title}")
    await state.set_state(CurrentQuiz.question)
    await ask_question(message, state)

After the user selects a test, we save the questions from the selected test to the state:

    await state.update_data(
    choosing_test=choosing_test,
    current_question=choosing_test[0]
)

And we ask the user the first question from it: await ask_question(message, state)

The most important handler is the handler for receiving a response from the user:

@router.message(CurrentQuiz.question)
async def getting_answer(message: types.Message, state: FSMContext):
    data = await state.get_data()
    quizzer.write_answer_to_result_cell(
        message.from_user.username,
        data["current_question"]["question"],
        message.text,
        f'{data["current_question"]["correct_answer"]}'
    )

    remaining_questions = data["choosing_test"]

    if remaining_questions:
        await state.update_data(choosing_test=remaining_questions, current_question=remaining_questions[0])
        await ask_question(message, state)
    else:
        await state.clear()
        await message.answer("Все вопросы закончились", reply_markup=create_keyboard(["Выбрать новый квиз"]))
        await state.set_state(CurrentQuiz.start)

First, we save the user's response to a table:

    quizzer.write_answer_to_result_cell(
        message.from_user.username,
        data["current_question"]["question"],
        message.text,
        f'{data["current_question"]["correct_answer"]}'
    )

And then we check if there are any unasked questions left in the test. If there are, we ask the next one:

        await state.update_data(choosing_test=remaining_questions, current_question=remaining_questions[0])
        await ask_question(message, state)

If not, we clear the state memory and return to the original state – launching the bot:

        await state.clear()
        await message.answer("Все вопросы закончились", reply_markup=create_keyboard(["Выбрать новый квиз"]))
        await state.set_state(CurrentQuiz.start)

Now all that remains is to launch our bot:

# Запуск процесса поллинга новых апдейтов
async def main():
    dp.include_router(router)
    await dp.start_polling(bot)

if __name__ == "__main__":
    asyncio.run(main())

Our bot started working on a local computer, but the moment we turn it off, our bot will turn off. So that the bot's work does not depend on the state of your computer, it is customary to upload (deploy) them to the cloud. This is what we will do now.

Deploy

We will use as a cloud for deployment Amvera.

The service will allow you to deploy by simply dragging and dropping files in the interface (or via the git push amvera master command in the IDE) and will provide a starting balance for the first weeks of free use.

Let's create a project.

We download the necessary project files, including configuration files, as well as a file with dependencies. It can be generated automatically.
Using for example the command: pip freeze > requirements.txt.
But it's better to write it manually, pip freeze generates a lot of extra dependencies that slow down the build.

Then we configure: we specify the python version, the dependency management mechanism and the main program file:

Let's start the build and enjoy the work of our bot.

If you want to use Git for deployment, I recommend reading the article.

In our Google Sheet we have questions and answers:

And all interaction is through the bot:

In this tutorial we learned how to use a Google spreadsheet as a “DB” for a telegram bot. The full project code is available in repositories on GitHub