Keep a sheet of new videos from channels you follow

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, http, credentials, state, notify

YT = "https://www.googleapis.com/youtube/v3"

def yt(path, **params):
    return http.get(f"{YT}/{path}", params={**params, "key": credentials.secret("YOUTUBE_API_KEY")}).json().get("items", [])

def api(name, version):
    return build(name, version, credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)

SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    sheet = api("sheets", "v4").spreadsheets().values()
    channels = [r[0] for r in sheet.get(spreadsheetId=SHEET_ID, range="Channels!A2:A").execute().get("values", []) if r]
    seen, rows = set(state.get("seen") or []), []
    for ch in yt("channels", part="contentDetails,snippet", id=",".join(channels[:50])):
        uploads = ch["contentDetails"]["relatedPlaylists"]["uploads"]
        for item in yt("playlistItems", part="snippet", playlistId=uploads, maxResults=5):
            s = item["snippet"]
            vid = s["resourceId"]["videoId"]
            if vid not in seen:
                rows.append([s["publishedAt"][:10], ch["snippet"]["title"], s["title"], f"https://youtu.be/{vid}"])
                seen.add(vid)
    if not rows:
        return {"new": 0}
    sheet.append(spreadsheetId=SHEET_ID, range="Videos!A:D", valueInputOption="RAW", body={"values": rows}).execute()
    notify.slack("New videos today:\n" + "\n".join(f"• {r[1]}: {r[2]} {r[3]}" for r in rows), channel="#research")
    state.set("seen", sorted(seen)[-5000:])
    return {"new": len(rows)}