Import leads from a sheet into your Airtable CRM

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify, report_fetched
BASE_ID = "appXXXXXXXXXXXXXX"
SHEET_ID = "1AbCdEfGhIjKlMnOpQrStUvWxYz"
@on_trigger
def run(event):
    values = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")),
                   cache_discovery=False).spreadsheets().values()
    rows = values.get(spreadsheetId=SHEET_ID, range="Marketing leads!A2:E").execute().get("values", [])
    new = [(i + 2, (r + [""] * 5)[:5]) for i, r in enumerate(rows) if len(r) < 5 or not r[4]]
    report_fetched(len(new))
    if not new:
        return {"imported": 0}
    api, headers = f"https://api.airtable.com/v0/{BASE_ID}/Companies", {"Authorization": f"Bearer {credentials.connection('airtable', 'work')}"}
    skipped = []
    for row_num, (company, contact, email, website, _) in new:
        domain = (website or email.split("@")[-1]).lower().replace("https://", "").replace("http://", "").replace("www.", "").strip("/")
        if not domain:
            skipped.append(str(row_num))
            continue
        found = http.get(api, headers=headers, params={"filterByFormula": f"{{Domain}}='{domain}'", "maxRecords": 1}).json()["records"]
        fields = {"Name": company, "Domain": domain, "Contact": contact, "Email": email, "Source": "Marketing sheet"}
        record = {"id": found[0]["id"], "fields": fields} if found else {"fields": fields}
        (http.patch if found else http.post)(api, headers=headers, json={"typecast": True, "records": [record]}).raise_for_status()
        values.update(spreadsheetId=SHEET_ID, range=f"Marketing leads!E{row_num}", valueInputOption="RAW",
                      body={"values": [["Updated" if found else "Imported"]]}).execute()
    if skipped:
        notify.email_me("Leads without a domain", "Rows missing a website or email: " + ", ".join(skipped))
    return {"handled": len(new) - len(skipped), "skipped": len(skipped)}