Spreadsheet bulk import

import csv
import sys
import time

import requests

BASE = "https://api.onramp.us/v1"
API_KEY = "YOUR_API_KEY"              # Settings → API → Keys
PLAYBOOK_UUID = "YOUR_PLAYBOOK_UUID"  # from the step above

# Optional: map extra spreadsheet columns onto data fields, so the values
# land in the same transaction as the project. UUIDs come from
# GET /v1/datafields?object_type_code=PROJECT&is_archived=false&limit=200
# (project-scoped and live — the create rejects any other kind)
DATAFIELD_COLUMNS = {
    # "contract_tier": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    # "region":        "b2c3d4e5-f6a7-8901-bcde-f12345678901",
}

session = requests.Session()
session.headers["Authorization"] = f"Bearer {API_KEY}"


class QuotaExhausted(Exception):
    """Monthly API quota reached — retrying can't clear it, so stop the run."""


class DedupUnsafe(Exception):
    """Too many name matches to prove this row isn't already imported."""


class DedupCheckFailed(Exception):
    """Couldn't confirm whether the project exists — skip rather than risk a twin."""


class MissingColumns(Exception):
    """A row is missing a required value — skip the row, not the run."""


def is_quota_error(resp):
    return "API limit" in (resp.json().get("detail") or "")


def already_exists(name):
    """True if a project with this exact name exists — makes re-runs safe.

    ?name= is a substring match, so ask for the 200-row maximum: with the
    default 50 the exact match can sit on a later page and be missed.
    """
    params = {"name": name, "limit": 200}
    resp = session.get(f"{BASE}/projects", params=params)
    if resp.status_code == 429:
        if is_quota_error(resp):
            raise QuotaExhausted(resp.json().get("detail"))
        time.sleep(60)
        resp = session.get(f"{BASE}/projects", params=params)
        if resp.status_code == 429 and is_quota_error(resp):
            raise QuotaExhausted(resp.json().get("detail"))
    if not resp.ok:
        # Mirror the create path: report the row and move on, rather than
        # ending the run on an uncaught HTTPError.
        raise DedupCheckFailed(f"dedup check failed ({resp.status_code})")
    body = resp.json()["data"]
    # 200 is the hard maximum, so more matches than that means the exact
    # one may be on a page we can't reach — don't guess, say so.
    if body["pagination"]["total"] > 200:
        raise DedupUnsafe(
            f"{body['pagination']['total']} projects contain this name; "
            f"the exact-name check can't see past 200"
        )
    return any(p["name"] == name for p in body["projects"])


def split_emails(cell):
    return [e.strip() for e in (cell or "").split(";") if e.strip()]


def cell(row, column):
    """Spreadsheets drop trailing empty cells, so DictReader hands back None
    for columns the row never reached. Treat those as blank, not a crash."""
    return (row.get(column) or "").strip()


def build_payload(row):
    payload = {
        "name": cell(row, "project_name"),
        "playbook_uuid": PLAYBOOK_UUID,
        "account": cell(row, "account"),
        "start_date": cell(row, "start_date"),   # MM/DD/YYYY
        "project_owner": cell(row, "owner_email"),
        "send_invite_email": False,            # flip to True to invite the team
        "send_invite_email_customers": False,  # ...and your customers
    }
    missing = [c for c in ("project_name", "account", "start_date", "owner_email") if not cell(row, c)]
    if missing:
        raise MissingColumns(", ".join(f"{c} is required" for c in missing))
    if cell(row, "due_date"):
        payload["due_date"] = cell(row, "due_date")
    if split_emails(row.get("team_emails")):
        payload["internal_users"] = split_emails(row.get("team_emails"))
    if split_emails(row.get("customer_emails")):
        payload["customer_users"] = split_emails(row.get("customer_emails"))
    datafields = {
        uuid: cell(row, column)
        for column, uuid in DATAFIELD_COLUMNS.items()
        if cell(row, column)
    }
    if datafields:
        payload["datafields"] = datafields
    return payload


def create_project(payload):
    resp = session.post(f"{BASE}/projects", json=payload)
    if resp.status_code == 429:
        if is_quota_error(resp):
            raise QuotaExhausted(resp.json().get("detail"))
        # Per-minute rate limit. The API doesn't currently return
        # Retry-After, so back off a fixed minute and retry once.
        time.sleep(60)
        resp = session.post(f"{BASE}/projects", json=payload)
        if resp.status_code == 429 and is_quota_error(resp):
            raise QuotaExhausted(resp.json().get("detail"))
    return resp


def main(csv_path):
    # utf-8-sig strips the invisible BOM Excel adds to CSV exports
    with open(csv_path, newline="", encoding="utf-8-sig") as f:
        rows = list(csv.DictReader(f))

    print(f"{len(rows)} rows to import")
    for i, row in enumerate(rows, start=1):
        name = cell(row, "project_name") or f"<row {i}>"
        try:
            payload = build_payload(row)
            if already_exists(name):
                print(f"[{i}] skipped  {name} — already exists")
                continue
            resp = create_project(payload)
        except (MissingColumns, DedupCheckFailed) as exc:
            print(f"[{i}] FAILED   {name}: {exc}")
            continue
        except QuotaExhausted as exc:
            print(f"[{i}] STOPPED  {name}: {exc}")
            print(f"Quota reached with {len(rows) - i + 1} rows left. "
                  f"Check GET {BASE}/usage, then re-run — "
                  f"the exists-check resumes where this left off.")
            return
        except DedupUnsafe as exc:
            print(f"[{i}] SKIPPED  {name}: {exc}. "
                  f"Give it a more distinctive name, or check it by hand.")
            continue

        if resp.status_code == 200:
            project = resp.json()["data"]
            print(f"[{i}] created  {name} → {project['portal_project_url']}")
        else:
            body = resp.json()
            print(f"[{i}] FAILED   {name} ({resp.status_code}): "
                  f"{body.get('message') or body.get('detail')}")
            for err in body.get("errors") or []:
                print(f"         - {err.get('field')}: {err.get('message')}")

        time.sleep(1)  # ~60 projects/minute — a polite pace, not a fixed cap


if __name__ == "__main__":
    main(sys.argv[1])

// Parse your CSV with the library you already use (csv-parse, papaparse…)
// so `rows` is an array of objects keyed by the header row.
const BASE = "https://api.onramp.us/v1";
const HEADERS = {
  Authorization: "Bearer YOUR_API_KEY",
  "Content-Type": "application/json",
};
const PLAYBOOK_UUID = "YOUR_PLAYBOOK_UUID";

// Optional: map extra columns onto data fields, set inside the create
// transaction. UUIDs from
// GET /v1/datafields?object_type_code=PROJECT&is_archived=false&limit=200
const DATAFIELD_COLUMNS = {
  // contract_tier: "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
};

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
const splitEmails = (value) =>
  (value || "").split(";").map((e) => e.trim()).filter(Boolean);
// Spreadsheets drop trailing empty cells, so a short row has undefined
// columns. Treat those as blank rather than throwing on .trim().
const cell = (row, column) => (row[column] || "").trim();

class QuotaExhausted extends Error {}
class DedupUnsafe extends Error {}
class DedupCheckFailed extends Error {}
class MissingColumns extends Error {}

const isQuotaError = (body) => (body.detail ?? "").includes("API limit");

// ?name= is a substring match — ask for the 200-row max so the exact
// match can't sit on a page we never read.
async function alreadyExists(name) {
  const url = `${BASE}/projects?name=${encodeURIComponent(name)}&limit=200`;
  let resp = await fetch(url, { headers: HEADERS });
  if (resp.status === 429) {
    if (isQuotaError(await resp.clone().json())) {
      throw new QuotaExhausted((await resp.json()).detail);
    }
    await sleep(60000);
    resp = await fetch(url, { headers: HEADERS });
    if (resp.status === 429) {
      const retryBody = await resp.clone().json();
      if (isQuotaError(retryBody)) throw new QuotaExhausted(retryBody.detail);
    }
  }
  if (!resp.ok) throw new DedupCheckFailed(`dedup check failed (${resp.status})`);
  const res = await resp.json();
  // 200 is the hard maximum: more matches than that and the exact one may
  // be on a page we can't reach.
  if (res.data.pagination.total > 200) {
    throw new DedupUnsafe(
      `${res.data.pagination.total} projects contain this name; `
      + `the exact-name check can't see past 200`);
  }
  return res.data.projects.some((p) => p.name === name);
}

async function createProject(payload) {
  const send = () => fetch(`${BASE}/projects`, {
    method: "POST", headers: HEADERS, body: JSON.stringify(payload),
  });
  let resp = await send();
  if (resp.status === 429) {
    const body = await resp.clone().json();
    // Monthly quota: retrying can't clear it, so stop the run.
    if (isQuotaError(body)) throw new QuotaExhausted(body.detail);
    // Per-minute limit. No Retry-After is returned, so back off a minute.
    await sleep(60000);
    resp = await send();
    if (resp.status === 429) {
      const retryBody = await resp.clone().json();
      if (isQuotaError(retryBody)) throw new QuotaExhausted(retryBody.detail);
    }
  }
  return resp;
}

async function importRows(rows) {
  for (const [i, row] of rows.entries()) {
    const name = cell(row, "project_name") || `<row ${i + 1}>`;
    const payload = {
      name: cell(row, "project_name"),
      playbook_uuid: PLAYBOOK_UUID,
      account: cell(row, "account"),
      start_date: cell(row, "start_date"), // MM/DD/YYYY
      project_owner: cell(row, "owner_email"),
      send_invite_email: false,
      send_invite_email_customers: false,
    };
    if (cell(row, "due_date")) payload.due_date = cell(row, "due_date");
    if (splitEmails(row.team_emails).length)
      payload.internal_users = splitEmails(row.team_emails);
    if (splitEmails(row.customer_emails).length)
      payload.customer_users = splitEmails(row.customer_emails);
    const datafields = Object.fromEntries(
      Object.entries(DATAFIELD_COLUMNS)
        .filter(([column]) => cell(row, column))
        .map(([column, uuid]) => [uuid, cell(row, column)]),
    );
    if (Object.keys(datafields).length) payload.datafields = datafields;

    let resp;
    try {
      const missing = ["project_name", "account", "start_date", "owner_email"]
        .filter((column) => !cell(row, column));
      if (missing.length) {
        throw new MissingColumns(missing.map((c) => `${c} is required`).join(", "));
      }
      if (await alreadyExists(name)) {
        console.log(`[${i + 1}] skipped  ${name} — already exists`);
        continue;
      }
      resp = await createProject(payload);
    } catch (err) {
      if (err instanceof DedupUnsafe) {
        console.log(`[${i + 1}] SKIPPED  ${name}: ${err.message}. `
          + `Give it a more distinctive name, or check it by hand.`);
        continue;
      }
      if (err instanceof MissingColumns || err instanceof DedupCheckFailed) {
        console.log(`[${i + 1}] FAILED   ${name}: ${err.message}`);
        continue;
      }
      if (!(err instanceof QuotaExhausted)) throw err;
      console.log(`[${i + 1}] STOPPED  ${name}: ${err.message}`);
      console.log(`Quota reached with ${rows.length - i} rows left. Check `
        + `GET ${BASE}/usage, then re-run — the exists-check resumes here.`);
      return;
    }
    const body = await resp.json();
    if (resp.ok) {
      console.log(`[${i + 1}] created  ${name} → ${body.data.portal_project_url}`);
    } else {
      console.log(`[${i + 1}] FAILED   ${name} (${resp.status}): ${body.message ?? body.detail}`);
      for (const err of body.errors ?? [])
        console.log(`         - ${err.field}: ${err.message}`);
    }

    await sleep(1000); // ~60 projects/minute — a polite pace, not a fixed cap
  }
}

# Per row: 1. SKIP IF EXISTS — exact-name match?
# ?name= is a substring match, so raise the 50-row default to the 200 max.
curl -H "Authorization: Bearer YOUR_API_KEY" \
  "https://api.onramp.us/v1/projects?name=Acme%20Corp%20Onboarding&limit=200"
# → if data.projects contains that exact name, skip this row
# → if data.pagination.total > 200 the exact match may be on an unreachable
#   page: rename the row or check it by hand rather than risk a duplicate

# 2. CREATE (dates are MM/DD/YYYY — see Good to know)
curl -X POST -H "Authorization: Bearer YOUR_API_KEY" -H "Content-Type: application/json" \
  -d '{
    "name": "Acme Corp Onboarding",
    "playbook_uuid": "YOUR_PLAYBOOK_UUID",
    "account": "Acme Corp",
    "start_date": "09/01/2026",
    "due_date": "11/30/2026",
    "project_owner": "jordan@yourco.example",
    "internal_users": ["sam@yourco.example", "priya@yourco.example"],
    "customer_users": ["it@acmecorp.example"],
    "send_invite_email": false,
    "send_invite_email_customers": false,
    "datafields": {"a1b2c3d4-e5f6-7890-abcd-ef1234567890": "Enterprise"}
  }' \
  "https://api.onramp.us/v1/projects"
# → data.uuid, data.internal_project_url, data.portal_project_url
# datafields is optional — uuids from
#   GET /v1/datafields?object_type_code=PROJECT&is_archived=false&limit=200

# 3. PACE — sleep 1 second between rows