Send a weekly Telegram message to every chat in a sheet

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

SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    token = credentials.secret("TELEGRAM_BOT_TOKEN")
    values = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")),
                   cache_discovery=False).spreadsheets().values()
    message = values.get(spreadsheetId=SHEET_ID, range="Broadcast!B1").execute()["values"][0][0]
    rows = values.get(spreadsheetId=SHEET_ID, range="Broadcast!A3:B").execute().get("values", [])
    today, sent, statuses = date.today().isoformat(), 0, []
    for i, row in enumerate(rows):
        status = row[1] if len(row) > 1 else ""
        if row and not status.startswith("sent"):
            r = http.post(f"https://api.telegram.org/bot{token}/sendMessage", json={"chat_id": row[0], "text": message})
            status = f"sent {today}" if r.ok else f"error {r.status_code}"
            sent += r.ok
            if i % 20 == 19:
                time.sleep(1)
        statuses.append([status])
    values.update(spreadsheetId=SHEET_ID, range="Broadcast!B3", valueInputOption="RAW", body={"values": statuses}).execute()
    return {"chats": len(rows), "sent": sent}