Case Study 24-2: Maya Automates Her Client Status Dashboard with Google Sheets

Characters: - Maya Reyes — Freelance Business Consultant, 3 years in business - Client examples: "Apex Corp" (process improvement engagement), "Birch LLC" (market analysis)

Business Problem: Maya spends several hours per week responding to client emails asking "where are things with my project?" She wants clients to always have access to current status without requiring her to manually send updates.

The Situation

Maya Reyes runs a solo consulting practice. At any given time she has eight to twelve active clients, each at a different stage of a project. Her maya_projects.csv file tracks every project: start date, deadline, completion percentage, last update, and whether the project is on hold.

The problem is the status request emails. Her most common recurring message: "Hi Maya, just wanted to check in on where things stand with [project name]. Looking forward to your update!"

Maya tracked these for one month. She received 23 status request emails in four weeks — roughly one per working day. Each reply took five to fifteen minutes, including context-switching back into the right project headspace. That is roughly two hours per week doing the same communication that a shared document could handle automatically.

Her first instinct was to build a client portal web application. A Google search for "simple client portal Python" sent her down a rabbit hole involving Django, user authentication, databases, deployment, SSL certificates, and ongoing server costs. "This is too much infrastructure for eight clients," she told herself.

Her second instinct was simpler: Google Sheets. Every client already had a Google account. She already had a spreadsheet for internal tracking. The only missing piece was automation — making the sheet update itself from her maya_projects.csv data every night.

The Planning

Maya mapped the information her clients actually needed versus what she tracked internally:

The client-facing sheet would have seven columns and no pricing data. Maya created a second Google Sheet specifically for clients — separate from her internal tracking sheet.

The Implementation

Maya set up Google Cloud authentication over a lunch break using the steps from this chapter. She created a service account, downloaded the credentials JSON, and shared the client-facing sheet with the service account's email address.

Her complete implementation:

"""
maya_client_dashboard.py
Nightly update of client-facing Google Sheet from maya_projects.csv.
Run via Task Scheduler (Windows) or cron (Mac/Linux).
"""

import os
import sys
import logging
import pandas as pd
from datetime import datetime, date
from dotenv import load_dotenv

load_dotenv()

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s — %(levelname)s — %(message)s",
    handlers=[
        logging.StreamHandler(),
        logging.FileHandler("dashboard_update.log"),
    ],
)
logger = logging.getLogger(__name__)

CLIENT_DASHBOARD_SHEET_ID = os.environ.get("CLIENT_DASHBOARD_SHEET_ID")
PROJECTS_CSV_PATH = os.environ.get("PROJECTS_CSV_PATH", "data/maya_projects.csv")


def load_projects(csv_path: str) -> pd.DataFrame:
    """
    Load Maya's project data from CSV.

    Args:
        csv_path: Path to maya_projects.csv.

    Returns:
        pd.DataFrame: Raw project data with parsed dates.

    Raises:
        FileNotFoundError: If the CSV file does not exist.
    """
    if not os.path.exists(csv_path):
        raise FileNotFoundError(
            f"Projects file not found: {csv_path}. "
            "Check PROJECTS_CSV_PATH in your .env file."
        )

    df = pd.read_csv(
        csv_path,
        parse_dates=["start_date", "deadline", "last_update"],
    )
    logger.info(f"Loaded {len(df)} projects from {csv_path}")
    return df


def compute_client_view(df: pd.DataFrame) -> pd.DataFrame:
    """
    Transform raw project data into a client-appropriate view.

    Adds derived columns and removes internal/sensitive columns.

    Args:
        df: Raw project DataFrame from load_projects().

    Returns:
        pd.DataFrame: Client-facing view with status labels.
    """
    today = date.today()

    df["days_remaining"] = df["deadline"].apply(
        lambda d: (d.date() - today).days
    )

    df["status_label"] = df.apply(
        lambda row: _status_label(
            pct=row["completion_percent"],
            days=row["days_remaining"],
            on_hold=bool(row.get("on_hold", False)),
        ),
        axis=1,
    )

    df["dashboard_updated"] = datetime.now().strftime("%Y-%m-%d %H:%M")

    # Format dates as readable strings for the sheet
    df["start_date"] = df["start_date"].dt.strftime("%b %d, %Y")
    df["deadline"] = df["deadline"].dt.strftime("%b %d, %Y")

    client_columns = [
        "project_name",
        "client_name",
        "start_date",
        "deadline",
        "completion_percent",
        "days_remaining",
        "status_label",
        "dashboard_updated",
    ]

    # Only include columns that actually exist in the DataFrame
    available_columns = [col for col in client_columns if col in df.columns]
    return df[available_columns]


def _status_label(pct: float, days: int, on_hold: bool) -> str:
    """
    Compute a plain-English project status label.

    Args:
        pct: Completion percentage (0 to 100).
        days: Days remaining until deadline (negative if overdue).
        on_hold: Whether the project is paused.

    Returns:
        str: Status label string.
    """
    if on_hold:
        return "On Hold"
    if pct >= 100:
        return "Complete"
    if days < 0:
        return "Overdue"
    if days < 7 and pct < 80:
        return "At Risk"
    return "On Track"


def log_status_summary(df: pd.DataFrame) -> None:
    """Log a count of projects in each status category."""
    counts = df["status_label"].value_counts()
    for status, count in counts.items():
        logger.info(f"  {status}: {count} project(s)")


def update_client_dashboard() -> bool:
    """
    Load project data and push the client view to Google Sheets.

    Returns:
        bool: True if update succeeded, False if it failed.
    """
    if not CLIENT_DASHBOARD_SHEET_ID:
        logger.error("CLIENT_DASHBOARD_SHEET_ID not set in .env")
        return False

    try:
        raw_df = load_projects(PROJECTS_CSV_PATH)
        client_df = compute_client_view(raw_df)

        log_status_summary(client_df)

        from sheets_client import dataframe_to_sheet
        dataframe_to_sheet(
            df=client_df,
            spreadsheet_id=CLIENT_DASHBOARD_SHEET_ID,
            worksheet_name="Project Status",
            clear_existing=True,
        )

        sheet_url = (
            f"https://docs.google.com/spreadsheets/d/{CLIENT_DASHBOARD_SHEET_ID}"
        )
        logger.info(f"Dashboard updated successfully.")
        logger.info(f"View at: {sheet_url}")
        return True

    except Exception as e:
        logger.error(f"Dashboard update failed: {e}")
        return False


if __name__ == "__main__":
    success = update_client_dashboard()
    sys.exit(0 if success else 1)

Setting Up the Nightly Schedule

On Windows, Maya used Task Scheduler: 1. Open Task Scheduler 2. Create a new task: "Maya Client Dashboard Update" 3. Trigger: Daily at 11:00 PM 4. Action: Start a program — python.exe maya_client_dashboard.py 5. Working directory: the project folder

On Mac or Linux, she would use cron:

# Run at 11 PM every day
0 23 * * * cd /home/maya/consulting && python maya_client_dashboard.py

The script exits with code 0 on success or 1 on failure, which Task Scheduler and cron can use to detect problems.

Client Communication

Maya sent each client a single email with the Google Sheet link and instructions:

"I've set up a shared project dashboard that updates nightly with current status on your project(s). Bookmark this link for instant status updates without waiting for my reply: [link]"

"The sheet shows completion percentage, days remaining, and a status summary. I'll still send you milestone emails, but for quick status checks, the dashboard is the fastest way to see where things stand."

Eight of twelve clients bookmarked the link immediately. Three said they would still prefer email updates — Maya continued sending those manually. One client forgot about it entirely and still emails, which Maya now considers "fine, it only takes 30 seconds to paste the link."

What Maya Learned

The data preparation was more work than the API integration. Getting the status logic right — deciding when a project is "At Risk" versus "Overdue" versus "On Track" — required thinking carefully about what her clients actually needed to see. The API integration itself (authenticate, push data) was about fifteen lines of code once the credentials were set up.

Column selection matters for client-facing data. Her first draft accidentally included a billing_rate column. She caught it during a dry-run review before the first real push. The lesson: always print the client DataFrame and review it manually before the first live update.

The service account email was confusing at first. Maya spent twenty minutes wondering why her script could not find her spreadsheet, then realized she had forgotten to share the sheet with the service account's email address. Once shared, it worked immediately.

Clients appreciate reliability more than features. The sheet is simple — seven columns, no charts, no conditional formatting. But because it updates every night reliably, clients have started trusting it. One client told Maya: "I check it every morning before I check email."

Outcome

Maya's status request emails dropped from 23 per month to 7 per month — the three clients who prefer email plus occasional questions from the others. She calculates that at $175 per hour, the two hours per week she recovers is worth $350 weekly in reclaimed billable time, though she notes the real value is the reduced context-switching.

She has since expanded the system: a second tab on the same sheet shows completed projects with final delivery dates, giving clients a record of work done. Maya updates this tab manually once per week — a two-minute task.

Discussion Questions

1. Maya chose not to include billing rate or invoiced amounts in the client-facing sheet. What technical mechanism (beyond just not including those columns) could she add to ensure confidential columns can never accidentally be published?

2. The status logic marks a project "At Risk" if it has fewer than 7 days remaining and is less than 80% complete. What business situations might this threshold miss or misclassify? How would you make the threshold configurable without changing the code?

3. Maya shares one Google Sheet with all clients. Should each client see all projects or only their own? What code change would produce a per-client sheet instead of a shared one?

4. The script logs to a file called dashboard_update.log. After 30 days of nightly runs, this file might grow large. Write a brief design for a log rotation strategy.