Export YouTube video stats to Google Sheets

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
import re
from clickruntime import on_trigger, credentials, report_fetched

SHEET_ID = "your-spreadsheet-id"

def duration(iso):
    h, m, s = (int(x or 0) for x in re.match(r"PT(?:(\d+)H)?(?:(\d+)M)?(?:(\d+)S)?", iso).groups())
    return f"{h}:{m:02}:{s:02}" if h else f"{m}:{s:02}"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    yt = build("youtube", "v3", credentials=creds, cache_discovery=False)
    uploads = yt.channels().list(part="contentDetails", mine=True).execute()["items"][0]["contentDetails"]["relatedPlaylists"]["uploads"]
    ids, token = [], None
    while True:
        page = yt.playlistItems().list(part="contentDetails", playlistId=uploads, maxResults=50, pageToken=token).execute()
        ids += [i["contentDetails"]["videoId"] for i in page["items"]]
        token = page.get("nextPageToken")
        if not token:
            break
    report_fetched(len(ids))
    rows = []
    for i in range(0, len(ids), 50):
        for v in yt.videos().list(part="snippet,statistics,contentDetails", id=",".join(ids[i:i + 50])).execute()["items"]:
            s = v["statistics"]
            rows.append([v["snippet"]["title"], v["snippet"]["publishedAt"][:10], duration(v["contentDetails"]["duration"]),
                         int(s.get("viewCount", 0)), int(s.get("likeCount", 0)), int(s.get("commentCount", 0)), f"https://youtu.be/{v['id']}"])
    rows.sort(key=lambda r: -r[3])
    tab = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values()
    tab.clear(spreadsheetId=SHEET_ID, range="Videos!A:G").execute()
    tab.update(spreadsheetId=SHEET_ID, range="Videos!A1", valueInputOption="RAW", body={"values": [
        ["Title", "Published", "Duration", "Views", "Likes", "Comments", "Link"]] + rows}).execute()
    return {"videos": len(rows)}