List the people at each company in your sheet

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

SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")),
                   cache_discovery=False).spreadsheets().values()
    rows = sheets.get(spreadsheetId=SHEET_ID, range="Companies!A2:B").execute().get("values", [])
    new = [(i + 2, r[0]) for i, r in enumerate(rows) if len(r) == 1 and r[0]]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    contacts, marks = [], []
    for n, domain in new:
        data = http.get("https://api.hunter.io/v2/domain-search", params={"domain": domain, "limit": 5, "type": "personal",
                        "api_key": credentials.secret("HUNTER_API_KEY")}).json().get("data") or {}
        people = data.get("emails", [])
        contacts += [[domain, f"{p.get('first_name') or ''} {p.get('last_name') or ''}".strip(), p.get("position") or "",
                      p["value"], p.get("confidence", "")] for p in people]
        marks.append({"range": f"Companies!B{n}", "values": [[f"Done: {len(people)} contacts" if people else "No contacts found"]]})
    if contacts:
        sheets.append(spreadsheetId=SHEET_ID, range="Contacts!A:E", valueInputOption="RAW", body={"values": contacts}).execute()
    sheets.batchUpdate(spreadsheetId=SHEET_ID, body={"valueInputOption": "RAW", "data": marks}).execute()
    return {"domains": len(new), "contacts": len(contacts)}