Sync leads from a Google Sheet into HubSpot

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

SHEET_ID = "1AbCdEfGhIjKlMnOpQrStUvWxYz"

def sheets():
    return build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()

@on_trigger
def run(event):
    rows = sheets().get(spreadsheetId=SHEET_ID, range="Leads!A2:E").execute().get("values", [])
    todo = [(i + 2, r + [""] * (5 - len(r))) for i, r in enumerate(rows) if r and "@" in r[0] and (len(r) < 5 or not r[4])][:100]
    report_fetched(len(todo))
    if not todo:
        return {"synced": 0}
    inputs = [{"idProperty": "email", "id": r[0].strip().lower(), "properties": {"email": r[0].strip().lower(), "firstname": r[1], "lastname": r[2], "company": r[3]}} for _, r in todo]
    resp = http.post("https://api.hubapi.com/crm/v3/objects/contacts/batch/upsert", json={"inputs": inputs},
                     headers={"Authorization": f"Bearer {credentials.connection('hubspot', 'work')}"})
    resp.raise_for_status()
    ids = {c["properties"]["email"]: c["id"] for c in resp.json()["results"]}
    data = [{"range": f"Leads!E{row}", "values": [[ids.get(r[0].strip().lower(), "")]]} for row, r in todo]
    sheets().batchUpdate(spreadsheetId=SHEET_ID, body={"valueInputOption": "RAW", "data": data}).execute()
    return {"synced": len(ids)}