Scan email links, log results and alert Teams on bad ones

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

SHEET_ID, TEAM_ID, CHANNEL_ID = "your-spreadsheet-id", "your-team-id", "your-channel-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    gmail = build("gmail", "v1", credentials=creds, cache_discovery=False).users().messages()
    seen = state.get("seen") or []
    new = [m["id"] for m in gmail.list(userId="me", q="newer_than:1d").execute().get("messages", []) if m["id"] not in seen]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    vt, rows = {"x-apikey": credentials.secret("VIRUSTOTAL_API_KEY")}, []
    for mid in new:
        msg = gmail.get(userId="me", id=mid, format="full").execute()
        subject = next((h["value"] for h in msg["payload"]["headers"] if h["name"] == "Subject"), "")
        for url in list(set(re.findall(r"https?://[^\s\"'<>]+", str(msg["payload"]))))[:5]:
            uid = base64.urlsafe_b64encode(url.encode()).decode().strip("=")
            stats = http.get(f"https://www.virustotal.com/api/v3/urls/{uid}", headers=vt).json().get("data", {}).get("attributes", {}).get("last_analysis_stats", {})
            bad = stats.get("malicious", 0)
            rows.append([msg["internalDate"], subject, url, bad])
            if bad:
                http.post(f"https://graph.microsoft.com/v1.0/teams/{TEAM_ID}/channels/{CHANNEL_ID}/messages",
                          headers={"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"},
                          json={"body": {"content": f"⚠️ Malicious link in \"{subject}\": {url}"}})
    if rows:
        build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values().append(spreadsheetId=SHEET_ID,
            range="Security!A:D", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    state.set("seen", (seen + new)[-1000:])
    return {"emails": len(new), "links": len(rows)}