Google spreadsheet as a database for telegram bot (continued)

Continuation of the article about using Google Spreadsheets as a database.

Brief preface: in the previous post, I gave an example of how to take text from table cells and send this text as separate messages to a telegram chat with a bot.

As promised, in a new post I will tell you how to take data from cells and turn them into buttons by placing them under messages that we have successfully sent before.

Cells gt to buttons mg – transformation

Let’s return to the structure of the table in which the buttons are located (to the values that will be used for the text of the buttons).

This is the Answers table with three columns:

the text id of the question to which the answer belongs
button text
correct answer marker

Let’s supplement the existing script by processing data from this table and forming an object of type InlineKeyboardMarkup according to Telegram bot api documentation.

Let’s go back to the sendQuestions(chat_id) function from the last post

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

And add a line to get all the values from the Answers tab.

const answersArr = answersSheet.getDataRange().getValues();

As noted in the comments to the last article, there is a more understandable way to get all the data from the sheet, namely the .getDataRange() method.

I propose a line

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

also change to

const questionsArr = questionsSheet.getDataRange().getValues();

As a result, we get

function sendQuestions(chat_id) {
  const questionsArr = questionsSheet.getDataRange().getValues();
  const answersArr = answersSheet.getDataRange().getValues();

  Logger.log(questionsArr)
  Logger.log(answersArr)

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

I have commented out the last line in the function so far, since we will interact with the chat later.

We run the function and get two arrays in the logger – with questions and answers.

Let’s start transforming an array with answer options into an object keyboard.

At this stage, I will declare a variable of the same name, in which I will indicate the key inline_keyboard with the value of an array of buttons.

const keyboard = {
      "inline_keyboard": arr
}

Meaning arr is a two-dimensional array. The nested array includes an object describing the characteristics of the button. An example of what it might look like arr

const arr =
  [
    [{"text":"button1", "otherKey1":"value1", "otherKey2":"value2"}],
    [{"text":"button2", "otherKey1":"value1", "otherKey2":"value2"}]
  ]

* If your project has one or more static keyboards, you can set them as variables directly in the code, for example

const keyboard1 =  {
  "inline_keyboard":
    [
      [{"text": "buttonText1", "otherKey1":"value1", "otherKey2":"value2"}],
      [{"text": "buttontext2", "otherKey1":"value1", "otherKey2":"value2"}]
    ]
}

const keyboard2 =  {
  "inline_keyboard":
    [
      [{"text": "buttonText3", "otherKey1":"value1", "otherKey2":"value2"}],
      [{"text": "buttontext4", "otherKey1":"value1", "otherKey2":"value2"}],
      [{"text": "buttontext5", "otherKey1":"value1", "otherKey2":"value2"}]
    ]
}

Key text mandatory and its value is displayed directly in the telegram. Other available keys can be viewed in documentationin this problem I will additionally use only callback_data.

Let’s remember the logic again. For each question, I have several answer options, so for one message I need to generate a list of all the answers available to this question by the id of this question.

Back to processing an array with questions in a row questionsArr.forEach(e => send(e[1],chat_id)). To do this, I will rewrite it a little differently.

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

In the body of the arrow function, we declare our arr for the keyboard object and the object itself keyboard

questionsArr.forEach(e => {
  let arr = answersArr.filter(el => el[0] == e[0])

  const keyboard = {
    "inline_keyboard": arr
  }
  
  //send(e[1],chat_id)
})

In the newly added line of code, I declare an array variable, I write values from the array with answers to it, which I pre-filter on the basis that the question id is from the table Questions equals the question id from the table Answers. I use the array method filter().

Inside the method I refer to an array element answersArr using the notation eland compare the value el at position 0 with array element questionsArr also at position 0.

I will display the filtering result in the logger

questionsArr.forEach(e => {
  let arr = answersArr.filter(el => el[0] == e[0])
  Logger.log(arr)

  const keyboard = {
    "inline_keyboard": arr
  }
  
  //send(e[1],chat_id)
})

*The same result can be obtained with nested loops

for (let i=0; i<questionsArr.length; i++) {
    let arr = new Array();
    for (let j=0; j<answersArr.length; j++) {
      if (questionsArr[i][0] == answersArr[j][0]) arr.push(answersArr[j]) 
    }
    Logger.log(arr)
  }

Starting from the third line in the logger, filtered values are displayed for each of the question ids – 1, 2, 3 and 4.

Now I need to modify each such array, for which I will use the .map().

questionsArr.forEach(e => {
  let arr = answersArr.filter(el => el[0] == e[0])
  arr = arr.map(el => [{"text":el[1], "callback_data":el[1]}])
  Logger.log(arr)

  const keyboard = {
    "inline_keyboard": arr
  }
  
  //send(e[1],chat_id)
})

Inside a Method map() referencing an array element arr through eltake the value at position 1 and assign it to the keys text And callback_data. That is, the element [1.0, Джейк пес, ] will turn into [{“text”:”Джейк пес”, “callback_data”:”Джейк пес”}]

And an example of how it would be in cycles without applying methods (well, except for one)

  for (let i=0; i<questionsArr.length; i++) {
    let arr = new Array();
    for (let j=0; j<answersArr.length; j++) {
      if (questionsArr[i][0] == answersArr[j][0]) {
        const val = answersArr[j][1]
        const button = {"text":val, "callback_data":val}
        arr.push([button]) 
      }
    }
    Logger.log(arr)
  }

*The .push() method inserts the element specified in brackets at the end of the array

And the output in the logger of the modified array arr

All function sendQuestions() below

function sendQuestions() {
  const questionsArr = questionsSheet.getDataRange().getValues();
  const answersArr = answersSheet.getDataRange().getValues();

  Logger.log(questionsArr)
  Logger.log(answersArr)

  //Альтернатива 1
  /*
  for (let i=0; i<questionsArr.length; i++) {
    let arr = new Array();
    for (let j=0; j<answersArr.length; j++) {
      if (questionsArr[i][0] == answersArr[j][0]) {
        const val = answersArr[j][1]
        const button = {"text":val, "callback_data":val}
        arr.push([button]) 
      }
    }
    Logger.log(arr)
  }
  */
  //конец Альтернативы 1
  //Альтернатива 2  
  questionsArr.forEach(e => {
    let arr = answersArr.filter(el => el[0] == e[0])
    arr = arr.map(el => [{"text":el[1], "callback_data":el[1]}])
    Logger.log(arr)
  //конец Альтернативы 2
    const keyboard = {
      "inline_keyboard": arr
    }
    //send(e[1],chat_id)
  }) 
}

At this point, we’re ready to send the keyboard with a message, but we need the function that this one does. Let’s make some changes to the existing function send().

We have a new passed parameter, the name of the key of this parameter reply_markupand the value keyboard:

'reply_markup' : JSON.stringify(keyboard)

Don’t forget to indicate keyboard in function parameters. Thus, we get

function send(msg, chat_id, keyboard) {
  const payload = {
    'method': 'sendMessage',
    'chat_id': String(chat_id),
    'text': msg,
    'parse_mode': 'HTML',
    'reply_markup' : JSON.stringify(keyboard)
  }
  const data = {
    'method': 'post',
    'payload': payload,
    'muteHttpExceptions': true
  }
    UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/', data);
}

We save the project and deploy (How to do it?). Don’t forget to run the function api_connector() with new meaning appLink

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

We go to the chat, send any text and … we see the result!

The bot responded by submitting all questions with option buttons.

Below is all the code we currently have

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, keyboard) {
  const payload = {
    'method': 'sendMessage',
    'chat_id': String(chat_id),
    'text': msg,
    'parse_mode': 'HTML',
    'reply_markup' : JSON.stringify(keyboard)
  }
  const data = {
    'method': 'post',
    'payload': payload,
    'muteHttpExceptions': true
  }
    UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/', data);
}

function sendQuestions(chat_id) {
  const questionsArr = questionsSheet.getDataRange().getValues();
  const answersArr = answersSheet.getDataRange().getValues();

  Logger.log(questionsArr)
  Logger.log(answersArr)

  questionsArr.forEach(e => {
    let arr = answersArr.filter(el => el[0] == e[0])
    arr = arr.map(el => [{"text":el[1], "callback_data":el[1]}])
    Logger.log(arr)

    const keyboard = {
      "inline_keyboard": arr
    }

    send(e[1], chat_id, keyboard)
  })
}