This guide will walk you through creating a Telegram bot that monitors checkboxes in Google Sheets and sends notifications to a Telegram group chat when a checkbox is checked. You’ll set up a Telegram bot, link it to Google Sheets using Google Apps Script, and configure notifications for specific columns.

Step 1: Create a Telegram Bot

Set Up Your Bot with BotFather

  1. Open Telegram and search for BotFather. BotFather is the official bot for creating and managing Telegram bots.
  2. Start a chat with BotFather and use the command /newbot to create a new bot.
  3. Name your bot. For example, you can name it “Sheet Notifier Bot”.
  4. Set a username for your bot. The username must be unique and end with the word “bot” (e.g., SheetNotifierBot).
  5. Copy the API token provided by BotFather. This token will be used to interact with Telegram’s API. Keep this token secure and don’t share it publicly.

Add the Bot to Your Group

  1. Create a new group or use an existing one in Telegram.
  2. Add your bot to the group.
  3. Promote the bot to an admin if you want it to have additional permissions like deleting messages (not necessary for sending messages).

Step 2: Configure Your Bot to Send Messages to the Group

Get Your Group Chat ID

  • Open your Telegram group chat where you added the bot.
  • Send a message in the group from your personal account.

  • Use the following API request in your browser to get updates from the bot, replacing YOUR_BOT_TOKEN with your bot’s API token:

[code]https://api.telegram.org/botYOUR_BOT_TOKEN/getUpdates[/code]

  • Look for the chat object in the response. You’ll find the id of the group chat; this is your Group Chat ID. Save this for later use.

Step 3: Create Google Apps Script to Monitor Checkboxes

Open Your Google Sheet

  1. Open the Google Sheet that you want to monitor.
  2. Make sure you have checkboxes in the columns you want to monitor. For example, checkboxes in Column D, Column E, etc., each triggering different notifications.

Create Google Apps Script

  1. Go to Extensions > Apps Script in your Google Sheet.
  2. Delete any existing code in the script editor and copy the following script. This script monitors checkboxes and sends a message to your Telegram group when a checkbox is checked.

[code]

// Replace with your Telegram bot token and group chat ID

const TELEGRAM_BOT_TOKEN = ‘YOUR_BOT_TOKEN’;

const CHAT_ID = ‘YOUR_GROUP_CHAT_ID’;

// Function to send a message to the Telegram group

function sendTelegramMessage(message) {

const url = `https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage`;

const payload = {

chat_id: CHAT_ID,

text: message

};

const options = {

method: ‘post’,

contentType: ‘application/json’,

payload: JSON.stringify(payload)

};

UrlFetchApp.fetch(url, options);

}

// Function to monitor checkboxes in the sheet

function onEdit(e) {

const sheet = e.source.getActiveSheet();

const range = e.range;

const column = range.getColumn();

const value = range.getValue();

// Check if the edited cell is a checkbox and it is checked

if (value === true) {

let message = ”;

// Customize the message based on the column

switch (column) {

case 4: // Column D

message = ‘Column D is checked’;

break;

case 5: // Column E

message = ‘Column E is checked’;

break;

// Add more cases for additional columns

default:

return; // Do nothing if it’s not the monitored columns

}

// Send notification to Telegram group

sendTelegramMessage(message);

}

}

[/code]

Save and Deploy the Script

  1. Click on File > Save to save your script.
  2. Name your project (e.g., “Telegram Notification Script”).
  3. Go to Triggers (clock icon on the left sidebar) and click Add Trigger.
  4. Set the trigger:
    • Choose which function to run: Select onEdit.
    • Choose which deployment should run: Head
    • Select event source: From spreadsheet
    • Select event type: On edit
  5. Click Save. The script is now set to monitor checkboxes and send notifications.

Step 4: Test Your Bot

  1. Go to your Google Sheet and check a checkbox in one of the monitored columns (e.g., Column D or Column E).
  2. Check your Telegram group. You should receive a notification like “Column D is checked” or “Column E is checked” depending on which checkbox you checked.

Key Points to Remember

  • Keep your API token secure. If it gets exposed, someone could misuse your bot.
  • Test thoroughly. Make sure your bot is sending the correct notifications by checking each monitored column.
  • Expand as needed. You can easily add more columns and customize messages by extending the switch statement in the script.

This guide should help you create a functional Telegram bot that integrates with Google Sheets to send notifications based on checkbox activity. Happy coding!