Monthly Stripe tax summary by country in a sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from datetime import datetime, timezone
from collections import defaultdict
import stripe
from clickruntime import on_trigger, credentials, notify

SHEET_ID = "your-sheet-id"

@on_trigger
def run(event):
    stripe.api_key = credentials.secret("STRIPE_API_KEY")
    end = datetime.now(timezone.utc).replace(day=1, hour=0, minute=0, second=0, microsecond=0)
    start = (end.replace(day=1) - timedelta(days=1)).replace(day=1)
    totals = defaultdict(lambda: [0, 0])
    for inv in stripe.Invoice.list(status="paid", created={"gte": int(start.timestamp()), "lt": int(end.timestamp())}, limit=100).auto_paging_iter():
        country = (inv.customer_address or {}).get("country") or "unknown"
        for t in inv.total_tax_amounts:
            rate = stripe.TaxRate.retrieve(t.tax_rate).percentage
            key = (country, rate, inv.currency.upper())
            totals[key][0] += t.taxable_amount or 0
            totals[key][1] += t.amount
    tab = start.strftime("%Y-%m")
    rows = [["Country", "Rate %", "Currency", "Taxable", "Tax"]] + [[c, r, cur, v[0] / 100, v[1] / 100] for (c, r, cur), v in sorted(totals.items())]
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    sheets.batchUpdate(spreadsheetId=SHEET_ID, body={"requests": [{"addSheet": {"properties": {"title": tab}}}]}).execute()
    sheets.values().update(spreadsheetId=SHEET_ID, range=f"{tab}!A1", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    notify.slack(f"Tax summary for {tab} is in the sheet: {len(totals)} country and rate lines.", channel="#finance")
    return {"lines": len(totals)}