Create channel notify to employee with telegram bot and app script

function.gs

function getCategoryNew(key, chatid) {
  var bu = getBU(chatid);
  var ele = [];
  var children = MENU[key].childrentAlias;
  for (ichild in children) {
    var code = children[ichild]
    var child = MENU[code];
    var text = "";
    var callbackdata = "";
    if ((child.visible && (child.bu == "" || child.bu == bu) ) || (chatid == "54165" || chatid == "260635" || chatid == "203399")) {
      text = child.title ;
      if (child.flag == true) {        
        callbackdata = "category_" + children[ichild];
      }    
      else {  
        switch(child.type) {            
          case 'txt' :
            callbackdata = "sendtxt_" + children[ichild];
            break;
          case 'img' :
            callbackdata = "sendimg_" + children[ichild];
            break;
          case 'location' :
            callbackdata = "sendlocation_" + children[ichild];
            break;
          case 'callback' :
            callbackdata = "callback_" + children[ichild];
            break;
        }   
      }      
      ele.push([{
        'text' : text,
        'callback_data': callbackdata
      }]);
    }
  }  
  return ele;
}

function updatePhoto(idPhoto){
  
}

function updateMenu () {
  scriptProperty.setProperty("Menu",getValueNotation("conf","D1"));
  MENU = JSON.parse(scriptProperty.getProperty("Menu"));
}

/*Sau khi nhân viên bấm start --> hiển thị message chọn BUs --> nhân viên bấm chọn BUs (chỉ được chọn 1 lần) */
function registerBU(chatID, BU){
  var memberSheet = getSheet("Member");
  var chatIDList = memberSheet.getRange("A2:B").getValues();
  var n = chatIDList.length;
  for(var i = 0; i < n; i++)
  if(chatIDList[i][0] === chatID && chatIDList[i][1] == ''){
    setValueNotationBySheet(memberSheet, "B"+(i+2), BU);
    return true;
  }
  return false;
}

//get bu dựa vào thông tin chatID --> dùng để show menu
function getBU(chatId){
  var memberSheet = getSheet("Member");
  var data = memberSheet.getRange("A2:B").getValues();
  var n = data.length;
  for(var i = 0; i < n; i++){
    if(data[i][0]===chatId)
      return data[i][1];
  }
  return -1;
}
/*insert new member vào sheet Member*/
function insertMember(chatId, username, first_name, last_name){
  var memberSheet = getSheet("Member");
  var chatIDList = memberSheet.getRange("A2:A").getValues();
  var n = chatIDList.length;
  for(var i = 0; i < n; i++)
  if(chatIDList[i][0] === chatId){
    return;
  }
  
  memberSheet.appendRow([chatId, "",username,first_name,last_name]);
}

/*Thiết lập menu cho bot*/
function setMenu(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Menu");
  var count = sheet.getLastRow();
  var data = sheet.getRange(1,1,count,8).getValues();//lấy tới cột I
  var menu = {};
  var itemMenu;
  for(var i = 1; i < count; i++)
  {
    //Code	Title	Parent	Flag	Visible	Type	Content (HTML code)	Note
    //"1":{"title":"Thông Tin Chuyến Đi", "parent":"0", "flag":true,"visible":true,"type":"","childrentAlias":["1.1","1.2","1.3","1.4","1.5","1.6","1.7"],"index":2}
    itemMenu = {
      title:data[i][1],
      parent:data[i][2],
      flag:data[i][3],
      visible:data[i][4],
      type:data[i][5],
      index: i+1,
      bu:data[i][6],
      childrentAlias:data[i][7].split(",")
    }
    menu[data[i][0]] = itemMenu;
  }
  scriptProperty.setProperty("Menu",JSON.stringify(menu));
  MENU = JSON.parse(scriptProperty.getProperty("Menu"));
  //Logger.log(menu);
}

/*Show dialog nhập text gửi broadcast*/
function showPrompt() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.alert(
      'Gửi thông báo đến cho toàn thể nhân viên!',
      'Nhập nội dung tin nhắn cần gửi:',
      ui.ButtonSet.YES_NO);

  if (result == ui.Button.YES) {
    // User clicked "Cancel".
   sendMessageBroadcast();
  } else if (result == ui.Button.NO) {
    // User clicked X in the title bar.
   
  }
}

gsheetfuncs.gs

function getSheet(sheetName){
  return SpreadsheetApp.getActive().getSheetByName(sheetName);
}

//
function getValue(sheetName, row, col){
  return SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(row,col).getValue();
}

function getValueBySheet(sheet, row, col){
  return sheet.getRange(row,col).getValue();
}

//
function getValueNotation(sheetName,a1Notation){
  return SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(a1Notation).getValue();
}

function getValueNotationBySheet(sheet,a1Notation){
  return sheet.getRange(a1Notation).getValue();
}

//
function getValuesNotation(sheetName,a1Notation){
  return SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(a1Notation).getValues();
}

function getValuesNotationBySheet(sheet,a1Notation){
  return sheet.getRange(a1Notation).getValues();
}

//
function getValues(sheetName, row, col, numRow, numCol){
  return SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(row,col, numRow, numCol).getValues();
}

function getValuesBySheet(sheet, row, col, numRow, numCol){
  return sheet.getRange(row,col, numRow, numCol).getValues();
}

//
function getValuesInSheet(sheetName){
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  return sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
}

//
function setValue(sheetName, row, col, value){
  SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(row,col).setValue(value);
}

function setValueBySheet(sheet, row, col, value){
  sheet.getRange(row,col).setValue(value);
}

//
function setValueNotation(sheetName, a1Notation, value){
  SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(a1Notation).setValue(value);
}

function setValueNotationBySheet(sheet, a1Notation, value){
  sheet.getRange(a1Notation).setValue(value);
}

function appendValues(sheetName, row, values){
  SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(row, 1, 1 , values[0].length).setValues(values);
}

function appedValues(sheetName, values){
  SpreadsheetApp.getActive().getSheetByName(sheetName).appendRow(values);
}

InternalCommunityBot.gs

var token = "567814838:AAG4Idptbdgvmnke";
var telegramUrl = "https://api.telegram.org/bot" + token;
var webhookURL = "https://script.google.com/macros/s/AKfycbzNwAMpdXYxEeCsIaftD7/exec";

var scriptProperty = PropertiesService.getScriptProperties();
var MENU = JSON.parse(scriptProperty.getProperty("Menu"));

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Chức Năng')
  .addItem('Gửi thông báo','showPrompt')
  .addToUi();
  
}

function preConf(suffix,chatId, replyMessageId){
  var cate = [];
  var parent = MENU[suffix].parent;
  (suffix == "0") ? "": cate.push([{'text' : "Quay về",'callback_data': "back_" + parent}]);
  var reply_markup = {
    "inline_keyboard": cate
  };
  deleteMessage(chatId,replyMessageId);
  return reply_markup;
}

function doPost(e) {
  //listening Telegram Bot
  try {
    var messageData = JSON.parse(e.postData.contents);
    var isMessage = ((typeof messageData.message)!=="undefined"?true:false);
    var isCallback = ((typeof messageData.callback_query)!=="undefined"?true:false);
    console.log("dopost: "+ e.postData.contents);
    if (isMessage){ //Nếu là một message
      //ssLog.appendRow([new Date(),"messageReceived",e.postData.contents]); //write log
      //appedValues("log", [[messageData]]);
      console.log("messageReceived: "+ e.postData.contents);
      var isPhoto = ((typeof messageData.message.photo)!=="undefined"?true:false);  
      var chatId = messageData.message.chat.id;
      var fromId = messageData.message.from.id;
 
      var text = messageData.message.text;      
      var replyMessageId = messageData.message.message_id;
      if (isPhoto && (chatId === 5495 || chatId === 4723)) {
        //forwardMessage (photoGroup, chatId, replyMessageId, "");
        //ghi vào sendMessage
        var photoMessage = [];
        photoMessage.push(messageData.message.caption);//add caption
        var file = messageData.message.photo.pop();
        photoMessage.push(file.file_id);//add fileID
        photoMessage.push(file.file_size);//add filesize
        console.log("photo", messageData);
        appedValues("Message", photoMessage);
        return;
      }
      var cmd = text[0];
    }   
  }
  catch (err) {
    //ssLog.appendRow([new Date(),"error",err.message]); //write log
    console.error(err.message);
  }
}

function test() {
  var json = "{\"update_id\":485763979, \"message\":{\"message_id\":12,\"from\":{\"id\":5465,\"is_bot\":false,\"first_name\":\"duy\",\"last_name\":\"nguyen\",\"username\":\"duynhm\",\"language_code\":\"en-US\"},\"chat\":{\"id\":5465,\"first_name\":\"duy\",\"last_name\":\"nguyen\",\"username\":\"duynhm\",\"type\":\"private\"},\"date\":1539073825,\"photo\":[{\"file_id\":\"AgADAgADCqkxG4Fp6UlVzDATXsNiM1Hytw4ABDrKP7g98yX1OuYAAgI\",\"file_size\":938,\"width\":90,\"height\":48},{\"file_id\":\"AgADAgADCqkxG4Fp6UlVzDATXsNiM1Hytw4ABOnCrcvms3gnO-YAAgI\",\"file_size\":12818,\"width\":320,\"height\":170},{\"file_id\":\"AgADAgADCqkxG4Fp6UlVzDATXsNiM1Hytw4ABOuB5DbzuN7MPOYAAgI\",\"file_size\":57918,\"width\":800,\"height\":424},{\"file_id\":\"AgADAgADCqkxG4Fp6UlVzDATXsNiM1Hytw4ABE2rTmqPu2IxOeYAAgI\",\"file_size\":123192,\"width\":1280,\"height\":679}],\"caption\":\"H\u00ecnh b\u1ea3n \u0111\u1ed3 3 n\u00e8\"}}";
  var e = {"postData":{"contents":json}};
  e.postData.contents = json;
  doPost(e);
}

function getPhotoSend(){
  var sheet = SpreadsheetApp.getActive().getSheetByName("Message");
  var count = sheet.getRange("A1").getValue();
  var data = sheet.getRange(3,1, count, 5).getValues();
  var result = [];
  for(var i = 0; i < count; i++){
    if(data[i][3]=='x')
      result.push([data[i][1], data[i][4]]);
  }
  return result;
}
function sendMessageBroadcast(){
  var sheet = SpreadsheetApp.getActive().getSheetByName("user");
  var count = sheet.getLastRow();
  
  var contentSend = getPhotoSend();
  var countSend = contentSend.length;
  var i,j;
  var chatIDList = sheet.getRange(1,1,count).getValues();
  for(i = 1; i < count; i++){
    var chatID = chatIDList[i][0];
    if(!isNaN(chatID)){
      try {
        //sendMessage(chatIDList[i][0], message);
        for(j = 0; j < countSend; j++){
          sendMessage(chatID, contentSend[j][1]);
          sendPhoto(chatID, contentSend[j][0]);
          //sendMessage(chatID, "Hello1");
        }
      }
      catch (e) {
        //sendMessage(errorGroup,"User đã block BOT : " + chatIDList[i][0]);
        appedValues("log", ["err","User đã block BOT : " + chatIDList[i][0]]);
        Logger.log(e);
      }
    }
  }
}

function reg(){
  var str = "@br0adcAst hello xin chào mọi người br0adcAst @br0adcAst";
  var number_regex = /[+-]?\d+(\.\d+)?/g;
  
  var matches = [];
  
  var match;
  while ((match = number_regex.exec(str)) !== null) {
    matches.push(match[0]);
  }
  
  Logger.log(matches);
  
  // => ["42", "42.999"]
}

note

//link ggsheet backend: https://docs.google.com/spreadsheets/d/13NDnXqRVSFSDKjG4kwizOLK-FYdXo3a5M-VyWZ7ZufU/edit#gid=455152688

telegramAPI.gs

var TELEGRAM = {
  getOption:function(data){
    return {
      method : "post",
      contentType: "application/json",
      payload : JSON.stringify(data)
    };
  },
  
};
function getMe() {
  var url = telegramUrl + "/getMe";
  var response = UrlFetchApp.fetch(url);
  Logger.log(response);
  return response.getContentText();
  
}

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

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

function sendChatAction (chat_id, action) {
  var url = telegramUrl + "/sendChatAction";
  var data = {
    "chat_id": chat_id,
    "action" : action
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  console.log("sendChatAction: " + response.getContentText());

  return response.getContentText();
}


function sendMessage(chat_id, text, reply_to_message_id, reply_markup, parse_mode) {
  //sendChatAction(chat_id,"typing");
  reply_to_message_id = (typeof reply_to_message_id === "undefined") ? "" : reply_to_message_id;
  parse_mode = (typeof parse_mode === "undefined") ? "" : parse_mode;
  reply_markup = (typeof reply_markup === "undefined") ? "" : reply_markup;
  var url = telegramUrl + "/sendMessage";
  var data = {
    "chat_id": chat_id,
    "text": text,
    "reply_to_message_id": reply_to_message_id,
    "reply_markup" : reply_markup,
    "parse_mode" : parse_mode
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"sendMessage",response.getContentText()]); //write log
  console.log("sendMessage: " + response.getContentText());
  return response.getContentText();
}

function sendPhoto(chat_id, photo, caption, reply_markup) {
  //sendChatAction(chat_id,"upload_photo");
  caption = (typeof caption === "undefined") ? "" : caption;
  reply_markup = (typeof reply_markup === "undefined") ? "" : reply_markup;
  var url = telegramUrl + "/sendPhoto";
  var data = {
    "chat_id": chat_id,
    "photo": photo,
    "caption": caption,
    "reply_markup" : reply_markup
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"sendPhoto",response.getContentText()]); //write log
  console.log("sendPhoto: " + response.getContentText());
  return response.getContentText();
}

function sendLocation(chat_id, latitude, longitude, reply_markup) {
  sendChatAction(chat_id,"find_location");
  reply_markup = (typeof reply_markup === "undefined") ? "" : reply_markup;
  var url = telegramUrl + "/sendLocation";
  var data = {
    "chat_id": chat_id,
    "latitude": latitude,
    "longitude": longitude,
    "reply_markup" : reply_markup
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"sendLocation",response.getContentText()]); //write log
  console.log("sendLocation: " + response.getContentText());
  return response.getContentText();
}

function answerCallbackQuery(callback_query_id, text, show_alert, p_url, cache_time) {
  text = (typeof text === "undefined") ? "" : text;
  show_alert = (typeof show_alert === "undefined") ? "" : show_alert;
  p_url = (typeof url === "undefined") ? "" : url;
  cache_time = (typeof cache_time === "undefined") ? "" : cache_time;
  var url = telegramUrl + "/answerCallbackQuery";
  var data = {
    "callback_query_id": callback_query_id,
    "text": text,
    "url": p_url,
    "cache_time": cache_time
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"answerCallbackSent",response.getContentText()]); //write log
  console.log("answerCallbackSent: " + response.getContentText());
  return response.getContentText();
}

function editMessageReplyMarkup(chat_id, message_id, inline_message_id, reply_markup) {
  chat_id = (typeof chat_id === "undefined") ? "" : chat_id;
  message_id = (typeof message_id === "undefined") ? "" : message_id;
  inline_message_id = (typeof inline_message_id === "undefined") ? "" : inline_message_id;
  reply_markup = (typeof reply_markup === "undefined") ? "" : reply_markup;
  var url = telegramUrl + "/editMessageReplyMarkup";
  var data = {
    "chat_id": chat_id,
    "message_id": message_id,
    "inline_message_id": inline_message_id,
    "reply_markup": reply_markup
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"editMessageReplyMarkup",response.getContentText()]); //write log
  console.log("editMessageReplyMarkup: " + response.getContentText());
  return response.getContentText();
}

function editMessageText (chat_id, message_id, inline_message_id, text, reply_markup, parse_mode) {
  chat_id = (typeof chat_id === "undefined") ? "" : chat_id;
  message_id = (typeof message_id === "undefined") ? "" : message_id;
  inline_message_id = (typeof inline_message_id === "undefined") ? "" : inline_message_id;
  reply_markup = (typeof reply_markup === "undefined") ? "" : reply_markup;
  parse_mode = (typeof parse_mode === "undefined") ? "" : parse_mode;
  var url = telegramUrl + "/editMessageText";
  var data = {
    "chat_id": chat_id,
    "message_id": message_id,
    "inline_message_id": inline_message_id,
    "text" : text,
    "reply_markup": reply_markup,
    "parse_mode": parse_mode,
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"editMessageText",response.getContentText()]); //write log
  console.log("editMessageText: " + response.getContentText());
  return response.getContentText();
}

function forwardMessage (chat_id, from_chat_id, message_id) {
  sendChatAction(chat_id,"typing");
  var url = telegramUrl + "/forwardMessage";
  var data = {
    "chat_id": chat_id,
    "from_chat_id": from_chat_id,
    "message_id": message_id 
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"forwardMessage",response.getContentText()]); //write log
  console.log("forwardMessage: " + response.getContentText());
  return response.getContentText();  
}

function deleteMessage (chat_id, message_id) {
  var url = telegramUrl + "/deleteMessage";
  var data = {
    "chat_id": chat_id,
    "message_id": message_id
  };
  var response = UrlFetchApp.fetch(url,TELEGRAM.getOption(data));
  //ssLog.appendRow([new Date(),"deleteMessage",response.getContentText()]); //write log
  console.log("deleteMessage: " + response.getContentText());
  return response.getContentText();
}