Fill cancelled appointments from your waitlist sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, notify, state, report_fetched
SHEET_ID = "your-waitlist-sheet-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    params = {"calendarId": "primary", "showDeleted": True, "singleEvents": True}
    if state.get("sync"):
        params["syncToken"] = state.get("sync")
    resp = build("calendar", "v3", credentials=creds, cache_discovery=False).events().list(**params).execute()
    cancelled = [e for e in resp.get("items", []) if e.get("status") == "cancelled" and "start" in e]
    report_fetched(len(cancelled))
    if not cancelled:
        state.set("sync", resp.get("nextSyncToken"))
        return {"cancelled": 0}
    values = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values()
    rows = values.get(spreadsheetId=SHEET_ID, range="Waitlist!A2:C").execute().get("values", [])
    free = [i for i, r in enumerate(rows) if len(r) < 3 or not r[2]]
    for e in cancelled:
        when = e["start"].get("dateTime", e["start"].get("date"))
        if not free:
            notify.email_me(f"Cancelled: {when}", "The waitlist is empty.")
            continue
        i = free.pop(0)
        values.update(spreadsheetId=SHEET_ID, range=f"Waitlist!C{i + 2}", valueInputOption="RAW", body={"values": [["offered"]]}).execute()
        notify.email_me(f"Cancelled: {when}", f"Offer it to {rows[i][0]} ({rows[i][1]}).")
    state.set("sync", resp.get("nextSyncToken"))
    return {"cancelled": len(cancelled)}