基于Python与Git开发集成Google Sheets的Slack Bot需求问询

Hey Kenny, let's break down how to build this Slack sales tracking bot step by step—this is totally doable with Python and a few key libraries. Here's a practical implementation guide tailored to your needs:

1. Pre-Requisites First #

Before writing code, get these setup out of the way:

• Slack Bot Setup: Create a Slack App, enable Bot User, and grab your SLACK_BOT_TOKEN and SLACK_APP_TOKEN from the Slack API dashboard.
• Google Sheets API: Enable the Google Sheets API in the Google Cloud Console, download the service account JSON key, and share your target Google Sheet with the service account's email (found in the JSON file).
• Sheet Structure: Create 3 worksheets in your Google Sheet:
  • Weekly Goals (columns: Date, User ID, Weekly Goal)
  • Daily Goals (columns: Date, User ID, Daily Goal)
  • Daily Results (columns: Date, User ID, Daily Sales)

2. Core Code Implementation (Python) #

We'll use slack-sdk for Slack interactions, gspread for Google Sheets sync, and APScheduler for timed tasks. Install dependencies first:

pip install slack-sdk python-dotenv gspread oauth2client apscheduler

2.1 Base Setup & Client Initialization #

import os
from datetime import datetime
from dotenv import load_dotenv
from slack_sdk import WebClient
from slack_sdk.errors import SlackApiError
from slack_sdk.events import EventAdapter
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from apscheduler.schedulers.blocking import BlockingScheduler

# Load environment variables (store tokens in a .env file)
load_dotenv()
SLACK_BOT_TOKEN = os.getenv("SLACK_BOT_TOKEN")
SLACK_APP_TOKEN = os.getenv("SLACK_APP_TOKEN")
GOOGLE_CREDS_PATH = os.getenv("GOOGLE_CREDS_PATH")
SHEET_ID = os.getenv("SHEET_ID")

# Initialize Slack clients
slack_client = WebClient(token=SLACK_BOT_TOKEN)
event_adapter = EventAdapter(SLACK_APP_TOKEN, "/slack/events")

# Initialize Google Sheets client
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name(GOOGLE_CREDS_PATH, scope)
gc = gspread.authorize(creds)
sheet = gc.open_by_key(SHEET_ID)

# Track user interaction state (e.g., waiting for weekly goal input)
user_state = {}

2.2 Timed Trigger Functions #

These handle sending scheduled prompts to users:

def send_weekly_goal_prompt(user_id: str):
    """Send weekly goal request every Monday 9 AM"""
    try:
        slack_client.chat_postMessage(
            channel=user_id,
            text="Hey there! Could you share your weekly sales target number today?"
        )
        user_state[user_id] = "waiting_weekly_goal"
    except SlackApiError as e:
        print(f"Failed to send weekly prompt: {e.response['error']}")

def send_daily_sales_prompt(user_id: str):
    """Send daily sales count request every night 9 PM"""
    try:
        slack_client.chat_postMessage(
            channel=user_id,
            text="Evening! Please input your daily completed sales count."
        )
        user_state[user_id] = "waiting_daily_sales"
    except SlackApiError as e:
        print(f"Failed to send daily prompt: {e.response['error']}")

2.3 Handle User Responses #

Listen for user messages and process them based on their current interaction state:

@event_adapter.on("message")
def handle_user_message(event_data):
    message = event_data["event"]
    user_id = message.get("user")
    text = message.get("text", "").strip()

    # Ignore messages from bots
    if message.get("bot_id"):
        return

    current_state = user_state.get(user_id)

    # Process weekly goal input
    if current_state == "waiting_weekly_goal":
        if text.isdigit():
            weekly_goal = int(text)
            # Confirm to user
            slack_client.chat_postMessage(
                channel=user_id,
                text=f"Awesome! Your weekly sales target is set to *{weekly_goal}*."
            )
            # Sync to Google Sheets
            weekly_sheet = sheet.worksheet("Weekly Goals")
            weekly_sheet.append_row([datetime.now().strftime("%Y-%m-%d"), user_id, weekly_goal])
            # Prompt for daily goal
            user_state[user_id] = "waiting_daily_goal"
            slack_client.chat_postMessage(
                channel=user_id,
                text="Now, could you share your daily sales target?"
            )
        else:
            slack_client.chat_postMessage(
                channel=user_id,
                text="Oops, please enter a valid number for your weekly target!"
            )

    # Process daily goal input
    elif current_state == "waiting_daily_goal":
        if text.isdigit():
            daily_goal = int(text)
            slack_client.chat_postMessage(
                channel=user_id,
                text=f"Got it! Your daily sales target is set to *{daily_goal}*."
            )
            # Sync to Google Sheets
            daily_goal_sheet = sheet.worksheet("Daily Goals")
            daily_goal_sheet.append_row([datetime.now().strftime("%Y-%m-%d"), user_id, daily_goal])
            user_state.pop(user_id, None)
        else:
            slack_client.chat_postMessage(
                channel=user_id,
                text="Oops, please enter a valid number for your daily target!"
            )

    # Process daily sales count input
    elif current_state == "waiting_daily_sales":
        if text.isdigit():
            daily_sales = int(text)
            slack_client.chat_postMessage(
                channel=user_id,
                text=f"Thanks! I've recorded your daily sales count as *{daily_sales}*."
            )
            # Sync to Google Sheets
            daily_results_sheet = sheet.worksheet("Daily Results")
            daily_results_sheet.append_row([datetime.now().strftime("%Y-%m-%d"), user_id, daily_sales])
            # Add your planned follow-up logic here (e.g., completion rate calculation)
            user_state.pop(user_id, None)
        else:
            slack_client.chat_postMessage(
                channel=user_id,
                text="Oops, please enter a valid number for your daily sales count!"
            )

2.4 Start Schedulers & Event Listener #

if __name__ == "__main__":
    # Initialize scheduler with your timezone (adjust as needed)
    scheduler = BlockingScheduler(timezone="Asia/Shanghai")

    # Schedule weekly prompt (Monday 9 AM)
    scheduler.add_job(
        send_weekly_goal_prompt,
        trigger="cron",
        day_of_week="mon",
        hour=9,
        minute=0,
        args=["YOUR_USER_OR_CHANNEL_ID"]  # Replace with target user/channel ID
    )

    # Schedule daily sales prompt (9 PM every day)
    scheduler.add_job(
        send_daily_sales_prompt,
        trigger="cron",
        hour=21,
        minute=0,
        args=["YOUR_USER_OR_CHANNEL_ID"]
    )

    # Start Slack event listener (run on port 3000; configure Slack event subscription URL to http://your-server-ip:3000/slack/events)
    event_adapter.start(port=3000)
    # Start scheduler
    scheduler.start()

3. Key Production Tips #

• State Persistence: Replace the user_state dictionary with a lightweight database (like SQLite) if you have multiple users—this avoids losing state when the bot restarts.
• Error Handling: Add retries for Google Sheets writes and Slack API calls to handle network blips.
• User List Management: If you need to send prompts to multiple users, store their IDs in a database and loop through them in the timed functions.

内容的提问来源于stack exchange，提问作者Kenny Patel