Connect telegram bot with google apps script

Question

I have setup a bot on telegram bot and connected it with google spreadsheets via apps script by following this tutorial. Here is the code:

var token = ""; // FILL IN YOUR OWN TOKEN
var telegramUrl = "https://api.telegram.org/bot" + token;
var webAppUrl = ""; // FILL IN YOUR GOOGLE WEB APP ADDRESS
var ssId = ""; // FILL IN THE ID OF YOUR SPREADSHEET

function getMe() {
  var url = telegramUrl + "/getMe";
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

function setWebhook() {
  var url = telegramUrl + "/setWebhook?url=" + webAppUrl;
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

function sendText(id,text) {
  var url = telegramUrl + "/sendMessage?chat_id=" + id + "&text=" + text;
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

function doGet(e) {
  return HtmlService.createHtmlOutput("Hi there");
}

function doPost(e) {
  // this is where telegram works
  var data = JSON.parse(e.postData.contents);
  var text = data.message.text;
  var id = data.message.chat.id;
  var name = data.message.chat.first_name + " " + data.message.chat.last_name;
  var answer = "Hi " + name + ", thank you for your comment " + text;
  sendText(id,answer);
  SpreadsheetApp.openById(ssId).getSheets()[0].appendRow([new Date(),id,name,text,answer]);

  if(/^@/.test(text)) {
    var sheetName = text.slice(1).split(" ")[0];
    var sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName) ? SpreadsheetApp.openById(ssId).getSheetByName(sheetName) : SpreadsheetApp.openById(ssId).insertSheet(sheetName);
    var comment = text.split(" ").slice(1).join(" ");
    sheet.appendRow([new Date(),id,name,comment,answer]);
  }
}

Now I encountered the following issue; I use my bot to store messages from my home automation system. Therefore I send those messages from the system to telegram bot via HTTP GET request:

https://api.telegram.org/bot[BOT_API_KEY]/sendMessage?chat_id=[MY_CHANNEL_NAME]&text=[MY_MESSAGE_TEXT]

Currently these messages sent through http get request seem to be ignored by the script. Does anyoene know how I can solve this issue?

Add more information about the response you get. You should include the headers (minus sensitive data), response code, etc. Also consider first building the request, logging it, comparing it to what the Telegram API expects, and then sending it. — tehhowch
To be more precise: The bot works when i text a message on my phone. If I enter for example "@test test123", a new sheet is generated in my spreadsheet containing a row with timestamp, chat id, first name of message sender, messagetext and the response that has been sent. However, if a message is sent by my home-automation system via http get request (as described above), the message is not processed by script. — honeyhill
is the Telegram webhook able to respond to non-user input? That may be why you have an issue when sending "@test test123" to telegram via your spreadsheet functions. If it does, I strongly recommend you preview the required that will be sent. You may need to explicitly urlencode your strings — tehhowch
After having tested it several times, I think it is not able to respond to non-user input but the question is, if there is a workaround. The messages which are sent through the http-get method are appearing in the chat - but as I mentioned, it does obviously not trigger the doPost-Post function of my script. — honeyhill

jlo jlo · Accepted Answer · 2020-04-07T16:54:29

Judging from your question and comments, it seems you are struggling with sending info from your script to your bot on Telegram. Here are the steps to do that:

1.- Create a bot: on Telegram's search look for @BotFather. Click start, write /newbot, give it a name and a username. You should get a token to access the HTTP API. Save this token.

2.- Find your bot on Telegram with its username. Write something to it e.g. 'test'. This will come in handy later.

3.- Test access to the bot from your code

var token = "123456:kioASDdjicOljd_ijsdf"; // Fill this in with your token
var telegramUrl = "https://api.telegram.org/bot" + token;

function getMe() {
  var url = telegramUrl + "/getMe";
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

You should get something resembling this:

{"ok":true,"result":{"id":<somenumber>,"is_bot":true,"first_name":"<name of your bot>","username":"<username of your bot>","can_join_groups":true,"can_read_all_group_messages":false,"supports_inline_queries":false}}

4.- Write the sendMessage function

function sendMessage(chat_id,text) {
  var url = telegramUrl + "/sendMessage?chat_id=" + chat_id + "&text=" + text;
  var response = UrlFetchApp.fetch(url);
  Logger.log(response.getContentText());
}

Known is the text you want to send e.g. 'testing bot', but chat_id is unknown. Where do we get this?

5.- Find the chat_id. Before running this function, make sure that you have at least written one message to your bot on Telegram (step 2)

 function getChat_id(){
   var res = UrlFetchApp.fetch(telegramUrl+"/getUpdates").getContentText();
   var res = JSON.parse(res);
   Logger.log(res.result[0].message.chat.id.toString());
}

6.- Run sendMessage with the chat_id you found in step 5 and the message you want to send.

Connect telegram bot with google apps script

1 Answers