Connecting telegrams bot to google tables

Hello everyone, Habravites!

This is my first post on this site, the purpose of which is to dive into the community for feedback and useful remarks regarding this article. I will also be glad if this article becomes useful for those who begin their way in writing human bots πŸ™‚

Over the past year, I learned about an interesting way to automate business processes, which is achieved by a combination of google tables and telegram. Tasks such as financial accounting, time management, forwarding events, messages, notifications, etc. easily solved with the free google and cart toolkit.

Next, we will talk about creating the first bot and writing a script in Apps Script. Go!

Creation of a telegram bot

Where do telegram bots come from? An experienced user can skip this point, but for beginners, it’s time to get acquainted with @BOTFATHER

We find the father of all bots in the search for a cart and see what this bot is all about.

After starting this bot, we will see a list of commands.

Here we need the / newbot command to create our first bot.

The procedure is straightforward: we create a bot with the pomand / newbot, give it a sane name and give it a username with the obligatory ending bot. As a result, we get the API token of our bot, which we will continue to use.

You can check the functionality of the created bot by going to the following URL:

https://api.telegram.org/bot2011183802: AAEW7ZNRVvlr1TG1N0DNkRB9G4FmvkBUUUU/ getMe

replace in bold with the api of your bot

The request will return something like this:

{"ok":true,"result":{"id":2011183802,"is_bot":true,"first_name":"Demo for Habr","username":"DemoForHabr_bot","can_join_groups":true,"can_read_all_group_messages":false,"supports_inline_queries":false}}

Connecting a google table

Go to google signs and create a new dock.

We need a menu item Tools / Tools -> Script editor.

You should get this:

Let’s start the script and write our own in JavaScript. Additionally, Google tools (classes, methods, etc.) can be used in the script, which we will consider further.

Let’s start by declaring global variables. We will have only two of them – the bot API and App_link.

const API = "2011183802:AAEW7ZNRVvlr1TG1N0DNkRB9G4FmvkBUUUU"; 
//Π² ΠΊΠ°Π²Ρ‹Ρ‡ΠΊΠΈ Π²ΠΏΠΈΡˆΠΈΡ‚Π΅ свой Π°ΠΏΠΈ
const App_link = "";
//Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ ΠΏΠ΅Ρ€Π΅ΠΌΠ΅Π½Π½ΠΎΠΉ ΠΏΠΎΠΊΠ° оставляСм пустым

Let me remind you that we got the bot API in the dialogue with botfather.

Next, we will write a send function that will send a message to the chat with our bot.

function send (msg, chat_id) {
  let payload = {
  'method': 'sendMessage',
  'chat_id': String(chat_id),
  'text': msg,
  'parse_mode': 'HTML'
  }
  let data = {
    'method': 'post',
    'payload': payload
  }
    UrlFetchApp.fetch('https://api.telegram.org/bot' + API + '/', data);
}

Function send() sends a request to communicate with strony applications, in our case – with a cart. Read more in the documentation Class UrlFetchApp

The function arguments are the text of the message to be sent and the chat ID to which this message is sent.

Next, you need to provide a mechanism for launching the function send()… Let’s do this from the body of another function doPost():

function doPost(e) {
  let update = JSON.parse(e.postData.contents);
  //Π½Π°ΠΌ Π½ΡƒΠΆΠ΅Π½ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Ρ‚ΠΈΠΏ "сообщСниС"
  if (update.hasOwnProperty('message')) {
    let msg = update.message;
    let chat_id = msg.chat.id;
    let text = msg.text;
    let user = msg.from.username;
    if (text == "/hello") {
      send("Hello World", chat_id)
    }
  }
}

This is a standard function when working with telegram bots, into which responses from the bot are sewn when sending a message to the chat with it.

Here we compare the text of the message received by the bot with the text “/ hello” and call the function send()if there is a match. These are all actions of our bot.

First, let’s save the script and deploy the project. Click on the Deploy -> New deployment button.

As a result, the New deployment window will open, where you need to click on the gear and select Web app.

In the Who has access field, select Anyone, which will allow other users to make edits to the project and deploy without your participation.

And click Deploy.

At the first launch, the application will ask for authorization. We are doing this exercise.

We get the following:

Here we need the URL at the very end. Copy it by button Copy and insert as the value of the global variable App_link, the value for which we left initially empty.

Let’s create a function for installing a webhook to receive updates from our bot.

Let’s add the following code to our script:

function api_connector () {
  UrlFetchApp.fetch("https://api.telegram.org/bot"+API+"/setWebHook?url="+App_link); 
}

The function accepts both the API and App_link global variables, so make sure you have them and are set correctly.

Save the code again and run only the api_connector function:

If the function was executed without errors, we go to check the work of our bot directly in the cart.

The message “/ hello” was successfully recognized, to which our bot replied “Hello world”.


The whole script is below:

const API = "2011183802:AAEW7ZNRVvlr1TG1N0DNkRB9G4FmvkBUUUU";
//Π² ΠΊΠ°Π²Ρ‹Ρ‡ΠΊΠΈ Π²ΠΏΠΈΡˆΠΈΡ‚Π΅ свой Π°ΠΏΠΈ 
const App_link = "https://script.google.com/macros/s/AKfycbyS0k0r1VF0T1O1y8b_oElTXWzWNJKUiQDXS2HWdZT9q-ODieKgLoNXGQTtRoi6a8fo/exec";
//послС ΠΊΠ°ΠΆΠ΄ΠΎΠ³ΠΎ дСплоя обновляСм Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ ΠΏΠ΅Ρ€Π΅ΠΌΠ΅Π½Π½ΠΎΠΉ
 
function send (msg, chat_id) {
  let payload = {
  'method': 'sendMessage',
  'chat_id': String(chat_id),
  'text': msg,
  'parse_mode': 'HTML'
  }
  let data = {
    "method": "post",
    "payload": payload
  }
    UrlFetchApp.fetch('https://api.telegram.org/bot' + API + '/', data);
}

function doPost(e) {
  let update = JSON.parse(e.postData.contents);
  //Π½Π°ΠΌ Π½ΡƒΠΆΠ΅Π½ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Ρ‚ΠΈΠΏ "сообщСниС"
  if (update.hasOwnProperty('message')) {
    let msg = update.message;
    let chat_id = msg.chat.id;
    let text = msg.text;
    let user = msg.from.username;
    if (text == "/hello") {
      send("Hello World", chat_id)
    }
  }
}

function api_connector () {
  UrlFetchApp.fetch("https://api.telegram.org/bot"+API+"/setWebHook?url="+App_link); 
}

Conclusion

We have created a simple bot for demonstrating the work of Google scripts in conjunction with a telegram.

If the topic is interesting, I will gladly prepare material on writing more complex functions and / or more useful from the point of view of business and automation.

I would be glad to receive feedback.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *