Fill your timesheet from the emails you sent today

from datetime import date
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, ai

SHEET_ID = "your-timesheet-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    gmail = build("gmail", "v1", credentials=creds, cache_discovery=False)
    ids = gmail.users().messages().list(userId="me", q="in:sent newer_than:1d", maxResults=100).execute().get("messages", [])
    lines = []
    for m in ids:
        msg = gmail.users().messages().get(userId="me", id=m["id"], format="metadata", metadataHeaders=["To", "Subject"]).execute()
        h = {x["name"]: x["value"] for x in msg["payload"]["headers"]}
        lines.append(f"To {h.get('To')}: {h.get('Subject')} ({msg.get('snippet', '')[:120]})")
    if not lines:
        return {"rows": 0}
    schema = {"type": "object", "properties": {"rows": {"type": "array", "items": {"type": "object", "properties": {
        "client": {"type": "string"}, "summary": {"type": "string"}, "minutes": {"type": "integer"}}}}}}
    groups = ai.generate("Group these sent emails by client or project and estimate minutes spent:\n" + "\n".join(lines),
                         json_schema=schema, max_output_tokens=1000).json["rows"]
    rows = [[date.today().isoformat(), g["client"], g["summary"], g["minutes"]] for g in groups]
    build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values().append(
        spreadsheetId=SHEET_ID, range="Timesheet!A:D", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    return {"emails": len(lines), "rows": len(rows)}