Find company details for domains in Google Sheets

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

SHEET_ID = "your-spreadsheet-id"
PER_RUN = 50

@on_trigger
def run(event):
    tab = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()
    rows = tab.get(spreadsheetId=SHEET_ID, range="Accounts!A2:F").execute().get("values", [])
    todo = [(i, r[0].strip().lower()) for i, r in enumerate(rows, start=2) if r and r[0].strip() and (len(r) < 2 or not r[1])][:PER_RUN]
    report_fetched(len(todo))
    key, updates = credentials.secret("HUNTER_API_KEY"), []
    for n, domain in todo:
        resp = http.get("https://api.hunter.io/v2/companies/find", params={"domain": domain, "api_key": key})
        c = resp.json().get("data") or {} if resp.ok else {}
        geo, cat, metrics = c.get("geo") or {}, c.get("category") or {}, c.get("metrics") or {}
        linkedin = (c.get("linkedin") or {}).get("handle")
        updates.append({"range": f"Accounts!B{n}:F{n}", "values": [[
            c.get("name") or "not found", cat.get("industry") or "", metrics.get("employees") or "",
            geo.get("country") or "", f"https://linkedin.com/{linkedin}" if linkedin else ""]]})
    if updates:
        tab.batchUpdate(spreadsheetId=SHEET_ID, body={"valueInputOption": "RAW", "data": updates}).execute()
    return {"enriched": len(updates)}