Combine Excel files from OneDrive into one workbook

from clickruntime import on_trigger, credentials, http

GRAPH = "https://graph.microsoft.com/v1.0"
FOLDER = "/me/drive/root:/Store reports"
ARCHIVE_PATH = "/drive/root:/Store reports/Imported"
MASTER = "/me/drive/root:/Master.xlsx:/workbook/tables/Sales"
COLS = 6   # columns in the Sales table, including the final "source file" column

@on_trigger
def run(event):
    h = {"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"}
    files = [f for f in http.get(f"{GRAPH}{FOLDER}:/children", headers=h, params={"$top": 200}).json()["value"]
             if f.get("file") and f["name"].lower().endswith(".xlsx") and not f["name"].startswith("~$")]
    added = {}
    for f in files:
        base = f"{GRAPH}/me/drive/items/{f['id']}/workbook"
        sheet = http.get(f"{base}/worksheets", headers=h).json()["value"][0]["name"]
        used = http.get(f"{base}/worksheets('{sheet}')/usedRange(valuesOnly=true)", headers=h).json()["values"]
        rows = [(r + [""] * COLS)[:COLS - 1] + [f["name"]] for r in used[1:] if any(str(v).strip() for v in r)]
        for i in range(0, len(rows), 500):
            http.post(f"{GRAPH}{MASTER}/rows/add", headers=h, json={"values": rows[i:i + 500]}).raise_for_status()
        http.patch(f"{GRAPH}/me/drive/items/{f['id']}", headers=h,
                   json={"parentReference": {"path": ARCHIVE_PATH}}).raise_for_status()
        added[f["name"]] = len(rows)
    return {"files": len(added), "rows": sum(added.values()), "by_file": added}