Sync active Stripe subscriptions to Google Sheets

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from datetime import datetime, timezone
from clickruntime import on_trigger, credentials, http, report_fetched

SHEET_ID = "your-spreadsheet-id"
KEEP = {"active", "trialing", "past_due"}
PER_MONTH = {"day": 30, "week": 52 / 12, "month": 1, "year": 1 / 12}

@on_trigger
def run(event):
    key, subs, after = credentials.secret("STRIPE_API_KEY"), [], None
    while True:
        params = {"status": "all", "limit": 100, "expand[]": "data.customer", **({"starting_after": after} if after else {})}
        page = http.get("https://api.stripe.com/v1/subscriptions", params=params, auth=(key, "")).json()
        subs += [s for s in page["data"] if s["status"] in KEEP]
        if not page["has_more"]:
            break
        after = page["data"][-1]["id"]
    report_fetched(len(subs))
    rows, mrr = [], 0.0
    for s in subs:
        monthly = 0.0
        for item in s["items"]["data"]:
            rec = item["price"]["recurring"]
            monthly += (item["price"]["unit_amount"] or 0) / 100 * item.get("quantity", 1) * PER_MONTH[rec["interval"]] / rec["interval_count"]
        mrr += monthly if s["status"] != "trialing" else 0
        renew = datetime.fromtimestamp(s["items"]["data"][0]["current_period_end"], timezone.utc).date().isoformat()
        plan = s["items"]["data"][0]["price"].get("nickname") or s["items"]["data"][0]["price"]["id"]
        rows.append([s["customer"].get("email") or s["customer"]["id"], plan, round(monthly, 2), s["status"], renew, s["id"]])
    rows.sort(key=lambda r: -r[2])
    values = [["Customer", "Plan", "Monthly amount", "Status", "Renews", "Subscription"]] + rows + [["Total MRR", "", round(mrr, 2)]]
    tab = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()
    tab.clear(spreadsheetId=SHEET_ID, range="Subscriptions!A:F").execute()
    tab.update(spreadsheetId=SHEET_ID, range="Subscriptions!A1", valueInputOption="USER_ENTERED", body={"values": values}).execute()
    return {"subscriptions": len(rows), "mrr": round(mrr, 2)}