title: Sending message to Telegram from Google Sheet via Google Scripts
body: <p>I'm trying to send a telegram message to myself, every morning, with a different quote that I have listed in a Google Sheet. I wrote some code that adds messages to the list, but I can't seem to generate a random row from the list to send daily. </p>
  
  <pre><code>var token = "TOKEN"; 
  var telegramAPI = "https://api.telegram.org/bot" + token; 
  var webAppAPI = "https://script.google.com/macros/s/GOOGLE_WEB_APP_ADDRESS";
  var ssId = "SPREADSHEET_ID"; 
  
  function getMe() {
    var url = telegramAPI + "/getMe";
    var response = UrlFetchApp.fetch(url);
    Logger.log(response.getContentText());
  }
  
  function setWebhook() {
    var url = telegramAPI + "/setWebhook?url=" + webAppAPI;
    var response = UrlFetchApp.fetch(url);
    Logger.log(response.getContentText());
  }
  
  function sendText(id,text) {
    var url = telegramAPI + "/sendMessage?chat_id=" + id + "&amp;text=" + text;
    var response = UrlFetchApp.fetch(url);
    Logger.log(response.getContentText());
  }
  
  function doGet(e) {
    return HtmlService.createHtmlOutput("Test Data" + JSON.stringify(e,null,4));
  }
  
  function doPost(e) {
    Logger.log(e);  
    var data = JSON.parse(e.postData.contents);
    var text = data.message.text;
    var what = data.message.text.split("-")[0]
    var who = data.message.text.split("-")[1]
    var id = data.message.chat.id;
    var name = data.message.chat.first_name;
    var response = "Hi " + name + ", this quote has been added to your database: " + text;
    sendText(id,response);
    SpreadsheetApp.openById(ssId).getSheets()[1].appendRow([new Date(),id,name,text,response,what,who]);
  </code></pre>
  
  <p>All of this works fine. I added a query that pulls them over to my Quote sheet from my Telegram Feed sheet, that I'll put here to help someone: </p>
  
  <pre><code>=IFERROR(QUERY('Telegram Feed'!$G$1:$G$98,"",-1),"Error")
  </code></pre>
  
  <p>Now that I'm pulling in quotes, I want to generate a random one from the list and schedule it to send to myself on a daily basis. I've included what I've tried below, but I can't seem to figure out what I'm doing wrong.</p>
  
  <p>The randomizer is partially working, but seems to be grabbing all of the content, which I need to refactor to say something along the lines of: </p>
  
  <pre><code>message = f"{quote} + ' - ' + {author}"
  </code></pre>
  
  <p>Randomizer:</p>
  
  <pre><code>function randomizer() {
    var ssa = SpreadsheetApp.openById(ssId);
    var ss = ssa.getSheetByName('Quotes'); 
    var range = ss.getRange(1,1,ss.getLastRow(), 2);
    var data = range.getValues();
  
    for(var i = 0; i &lt; data.length; i++) 
    { 
      var j = Math.floor(Math.random()*(data[i].length));
      var element = data[i][j]; 
      ss.getRange(i+1, 6).setValue(element); 
      Logger.log(element);
    }
  }  
  </code></pre>
  
  <p>Up until this point, it mostly works (even though I need to figure out how to fix the randomizer function as mentioned above. It's when I try to send a random message from the script to Telegram that I run into problems.</p>
  
  <pre><code>function sendQuote(what,who) {
    var data = randomizer();
    var dataJSON = JSON.parse(data.postData.contents);
    var url = telegramAPI + "/sendMessage?chat_id=" + 'CHAT_ID_NUM' + "&amp;text=" + what + " - " who;
  } 
  </code></pre>
  
  <p>I'm getting nothing back. Anyone know what I'm doing wrong?</p>
  
  <p>EDIT:</p>
  
  <p>I followed the suggestions from Дмитро-Булах &amp; carlesgg97, and I refactored a bunch of my randomize code to give me a quote and author. For some reason, I'm now getting the error "TypeError: Cannot read property "postData" from undefined.: from the line that reads <code>var dataJSON = JSON.parse(data.postData.contents);</code></p>
  
  <p>Does anyone know why this is happening?
  I'll close the issue within 24hrs regardless. Thanks for the help everybody!</p>
  
  <pre><code>function sendQuote(quote,author) {
    var data = randomize();
    var dataJSON = JSON.parse(data.postData.contents);
    var encodedText = encodeURIComponent(quote + " - " + author);
    var url = telegramAPI + "/sendMessage?chat_id=" + 'CHAT_ID' + "&amp;text=" + encodedText;
    UrlFetchApp.fetch(url);
  } 
  
  function randomize() {
    var sss = SpreadsheetApp.openById(ssId);
    var ss = sss.getSheetByName('Quotes');
  
    var length = ss.getLastRow();
    var overshoot = 97 //monitor for changes as list size increases
    var true_length = length-overshoot;   
    var line = (Math.random() * ((true_length - 2) + 1)) + 2;
  
    var quote_cell = ss.getRange(line,2);
    var quote = quote_cell.getValue();
    var author_cell = ss.getRange(line,1);
    var author = author_cell.getValue();
    Logger.log(quote + " - " + author);
  }
  </code></pre>