Create Salesforce accounts from a Google Sheet

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

SHEET_ID = "your-spreadsheet-id"
API = "https://yourcompany.my.salesforce.com/services/data/v61.0"

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    rows = [(r + ["", "", ""])[:3] for r in sheets.values().get(spreadsheetId=SHEET_ID, range="Companies!A2:C").execute().get("values", [])]
    todo = [(i, r) for i, r in enumerate(rows, start=2) if r[0] and not r[2]]  # name filled, no Salesforce ID yet
    report_fetched(len(todo))
    if not todo:
        return {"summary": "No new rows"}
    headers = {"Authorization": f"Bearer {credentials.connection('salesforce', 'work')}"}
    updates, created = [], []
    for i, (name, website, _) in todo:
        safe = name.replace("'", "\\'")
        found = http.get(f"{API}/query", headers=headers, params={"q": f"SELECT Id FROM Account WHERE Name = '{safe}' LIMIT 1"}).json()["records"]
        if found:
            account_id = found[0]["Id"]
        else:
            account_id = http.post(f"{API}/sobjects/Account", headers=headers, json={"Name": name, "Website": website}).json()["id"]
            created.append(name)
        updates.append({"range": f"Companies!C{i}", "values": [[account_id]]})
    sheets.values().batchUpdate(spreadsheetId=SHEET_ID, body={"valueInputOption": "RAW", "data": updates}).execute()
    if created:
        notify.email_me(f"{len(created)} new Salesforce accounts from your sheet", "\n".join(created))
    return {"rows": len(todo), "created": len(created)}