check_missing_reports

Identify team members who have not submitted their weekly reports by checking submission status in Google Sheets.

Instructions

Check who hasn't submitted their weekly reports yet

Input Schema

TableJSON Schema

Name	Required	Description	Default
No arguments

Implementation Reference

mcp_server/weekly_report_server.py:68-81 (handler)

The core handler function for the 'check_missing_reports' tool. Decorated with @mcp.tool() for automatic registration in the FastMCP server. It calls the helper get_report_data() to fetch submission status from Google Sheets, computes missing reports based on NAME_LIST, and returns a formatted string listing missing submitters or '無' if all submitted.

@mcp.tool()
def check_missing_reports() -> str:
    """Check who hasn't submitted their weekly reports yet"""
    report_data = get_report_data()
    
    # Get names of people who haven't submitted
    missing_names = [name for name in NAME_LIST if not report_data[name]["submitted"]]
    
    # Format the response
    if missing_names:
        return f"本週未寫週報名單：{', '.join(missing_names)}"
    else:
        return "本週未寫週報名單：無"

mcp_server/weekly_report_server.py:17-65 (helper)

Key helper function that retrieves and processes weekly report submission data from a Google Sheet named '週報'. Initializes status for all members in NAME_LIST, parses recent rows for timestamps and names, determines if submissions are recent (within ~6.5 days), and populates detailed status dictionary used by the tool handler.

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

mcp_server/weekly_report_server.py:11-11 (helper)
Constant list of team member names expected to submit weekly reports, used by get_report_data() and the handler to check for missing submissions.
```
NAME_LIST = ["陳冠宇", "林柏志", "潘班", "董屹煊", "王宇軒", "許圃瑄", "陳冠言", "黃祈緯", "黃渝凌"]
```
mcp_server/weekly_report_server.py:68-68 (registration)
The @mcp.tool() decorator registers the check_missing_reports function as an MCP tool in the FastMCP server instance.
```
@mcp.tool()
```

Weekly Report Checker