1

So I have Telegram Bot that works with Google App Script.

My bot sends the last row that was edited in my Sheet in any List ( more then 10 list).

And the message from bot goes with inline button "Accepted" - and I'm receiving an array with call_back data.

But I don't know how can I fix "Accepted" to the row ( fixed cell ) where I got message from.

Func - message to telegram

function onEdit(e) {
  sendTelegram(e)
}

function sendTelegram(e){

var row = e.range.getRow();
var col = e.range.getColumn();

var startRow = 2; // Starting row
var targetColumn = 2; // Row, where placed trigger to send msg 
var ws = sheetName; //List name


var sheet = e.source.getActiveSheet();
var sheetName = e.source.getActiveSheet().getName(); //Takes list name
let Company = e.source.getActiveSheet().getRange(row,13).getValue(); //Takes data from row 2 to 13 Column in every list
var firstCol = 2; // Starting from 2 column
var numOfCols = 13;//Ending in 13 column
var fullRowValues = sheet.getRange(row, firstCol, 1, numOfCols).getValues();
var fullRowString = fullRowValues.flat().toString(); // Makes array toString.
let chatId = "ChatId";// Telegram groupId
var text = encodeURIComponent(Company + " New Document has Been added" + ws)

if(e.source.getActiveSheet().getRange(row,2).getValue() == "Yanson"){ //Yanson- trigger.If in column 2 "Yanson" is set - sends row to group chat.
    sendText(chatId,"[New Doc Added!]  " + Company + sheetName + " , " + fullRowString,accepted);} //accepted - inline button

Send Text Func.

 function sendText(chatId, text, keyBoard) {
  let data = {
    method: 'post',
    payload: {
      method: 'sendMessage',
      chat_id: String(chatId),
      text: text,
      parse_mode: 'HTML',
      reply_markup: JSON.stringify(keyBoard)
    }
  }
  UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/', data);
  }

And doPost func that track when button was clicked and saving it in "Log" list as array of data.

function doPost(e) {
  let contents = JSON.parse(e.postData.contents);
  SpreadsheetApp.openById("SheetId").getSheetByName("Log").appendRow([contents]);

So what I just need is to make When someone click on the button "Accepted" - text "Accepted" appears in 15 column near edited row that just was sent by bot. This is how one of my list looks (example). Tablepic And this is how looks array that I'm receiving from bot[Contents]. arrayFromButton

I'm sure we can connect list and button by the Case Name (111/111/111) but don't know how :( 1.Yanson - trigger to send msg 2.Bot sends Msg with row. 3.Pressing inline button in bot msg. 4. Accepted appears near the row that was sent. Thats what I'm trying to get and searching for the solution for step 4. Logic

Brown
  • 25
  • 5
  • 1
    You add json data to the sheet. You need to get the values inside an array. Look at `Object.values(contents)` And maybe you need to filter some out. – RemcoE33 Oct 18 '21 at 18:48
  • 1
    Can you provide a sample expected output? sorry i did not fully understood the goal. – Ron M Oct 18 '21 at 19:40
  • Yep. So I expect when someone click the button, text "Accepted" will appear near the row ( somewhere in 14-15 column but near the row.) that was sent by bot ( Bot sends last row that was added in sheet (list) with button "Accepted". If we take as example my screen with sheet, Accepted can appear in column "J" if this row was sent by the bot and someone has clicked button "Accepted" I guess I need to filter json output with courtcase ( 111/111/111) but I have no idead how to filter it and then connect it to main sheet with raw and make it fix Accepted near this raw if button was clicked. – Brown Oct 18 '21 at 21:25
  • So basically I expect when someone clicks button Accept - text Accept will appear near this row that was sent by the bot :( – Brown Oct 18 '21 at 21:26
  • 2
    I get it that you want to add "Accepted" in a particular row. What is not clear is how to determine which row should have the "Accepted". I really tried my best to understand the data sent by your bot but I cannot comprehend it. Please provide a copy of your bot message in text (not in screenshot). And provide some information on how to determine which row should have the accepted value. Please based your conditions on the actual content of your bot message. I did not find the court case you are mentioning. – Ron M Oct 18 '21 at 22:08
  • Ok. So this is how bot message looks like. [New Document has been added!] ListName , Yanson,111111111,Name Name Name,111/1111/111,CourtName,State,City,House,Link to document,,,, I think we can determine which row should have accepted value by comparing "ListName" and "CaseName" from bot with Google Sheet and found the last one that was added. Well... I have no move ideas – Brown Oct 19 '21 at 06:51
  • {update_id=11111111,callback_query={message={from={username=reestrDocbot, is_bot=true,id=11111111,first_name=docReestr}, text=[New doc Added]ABC, ABC,3163417968,NAMENAMЕNAME,111/111/111,Court,Link to document,reply_markup={inline_keyboard=[Ljava.lang.Object;@56a3c7a3},chat={id=1111111,all_members_are_administrators=true,type=group, title=GroupName},entities=[Ljava.lang.Object;@4f2b430b,message_id=1111111, date=111111111111},data=Accepted,id=111111111111,from={id=11111111, is_bot=false,first_name=My, language_code=en,last_name=Name, username=username},chat_instance=-11111111}} Log from button. – Brown Oct 19 '21 at 07:55
  • Although, I guess we can determine which row should have the accepted value , by connect it to "trigger" if(e.source.getActiveSheet().getRange(row,2).getValue() == "Yanson") So when someone sets in 2 column Yanson - this row send to chat by the bot, and when someone click button - Accepted somehow have to appear next to this row idk... – Brown Oct 19 '21 at 13:08
  • how are you doing? have done?) – dzNET Oct 23 '21 at 07:53

0 Answers0