Telegram bot with Google Apps Script (GAS)

// === CONFIGURATION ===
var TOKEN = 'YOUR_TELEGRAM_BOT_TOKEN';  // Replace with your bot token
var WEBHOOK_URL = 'YOUR_WEB_APP_URL';   // Replace with your deployed GAS web app URL
var USERS_SPREAD_SHEET_NAME = 'usersAnswers';  // Sheet name where user data is stored
 
// === COLUMN MAPPINGS ===
// Spreadsheet columns used for saving different values
var COLUMN_DATE = 'A';      // Column for date
var COLUMN_STEP_1 = 'E';    // First user input
var COLUMN_STEP_2 = 'F';    // Second user input
 
 
// === WEBHOOK SETUP ===
// Run this function manually once after every deployment to set the Telegram webhook
function setWebhook() {
  var url = 'https://api.telegram.org/bot' + TOKEN + '/setWebhook?url=' + WEBHOOK_URL;
  UrlFetchApp.fetch(url);
}
 
 
// === TELEGRAM MESSAGE HANDLER ===
// This function is triggered automatically when a new message is received via webhook
function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var message = data.message.text;
  var chatId = String(data.message.chat.id);
  var name = data.message.chat.first_name + " " + data.message.chat.last_name;
  var userData = data.message.from;
 
  const propertiesService = PropertiesService.getScriptProperties();
  let chatDialogStatus = propertiesService.getProperty(chatId);
  let msg = '';
  const COMMANDS = '\n/start\n/about\n/stat\n/add\n/show\n/pause\n/request';
 
  try {
    if (message === '/start') {
      // Reset dialog status and save interaction
      propertiesService.deleteProperty(chatId);
      saveRequestToSpreadsheet(userData, message);
      msg = 'Choose an action:' + COMMANDS;
 
    } else if (message === '/add') {
      // Start a multi-step input process
      propertiesService.setProperty(chatId, 'WAITING_FOR_ADD_STEP_1');
      updateChatText(userData, Date.now().toLocaleString(), COLUMN_DATE);
      msg = "Step 1. Send first value";
 
    } else if (chatDialogStatus === 'WAITING_FOR_ADD_STEP_1') {
      // Save first value and prompt for second
      updateChatText(userData, message, COLUMN_STEP_1);
      propertiesService.setProperty(chatId, 'WAITING_FOR_ADD_STEP_2');
      msg = 'Step 2. Send second value';
 
    } else if (chatDialogStatus === 'WAITING_FOR_ADD_STEP_2') {
      // Save second value and end the dialog
      updateChatText(userData, message, COLUMN_STEP_2);
      propertiesService.deleteProperty(chatId);
      msg = 'Done. Two values saved in different columns';
 
    } else {
      // Handle unrecognized commands or messages
      propertiesService.deleteProperty(chatId);
      saveRequestToSpreadsheet(userData, message);
      msg = `Hello ${name} (id: ${chatId})\nYour text:\n${message}`;
      msg += '\nPossible commands:' + COMMANDS;
    }
 
  } catch (err) {
    msg += '\nError: ' + err + '\nStatus: ' + chatDialogStatus;
  }
 
  sendTelegramMessage(chatId, msg);
}
 
 
// === TELEGRAM API ===
// Sends a message to the user via Telegram
function sendTelegramMessage(chatId, text) {
  var apiUrl = 'https://api.telegram.org/bot' + TOKEN + '/sendMessage';
  var payload = {
    'method': 'post',
    'payload': {
      'chat_id': chatId,
      'text': String(text)
    }
  };
  UrlFetchApp.fetch(apiUrl, payload);
}
 
 
// === DATA STORAGE ===
// Appends a new row to the spreadsheet with user info and message
function saveToSpreadsheet(userData, text) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(USERS_SPREAD_SHEET_NAME);
  sheet.appendRow([
    userData.id,
    userData.first_name,
    userData.last_name,
    userData.username,
    text
  ]);
}
 
// Updates an existing user's row in the specified column with new text
function updateChatText(userData, text, column) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(USERS_SPREAD_SHEET_NAME);
  var data = sheet.getDataRange().getValues();
  var rowIndex = -1;
 
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == userData.id) {
      rowIndex = i + 1;  // Spreadsheet rows are 1-indexed
      break;
    }
  }
 
  var columnNumber = column.charCodeAt(0) - 65 + 1; // Convert column letter (A, B, ...) to number
 
  if (rowIndex != -1) {
    sheet.getRange(rowIndex, columnNumber).setValue(text);
    Logger.log("Text updated successfully.");
  } else {
    Logger.log("ChatId not found. Creating new entry.");
    saveToSpreadsheet(userData, text);  // Create new row if user doesn't exist
  }
}
 
// Retrieves all answers for a given user ID
function getAllSessionsForUser(userData) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(USERS_SPREAD_SHEET_NAME);
  var rows = sheet.getDataRange().getValues();
  var sessions = [];
 
  for (var i = 0; i < rows.length; i++) {
    if (rows[i][0] === userData.id) {
      sessions.push(rows[i][4]);  // Assuming answer is in the 5th column (E)
    }
  }
 
  return sessions;
}
 
 
// === UTILITIES ===
// Saves a command (like /start or a message) to the spreadsheet
function saveRequestToSpreadsheet(userData, message) {
  saveToSpreadsheet(userData, message);
}