CODEWITHSUNDEEP
javascripttypescriptapigoogle-sheetstelegram-bot
Masoud
Masoud

Reputation: 1255

input data from telegram bot in google sheet

I wanna use spreadsheet via telgeram bot as TODO list in order to when input something in my phone that should be save in spreadsheet.(im coding just like this https://www.youtube.com/watch?v=XoTpdxbkGGk everything is true and google sheet , telegram bot are truly fectched but i dont know why when i input data in telegram bot this isnt save anything in google sheet.

please help me guys

var token="<<BOT-TOKEN>>";
var url="https://api.telegram.org/bot" + token ;
var webAppUrl="https://script.google.com/macros/s/<<secret>>/exec";
var spId='<<secret>>';
function getme() {
  var response=UrlFetchApp.fetch(url + "/getme");
  Logger.log(response.getContentText());
}
function getupdates() {
  var response=UrlFetchApp.fetch(url + "/getupdates");
  Logger.log(response.getContentText());
} 

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

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

function doGet(m){ 
  return HtmlService.createHtmlOutput("Heloo" + JSON.stringify(m));
}
function doPost(m){
    var contents = JSON.parse(m.PostData.contents);
GmailApp.sendEmail(Session.getEffectiveUser().getEmail(),"Telegram Bot Update",JSON.stringify(contents,null,4));
  var text = contents.message.text;
  var id = contents.message.from.id;
  var name = contents.message.from.first_name + ' ' + contents.message.from.last_name;
  sendText(id, "HI" + name);
SpreadsheetApp.openById(spId).appendRow([new Date(),id,text,contents,name]);
  SpreadsheetApp.openById(spId).appendRow([1,2,3,4,5]);
}
/*
{  
    "parameter": {},
    "contextPath": "",
    "contentLength": 310,
    "queryString": "",
    "parameters": {},
    "postData": {
        "type": "application/json",
        "length": 310,
        "contents": "{\"update_id\":*,\n\"message\":{\"message_id\":12,\"from\":{\"id\":*,\"is_bot\":false,\"first_name\":\"*\\*\",\"username\":\"*\",\"language_code\":\"fa\"},\"chat\":{\"id\":*,\"first_name\":\"*\\*\",\"username\":\"*\",\"type\":\"private\"},\"date\":1558331571,\"text\":\"salaaaaaaaaaaaaam\"}}",
        "name": "postData"

}
*/

expect this save everything that typed by telegram bot and save the data in google sheet rows.but it dosent save data or show them in sheet cells.it shows nothing

Upvotes: 1

Views: 7302

Answers (1)

rotimi-best
rotimi-best

Reputation: 1922

In my opinion using App script to interact with your spreadsheet is not really flexible and might not be good if you want to scale your application and add more functionalities.

I created a whole project that allows lecturers manage their registers and take attendance from telegram and then update a spreadsheet automatically. I used the official Google Spreadsheet Api which has great documentation and flexible use compared to App script.

Here is the code to the project on github. You can find the places where I wrote functions for the spreadsheet in the modules folder and then in the spreadSheetController. The project is kind of large but I can give a simple work through on how it will fit your use case.

Basic Requirements:

  1. Use a library for interacting with telegram api instead of making POST and GET requests yourself. You can use node-telegram-bot-api but you can use other libraries of your choice.

  2. Authenticate your google spreadsheet using the api. There is a great quick start from the google team follow the instructions and you should have a programmable spreadsheet. I am assuming you will do it in nodejs.

Once you have these 2 things the project I linked to above would be useful. I hope it helps, if it isn't clear let me know I don't want to paste all the code here (it's quit a lot) but the attendance project should be self explanatory.

Upvotes: 2

Related Questions