Save supplier attachments to S3 and log their details in a sheet

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

SHEET_ID = "your-spreadsheet-id"
QUERY = "has:attachment from:(@northwind.com OR @acme-supply.com) newer_than:2d"
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):
    gmail, done = build("gmail", "v1", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False), set(state.get("done") or [])
    msgs = [m for m in gmail.users().messages().list(userId="me", q=QUERY).execute().get("messages", []) if m["id"] not in done]
    report_fetched(len(msgs))
    if not msgs:
        return {"summary": "Nothing new"}
    s3, rows = aws("s3"), []
    for m in msgs:
        full = gmail.users().messages().get(userId="me", id=m["id"]).execute()
        sender = next(h["value"] for h in full["payload"]["headers"] if h["name"] == "From").split("@")[-1].strip(">")
        f = ai.generate(f"Return invoice number, date (YYYY-MM-DD) and total from:\n{full['snippet']}", max_output_tokens=80, json_schema={"type": "object",
                        "properties": {"number": {"type": "string"}, "date": {"type": "string"}, "total": {"type": "number"}}}).json
        for p in [p for p in full["payload"].get("parts", []) if p.get("filename")]:
            data = gmail.users().messages().attachments().get(userId="me", messageId=m["id"], id=p["body"]["attachmentId"]).execute()["data"]
            key = f"{date.today():%Y/%m}/{sender}/{m['id']}-{p['filename']}"
            s3.put_object(Bucket=BUCKET, Key=key, Body=base64.urlsafe_b64decode(data))
            rows.append([sender, p["filename"], f.get("number"), f.get("date"), f.get("total"), f"s3://{BUCKET}/{key}"])
        done.add(m["id"])
    build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Documents!A:F", valueInputOption="RAW", body={"values": rows}).execute()
    state.set("done", list(done)[-1000:])
    return {"emails": len(msgs), "files": len(rows)}