get_submission_stats
Retrieve statistics about weekly report submissions to monitor completion rates and identify missing reports in Google Sheets.
Instructions
Get statistics about weekly report submissions
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| result | Yes |
Implementation Reference
- The handler function for the 'get_submission_stats' tool. It is decorated with @mcp.tool(), which registers it as an MCP tool. Computes submission statistics by calling the helper get_report_data() and formats the result as a string.
@mcp.tool() def get_submission_stats() -> str: """Get statistics about weekly report submissions""" report_data = get_report_data() # Calculate statistics total = len(NAME_LIST) submitted = sum(1 for name in NAME_LIST if report_data[name]["submitted"]) return f"""週報提交統計： 已提交：{submitted}/{total} ({round(submitted/total*100 if total else 0, 1)}%) """ - Shared helper function used by get_submission_stats (and other tools) to fetch and process weekly report submission data from Google Sheets.
def get_report_data() -> Dict[str, Dict]: """Helper function to get report data from Google Sheets""" # Connect to Google Sheets sa = gspread.service_account(filename=SERVICE_ACCOUNT_FILE) sh = sa.open("週報") wks = sh.worksheet("週報") # Get current time current_time = datetime.datetime.now() # Dictionary to store report data for each person report_data = {name: { "submitted": False, "timestamp": None, "content": None, "days_ago": None } for name in NAME_LIST} # Check each row in the sheet for i in range(2, 15): # Assuming data starts from row 2 and goes to row 14 try: row = wks.get(f"A{i}:F{i}") if not row or not row[0][0]: # Skip empty rows continue # Parse the timestamp from the sheet item_time = datetime.datetime.strptime(row[0][0], '%m/%d/%Y %H:%M:%S') name = row[0][2] # Assuming name is in column C # Skip if the name is not in our list if name not in report_data: continue # Calculate days ago delta_sec = (current_time - item_time).total_seconds() days_ago = delta_sec / 86400 # Convert seconds to days # Check if the report was submitted within the last 6 days (518400 seconds + 12 hours buffer) if delta_sec < (518400 + 43200): report_data[name] = { "submitted": True, "timestamp": item_time.strftime('%Y-%m-%d %H:%M:%S'), "content": row[0][3] if len(row[0]) > 3 else "No content", # Assuming content is in column D "days_ago": round(days_ago, 1) } except Exception: continue return report_data