Google spreadsheet as database for telegram bot

In this post, we’ll take a closer look at how to directly use Google Sheets as a database.

Let’s try to write a bot from scratch that takes quiz questions with answer options from a table and writes the answers back.

Below is a screenshot from the chat with the bot.

Disclaimer: if you are here for the first time, please read the first post, where the scripts in google sheets (tyts) are analyzed in more detail.

Let’s start by creating a container and writing a script in it. Create a new Spreadsheet.

As the values ​​to be taken, I will indicate 4 questions on the Questions sheet.

And answer options for each question on the Answers sheet. Data from two tables are linked by ID – the first column.

I will add answers from users to the Users table, but more on that later.

Let’s move on to the script. I will specify the following values ​​as global variables:

const doc = SpreadsheetApp.getActive();
const questionsSheet = doc.getSheetByName("Questions");
const answersSheet = doc.getSheetByName("Answers");
const usersSheet = doc.getSheetByName("Users");

Since we deploy the script in the same document (container), it is enough for us to use the getActive () method. If you are referring to another Spreadsheet, then you can refer to it with the .openByUrl() or .openById() methods.

I write each sheet into separate variables so that it is convenient to access the sheets from anywhere in the script.

Now let’s try to take the values ​​from the table and display them in the logger. The following function takes values ​​from a table and returns them as an array.

function sendQuestions() {
  const questionsArr = questionsSheet.getRange(1,1,questionsSheet.getLastRow(), questionsSheet.getLastColumn()).getValues()

  Logger.log(questionsArr)
}

You can run a function from the menu by selecting the function name and clicking Run

The result of the function execution will be an array in the logger.

Let’s parse the string in the function in parts.

const questionsArr = questionsSheet.getRange(1,1,questionsSheet.getLastRow(), questionsSheet.getLastColumn()).getValues()

Here we are declaring a variable to store the returned array into. Next, specify the sheet from which we will take the values questionsSheet. For a sheet, select the .getRange() method and specify the cells that the function should access.

I translate what is written in the brackets of the method getRange in understandable language:

getRange(номер строки начала диапазона
         , номер столбца начала диапазона
         , номер строки конца диапазона
         , номер столбца конца диапазона)

Using the .getLastRow() and .getLastColumn() sheet methods, we get the number of the last row and column, which will be equal to 4 and 2, respectively. In this case, the methods return the last column and row in which the value is indicated (the cell is not empty) or there is validation.

At the same time, you can specify in brackets questionsSheet.getRange(“A1:B4”).

We return to the function and to the .getValues() range method. Here, pay attention to the fact that there is also a .getValue () method, which should be used if we are accessing a single cell, for example questionsSheet.getRange(3,4) or questionsSheet.getRange(“D3”).

getValues() returns a two-dimensional array, while getValue() returns a value.

*Try accessing different ranges using different syntax and observe the returned values. After a few attempts, referring to ranges will become intuitive.

So, the function returned a two-dimensional array, so we can continue working with the array and its methods.

As planned, the bot sends all the questions at the same time as the buttons. First, let’s try to just send questions without answer options.

Let’s turn to the send() function.

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);
}

We pass the text of the message sent by the bot and the chat ID to which this message will be sent to the function. Inside the function, we declare the payload object and specify the parameters to be passed. I use only the parameters necessary for the task (method, chat_id, text, parse_mode), but there may be more (link on api telegram for sendMessage method).

Pass the payload to the data variable (passed parameters for the “post” api method) and specify the method itself post.

In the UrlFetchApp line, we access the cart API by reference (any other available API can be accessed in the same way). In the same line, we pass the value of the variable token and data. About token a little later.

Let’s return to the sendQuestions() function and call send() from it for each element of the questionsArr array.

To access each element, you can use for or while loops. But I prefer to use array methods and arrow functions that fit on one line.

Thus, instead of constructing

for (let i=0; i<questionsArr.length; i++) {
    send(questionsArr[i][1],chat_id)
}

I can write

questionsArr.forEach(e => send(e[1],chat_id))

So, as the text of the message, I take the element from the nested array at the position [1] (question id is indicated at position 0).

We are missing only the chat id, which I will specify as an argument to the sendQuestions() function and get the following:

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));
}

Finally, we will call the sendQuestions(chat_id) function when the user starts the bot with the /start command. Let’s write this logic.

First, I’ll add a function that parses the json package returned from the telegram.

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
    };
  }
}

The result of the function is an msgData object with the keys id, chat_id, user_name, text, is_msg. You can define the returned keys yourself, for simplicity, I have indicated only those that are needed for the task.

Here I will pass the value of the chat_id key to the function for sending questions.

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,
      first_name : update.message.from.first_name,
      text       : update.message.text,
      date       : update.message.date/86400+25569.125,
      is_msg     : true
    };
  }
  sendQuestions(msgData.chat_id);
}

How to create a bot and get a token has already been described here. We take this token and write it to a global variable token. Next, we deploy the application (see the link above) and write the URL of the web application to a variable appLink.

And finally the api_connector() function to set up the web hook.

function api_connector() {
  const appLink = "Ваш URL";
  UrlFetchApp.fetch("https://api.telegram.org/bot"+token+"/setWebHook?url="+ appLink); 
}

In this function, we again send a request to the server at https://api.telegram.org and additionally specify the method and/or parameters. In this case, the setWebhook method and the url parameter are used, where we pass the value of the variable appLink.

Run this function on the button Run.

After successfully installing the webhook, we launch the bot in the telegram. We get questions in response.

To be continued…

Here I will probably interrupt, because. I do not want to write a training manual for the lab. Try to solve this little problem, play around with methods for specifying ranges and methods for arrays.

As always, feedback is welcome, let me know if I write too complicated \ illegible \ without details ..

You can address all questions on implementation directly to my telegram.

I will try to prepare a continuation in a reasonable time and show how arrays with answers to questions can be turned into buttons. It will be interesting! 😉