Transcribe audio uploaded to S3 into a Google Sheet

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

SHEET_ID = "your-spreadsheet-id"
REGION, BUCKET = "us-east-1", "northwind-files"

def aws(service):
    return boto3.client(service, region_name=REGION, aws_access_key_id=credentials.secret("AWS_ACCESS_KEY_ID"),
                        aws_secret_access_key=credentials.secret("AWS_SECRET_ACCESS_KEY"))

@on_trigger
def run(event):
    s3, tr = aws("s3"), aws("transcribe")
    jobs = state.get("jobs") or {}
    keys = [o["Key"] for o in s3.list_objects_v2(Bucket=BUCKET, Prefix="audio/").get("Contents", []) if o["Key"].endswith((".mp3", ".wav", ".m4a"))]
    new = [k for k in keys if k not in jobs]
    report_fetched(len(new) + sum(1 for v in jobs.values() if v != "done"))
    for k in new:
        jobs[k] = "cr-" + k.replace("/", "-").rsplit(".", 1)[0][:180]
        tr.start_transcription_job(TranscriptionJobName=jobs[k], Media={"MediaFileUri": f"s3://{BUCKET}/{k}"}, IdentifyLanguage=True)
    rows = []
    for k, name in jobs.items():
        if name == "done" or k in new:
            continue
        job = tr.get_transcription_job(TranscriptionJobName=name)["TranscriptionJob"]
        if job["TranscriptionJobStatus"] in ("COMPLETED", "FAILED"):
            text = http.get(job["Transcript"]["TranscriptFileUri"]).json()["results"]["transcripts"][0]["transcript"] if job["TranscriptionJobStatus"] == "COMPLETED" else "(failed)"
            rows.append([k, job["CompletionTime"].isoformat()[:16], text[:45000]])
            jobs[k] = "done"
    if rows:
        build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Transcripts!A:C", valueInputOption="RAW", body={"values": rows}).execute()
    state.set("jobs", jobs)
    return {"started": len(new), "logged": len(rows)}