Creating a Telegram Bot to Manage Financial Transactions with Google Sheets Integration

In this tutorial, we’ll walk you through how to create a simple Telegram bot that can help you manage financial transactions. The bot will interact with a Google Sheets document to store and retrieve data. This project is ideal for anyone looking to automate their personal finance tracking or even manage small business transactions.

Prerequisites

Before you begin, make sure you have the following:

1. Python Installed: You’ll need Python 3.6 or later.
2. Google Account: Access to Google Sheets.
3. Telegram Account: To create and interact with the bot.
4. Libraries: Install the necessary Python libraries:

pip install pyTelegramBotAPI gspread oauth2client

Step 1: Setting Up Google Sheets API

1. Create a new Google Sheet: This will be where your transactions are logged.
2. Enable Google Sheets API: Go to the Google Cloud Console, create a new project, and enable the Google Sheets API.
3. Create Service Account Credentials:

• Navigate to “APIs & Services” > “Credentials”.
• Create a new service account and download the JSON file. This file will be used to authorize your Python script to access Google Sheets.

4. Share Your Sheet: Share the Google Sheet with the service account email found in your JSON file.

Step 2: Create Your Telegram Bot

1. Create a Bot on Telegram:

• Open Telegram and search for the “BotFather”.
• Use the command /newbot and follow the instructions to create a bot. You'll get a token that looks like this: 123456789:ABCDEF1234567890abcdef1234567890.

Install the Required Libraries:

pip install pyTelegramBotAPI

Step 3: Writing the Python Script

The following Python script sets up a Telegram bot that can handle basic financial transactions by interacting with a Google Sheet.

import os
import telebot
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime

# Set up your bot token and Google credentials
BOT_TOKEN = "YOUR_BOT_TOKEN_HERE"
bot = telebot.TeleBot(BOT_TOKEN)

# Setup for Google Sheets
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('telegrambot.json', scope)
client = gspread.authorize(creds)
sheet = client.open_by_key('YOUR_SHEET_KEY').sheet1

# Category mapping
category_mapping = {
    'saveadcb': 'Savings (ADCB)',
    'savefab': 'Savings (FAB)',
    'withdrawadcb': 'Withdraw (ADCB)',
    'withdrawfab': 'Withdraw (FAB)',
    'onhand': 'On Hand',
    'payhand': 'Pay Hand',
    'otherincome': 'Other Income',
    'other': 'Other'
}

# Handlers for Telegram commands
@bot.message_handler(commands=['start', 'hello'])
def send_welcome(message):
    bot.reply_to(message, "Howdy, how are you doing?")

@bot.message_handler(commands=['add'])
def add_transaction(message):
    # Send immediate feedback to the user
    bot.reply_to(message, "Adding Data . . .")
    try:
        # Parsing the message text
        parts = message.text.split(maxsplit=3)
        if len(parts) < 4:
            bot.reply_to(message, "Please use the format: /add description category +/-amount")
            return
        description = parts[1].capitalize()
        category_key = parts[2].lower()
        amount = parts[3]

        # Validate and map the category
        category = category_mapping.get(category_key, None)
        if not category:
            bot.reply_to(message, f"Invalid category. Available categories: {', '.join(category_mapping.keys())}")
            return

        # Determine if the transaction is income or debit
        if amount.startswith('+'):
            income = amount[1:]
            debit = ""
        elif amount.startswith('-'):
            income = ""
            debit = amount[1:]
        else:
            bot.reply_to(message, "Amount must start with '+' or '-' to indicate income or debit.")
            return

        # Find the next empty row based on column B being empty
        column_b_values = sheet.col_values(2)
        next_empty_row = len(column_b_values) + 1

        # Write to the spreadsheet
        sheet.update_cell(next_empty_row, 2, datetime.now().strftime('%d-%b-%Y'))
        sheet.update_cell(next_empty_row, 3, description)
        sheet.update_cell(next_empty_row, 4, category)
        sheet.update_cell(next_empty_row, 5, income)
        sheet.update_cell(next_empty_row, 6, debit)
        bot.reply_to(message, f"Added: {description}, {category}, Income: {income}, Debit: {debit}")
    except Exception as e:
        bot.reply_to(message, f"Failed to add transaction: {str(e)}")

@bot.message_handler(commands=['delete'])
def delete_last_transaction(message):
    bot.reply_to(message, "Deleting Data . . .")
    try:
        column_b_values = sheet.col_values(2)
        while column_b_values and column_b_values[-1] == '':
            column_b_values.pop()
        
        last_non_empty_row = len(column_b_values)
        if last_non_empty_row == 0:
            bot.reply_to(message, "No data to delete.")
            return

        range_name = f'B{last_non_empty_row}:F{last_non_empty_row}'
        values = [['', '', '', '', '']]
        sheet.update(range_name=range_name, values=values)
        
        bot.reply_to(message, "Last transaction deleted successfully.")
    except Exception as e:
        bot.reply_to(message, f"Failed to delete the last transaction: {str(e)}")

@bot.message_handler(commands=['view'])
def view_last_updates(message):
    bot.reply_to(message, "Viewing Data . . .")
    try:
        all_rows = sheet.get_all_values()
        filled_rows = [row for row in all_rows if row[1].strip()]

        last_filled_rows = filled_rows[-5:]

        response = "<b>Last 5 Filled Updates:</b>\n"
        response += "<code>Date    | Desc.       | Category    | Amount | Income | Debit</code>\n"
        for row in last_filled_rows:
            formatted_row = [cell.strip() for cell in row[1:7]] + [''] * 5
            response += f"<code>{formatted_row[0]:<8}| {formatted_row[1]:<11}| {formatted_row[2]:<12}| {formatted_row[3]:<6}| {formatted_row[4]:<6}| {formatted_row[5]:<5}</code>\n"

        bot.send_message(chat_id=message.chat.id, text=response, parse_mode='HTML')
    except Exception as e:
        bot.reply_to(message, f"Failed to view updates: {str(e)}")

@bot.message_handler(commands=['summary'])
def send_summary(message):
    try:
        range_name = 'M4:N15'
        summary_data = sheet.get(range_name)

        response = "<b>Financial Summary:</b>\n"
        response += "<code>Category            | Amount</code>\n"
        for row in summary_data:
            category = row[0] if len(row) > 0 else ''
            amount = row[1] if len(row) > 1 else ''
            response += f"<code>{category:<20} | {amount}</code>\n"

        bot.send_message(chat_id=message.chat.id, text=response, parse_mode='HTML')
    except Exception as e:
        bot.reply_to(message, f"Failed to fetch summary: {str(e)}")

@bot.message_handler(commands=['commands'])
def send_commands(message):
    commands_text = """
<b>Available Commands:</b>
/start - Start interaction with the bot.
/hello - Greet the bot.
/add - Add a new entry. Format: /add description category +/-amount
/delete - Delete the last transaction.
/view - View the last 5 filled transactions.
/summary - View financial summary from the spreadsheet.
/commands - List all available commands.

<b>Usage Example:</b>
To add data: /add Lunch taxi +15
To view data: /view
To delete the last entry: /delete
To view summary: /summary
    """
    bot.send_message(chat_id=message.chat.id, text=commands_text, parse_mode='HTML')

@bot.message_handler(func=lambda msg: True)
def echo_all(message):
    bot.reply_to(message, message.text)

# Run the bot
bot.infinity_polling()

Explanation of the Code:

• Initialization: The bot token is initialized and connected to the Telegram API using the telebot library.
• Google Sheets Setup: The bot uses the gspread library to interact with Google Sheets. The sheet is authorized using the JSON credentials file.
• Command Handlers:
• /add: Adds a new transaction to Google Sheets based on the user's input.
• /delete: Deletes the last transaction from the Google Sheet.
• /view: Displays the last 5 transactions in the chat.
• /summary: Retrieves and displays a financial summary from the Google Sheet.
• /commands: Lists all available commands for user guidance.

Running the Bot

To run your bot, simply execute the Python script:

python run.py

Your bot will start running and will listen for messages on Telegram. You can interact with it using the commands defined in the script.

Conclusion

By following this tutorial, you’ve built a fully functional Telegram bot that integrates with Google Sheets to manage financial transactions. This bot can be customized further to suit your specific needs, such as adding more categories, enhancing the summary feature, or even integrating with other APIs for more complex functionality.

Happy coding!