Audit open deals against rules kept in a sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify
SHEET_ID = "your-spreadsheet-id"
INSTANCE = "https://yourcompany.my.salesforce.com"
API = f"{INSTANCE}/services/data/v61.0"
CHECKS = {"max": lambda v, x: v is not None and float(v) > float(x),
          "min": lambda v, x: v is not None and float(v) < float(x),
          "required": lambda v, x: v in (None, "")}

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")),
                   cache_discovery=False).spreadsheets()
    rules = sheets.values().get(spreadsheetId=SHEET_ID, range="Rules!A2:D").execute().get("values", [])
    fields = sorted({r[1] for r in rules})
    headers = {"Authorization": f"Bearer {credentials.connection('salesforce', 'work')}"}
    opps = http.get(f"{API}/query", headers=headers, params={"q": f"SELECT Id, Name, Owner.Name, {', '.join(fields)} "
                    "FROM Opportunity WHERE IsClosed = false LIMIT 500"}).json()["records"]
    findings = [["Deal", "Owner", "Rule", "Value", "Link"]]
    for o in opps:
        for name, field, check, limit in [(r + ["", "", "", ""])[:4] for r in rules]:
            if CHECKS[check](o.get(field), limit):
                findings.append([o["Name"], o["Owner"]["Name"], name, str(o.get(field)), f"{INSTANCE}/{o['Id']}"])
    sheets.values().clear(spreadsheetId=SHEET_ID, range="Findings!A:E").execute()
    sheets.values().update(spreadsheetId=SHEET_ID, range="Findings!A1", valueInputOption="RAW",
                           body={"values": findings}).execute()
    notify.email_me(f"Deal audit: {len(findings) - 1} issues across {len(opps)} open deals",
                    f"https://docs.google.com/spreadsheets/d/{SHEET_ID}")
    return {"deals": len(opps), "findings": len(findings) - 1}