Import new Drive CSV files into Google Sheets

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

FOLDER_ID = "your-folder-id"
SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    drive = build("drive", "v3", credentials=creds, cache_discovery=False).files()
    done = set(state.get("imported") or [])
    files = drive.list(q=f"'{FOLDER_ID}' in parents and mimeType = 'text/csv' and trashed = false",
                       orderBy="createdTime", fields="files(id,name)", pageSize=100).execute()["files"]
    new = [f for f in files if f["id"] not in done]
    report_fetched(len(new))
    if not new:
        return {"imported": 0}
    tab = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values()
    added = 0
    for f in new:
        buf = io.BytesIO()
        loader = MediaIoBaseDownload(buf, drive.get_media(fileId=f["id"]))
        while not loader.next_chunk()[1]:
            pass
        rows = list(csv.reader(io.StringIO(buf.getvalue().decode("utf-8-sig"))))[1:]
        rows = [r + [f["name"]] for r in rows if any(c.strip() for c in r)]
        if rows:
            tab.append(spreadsheetId=SHEET_ID, range="Data!A:A", valueInputOption="USER_ENTERED",
                       body={"values": rows}).execute()
        added += len(rows)
        done.add(f["id"])
        state.set("imported", list(done)[-2000:])
    return {"files": len(new), "rows": added}