Telegram API Question posted in
A comprehensive official documentation can be found here.

How can I match exact text in a particular column and output the entire row of information? – Telegram API

I have been attempting to create a Telegram bot that searches a preexisting database and outputs information based on search query, essentially I want the bot to just receive a text via Telegram that contains an invoice number and output all relevant information regarding that order (The entire row of information).

Since I am dealing with invoice numbers and tracking numbers, sometimes the bot is exporting incorrect information given the current script is not matching exact text or a specific column.

For instance, rather than searching and finding invoice number it picks up a partial match of tracking number and outputs the wrong information.

I would like to set it up to search a specific column, ie. Column 3 – “Invoice #” and then output the entire row of information.

Thanks in advance!

I have been working in Google App Script:

var token = "";
var telegramUrl = "https://api.telegram.org/bot" + token;
var webAppUrl = "";
var ssId = "";

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) {
 
  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 + ", please enter invoice number.";
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders");
  var search_string = text
  var textFinder = sheet.createTextFinder(search_string)
  var search_row = textFinder.findNext().getRow();
  var value = SpreadsheetApp.getActiveSheet().getRange("F"+search_row).getValues();
  var value_a = SpreadsheetApp.getActiveSheet().getRange("G"+search_row).getValues();
  
  
  
 
  
 sendText(id,value+" "+ value_a)
 }
Tags:

2

Answers


  1. 0

    I would look for a specific column where the order number is stored.

    I`m not sure if it is the best way from performance side, but I think it should work. 

    function orderInformation(orderNumber){

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders");
//Gets last row in Orders sheet
var lastRow = sheet.getLastRow();
//Here you can change column where is order number stored
var orderNumberRange = sheet.getRange("A1:A" + lastRow);
//Gets all order number values
var orderNumbers = orderNumberRange.getValues();
//You can use indexOf to find which row has information about requested order
var orderLocation = orderNumbers.indexOf(orderNumber);
//Now get row with order data, lets suppose that your order information is from column A to Z
var orderData = sheet.getRange("A" + (orderLocation + 1) + ":Z" + (orderLocation + 1)).getValues();

//Now you have all data in array, where you can loop through and generate response text for a customer.

}

    Sorry, I have not tested it, at the moment I don`t have time to make a test sheet, but this is the way I would do it and I think it should work.

    I will test it maybe later when I will be able to make a test sheet.

    Login or Signup to reply.
  2. 0

    You want to find rows where the content in column 3 is exactly equal to your variable “text”

    Modify your function doPost as following:

    function doPost(e) {

  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 + ", please enter invoice number.";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders");
  var range = sheet.getRange(1,1, sheet.getLastRow(), sheet.getLastColumn());
  var values = range.getValues();
  for (var i=0; i< values.length; i++){
    var column3Value = values[i][2];
    if(column3Value == text){
       var rowValues = values[i].toString();  
       sendText(id,rowValues)
     }
   }
 }

    Explanations

    • The for loop iterates through all rows and compares the values in column 3 (array element[2] against the value of text
    • The operator == makes sure that only exact matches are found (indexOf() would also retrieve partial matches)
    • In case a match is found, the values from the whole row are converted to a comma separated string with toString() (You can procced the values differntly if desired)
    • Every row with a match will be sent to the function sendText() (you could alternatively push all rows with matches into an array / string and call sendText() only once, after exiting the for loop

    I hope this answer helps you to solve your problem and adapt the provided code snippet to your needs!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search