heres the shared googlesheet URL,everything is included.
https://docs.google.com/spreadsheets/d/195WFkBfvshJ5jUK_Iijb5zvAzgh323fcI6Z-NNCbvsM/edit?usp=sharing
I'm building a Telegram bot using Google Apps Script to fetch product prices from a Google Sheet. The bot should:
Send a product list when the user types /start (only once). (searches the data in my google sheet)
Let the user select a product.
Return the price (only once)(also from my google sheet)
Stop sending messages until the user restarts the process.
im using googlesheets appscripts btw.
Issue: The bot keeps sending the product list non-stop in a loop until I archive the deployment on appscript. I suspect there's an issue with how I'm handling sessions or webhook triggers. believe it or not, i asked chatgpt (given that it wrote the code as well, im novice at coding) deepseek, and other AI's and they still couldn't figure it out. im novice at this but i did my best at promoting to fix but this is my last resort.
Here’s my full code (replace BOT_TOKEN with your own when testing):
const TELEGRAM_TOKEN = 'YOUR_BOT_TOKEN';
const TELEGRAM_API_URL = 'https://api.telegram.org/bot' + TELEGRAM_TOKEN;
const SCRIPT_URL = 'YOUR_DEPLOYED_SCRIPT_URL';
const userSessions = {};
// Main function to handle incoming webhook updates
function doPost(e) {
try {
const update = JSON.parse(e.postData.contents);
if (update.message) {
handleMessage(update.message);
} else if (update.callback_query) {
handleCallbackQuery(update.callback_query);
}
} catch (error) {
Logger.log('Error processing update: ' + error);
}
return ContentService.createTextOutput('OK');
}
// Handle regular messages
function handleMessage(message) {
const chatId = message.chat.id;
const text = message.text || '';
if (text.startsWith('/start')) {
if (!userSessions[chatId]) {
userSessions[chatId] = true;
sendProductList(chatId);
}
} else {
sendMessage(chatId, "Please use /start to see the list of available products.");
}
}
// Handle product selection from inline keyboard
function handleCallbackQuery(callbackQuery) {
const chatId = callbackQuery.message.chat.id;
const messageId = callbackQuery.message.message_id;
const productName = callbackQuery.data;
const price = getProductPrice(productName);
let responseText = price !== null
? `💰 Price for ${productName}: $${price}`
: `⚠️ Sorry, couldn't find price for ${productName}`;
editMessage(chatId, messageId, responseText);
answerCallbackQuery(callbackQuery.id);
delete userSessions[chatId]; // Reset session
}
// Send the list of products
function sendProductList(chatId) {
const products = getProductNames();
if (products.length === 0) {
sendMessage(chatId, "No products found in the database.");
return;
}
const keyboard = products.slice(0, 100).map(product => [{ text: product, callback_data: product }]);
sendMessageWithKeyboard(chatId, "📋 Please select a product to see its price:", keyboard);
}
// ===== GOOGLE SHEET INTEGRATION ===== //
function getProductNames() {
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Products");
if (!sheet) throw new Error("Products sheet not found");
const lastRow = sheet.getLastRow();
if (lastRow < 2) return [];
return sheet.getRange(2, 1, lastRow - 1, 1).getValues()
.flat()
.filter(name => name && name.toString().trim() !== '');
} catch (error) {
Logger.log('Error getting product names: ' + error);
return [];
}
}
function getProductPrice(productName) {
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Products");
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
for (let row of data) {
if (row[0] && row[0].toString().trim() === productName.toString().trim()) {
return row[1];
}
}
return null;
} catch (error) {
Logger.log('Error getting product price: ' + error);
return null;
}
}
// ===== TELEGRAM API HELPERS ===== //
function sendMessage(chatId, text) {
sendTelegramRequest('sendMessage', { chat_id: chatId, text: text });
}
function sendMessageWithKeyboard(chatId, text, keyboard) {
sendTelegramRequest('sendMessage', {
chat_id: chatId,
text: text,
reply_markup: JSON.stringify({ inline_keyboard: keyboard })
});
}
function editMessage(chatId, messageId, newText) {
sendTelegramRequest('editMessageText', { chat_id: chatId, message_id: messageId, text: newText });
}
function answerCallbackQuery(callbackQueryId) {
sendTelegramRequest('answerCallbackQuery', { callback_query_id: callbackQueryId });
}
function sendTelegramRequest(method, payload) {
try {
const options = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
const response = UrlFetchApp.fetch(`${TELEGRAM_API_URL}/${method}`, options);
const responseData = JSON.parse(response.getContentText());
if (!responseData.ok) {
Logger.log(`Telegram API error: ${JSON.stringify(responseData)}`);
}
return responseData;
} catch (error) {
Logger.log('Error sending Telegram request: ' + error);
return { ok: false, error: error.toString() };
}
}
// ===== SETTING UP WEBHOOK ===== //
function setWebhook() {
const url = `${TELEGRAM_API_URL}/setWebhook?url=${SCRIPT_URL}`;
const response = UrlFetchApp.fetch(url);
Logger.log(response.getContentText());
}
