# Add leads from Google Sheets to a campaign

A Google Apps Script that adds each new row of a sheet to a campaign on a schedule, writes the lead id back, and keeps extra columns as custom fields.

Works with: Google Sheets, Google Apps Script.

Endpoints used:

- [`POST /v1/leads`](https://docs.versionseven.ai/api-reference/leads/create-lead) Create a lead
- [`GET /v1/leads`](https://docs.versionseven.ai/api-reference/leads/list-leads) List leads

A shared sheet is often where a lead list lives before it's a campaign. This recipe is an Apps Script bound to that sheet: on a schedule, it reads every row without a lead id, adds the row to a campaign with [`POST /v1/leads`](https://docs.versionseven.ai/api-reference/leads/create-lead), and writes the result back into the row. Nothing runs outside Google, and the sheet stays the source of truth.

## Before you start

- An API key with `leads:write` and `leads:read`. See [Create an API key](https://docs.versionseven.ai/help/api-keys).
- The campaign's id, from its URL in the app or from `GET /v1/campaigns`.
- A Google Sheet with a header row. The script recognises these column names, in any order and any case: **First name**, **Last name**, **Email**, **LinkedIn URL**, **Company**, **Title**, **Company website**, **Industry**. Any other column is kept as a custom field, usable in messages as a variable such as `{plan}`.
- Three empty columns for the script to fill: **Lead ID**, **Status** and **Synced at**. Add them to the header row.

## 1. Add the script

Open the sheet, choose **Extensions → Apps Script**, replace the contents of `Code.gs` with this, and save.

```javascript
// Code.gs
const API_URL = "https://api.versionseven.ai/v1";
const STANDARD = {
  "first name": "first_name",
  "last name": "last_name",
  "email": "email",
  "linkedin url": "linkedin_url",
  "company": "company",
  "title": "title",
  "company website": "company_website",
  "industry": "industry",
};
const CONTROL = { "lead id": "lead_id", "status": "status", "synced at": "synced_at" };

function syncLeads() {
  const props = PropertiesService.getScriptProperties();
  const apiKey = props.getProperty("VICTORIA_API_KEY");
  const campaignId = props.getProperty("CAMPAIGN_ID");
  if (!apiKey || !campaignId) throw new Error("Set VICTORIA_API_KEY and CAMPAIGN_ID in Project Settings → Script Properties");

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(props.getProperty("SHEET_TAB") || "Leads");
  const rows = sheet.getDataRange().getValues();
  const header = rows[0].map((cell) => String(cell).trim().toLowerCase());
  const column = (name) => header.indexOf(name);
  for (const name of Object.keys(CONTROL)) {
    if (column(name) === -1) throw new Error(`Add a "${name}" column to the header row`);
  }

  let synced = 0;
  for (let i = 1; i < rows.length; i++) {
    const row = rows[i];
    if (row[column("lead id")] || row[column("status")] === "invalid" || row[column("status")] === "suppressed") continue;
    if (row.every((cell) => cell === "")) continue;

    const lead = {};
    const customFields = {};
    header.forEach((name, index) => {
      const value = String(row[index]).trim();
      if (!value || name in CONTROL) return;
      if (name in STANDARD) lead[STANDARD[name]] = value;
      else customFields[name.replace(/\s+/g, "_")] = value;
    });
    if (lead.linkedin_url) lead.linkedin_url = lead.linkedin_url.replace(/\/+$/, "");
    if (Object.keys(customFields).length > 0) lead.custom_fields = customFields;

    const body = { campaign_id: campaignId, lead: lead };
    const result = addLead(apiKey, body);

    sheet.getRange(i + 1, column("status") + 1).setValue(result.status);
    sheet.getRange(i + 1, column("synced at") + 1).setValue(new Date());
    if (result.leadId) sheet.getRange(i + 1, column("lead id") + 1).setValue(result.leadId);
    synced++;
    Utilities.sleep(650); // about 90 requests a minute, under the limit of 100 to one endpoint
  }
  Logger.log(`Processed ${synced} row(s)`);
}

// One POST /v1/leads, retried on 429 and 503. The Idempotency-Key is derived from the campaign and
// the lead, so a retried request can't enrol the lead twice.
function addLead(apiKey, body) {
  const digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, JSON.stringify(body));
  const idempotencyKey = digest.map((b) => ("0" + (b & 0xff).toString(16)).slice(-2)).join("");
  for (let attempt = 1; attempt <= 5; attempt++) {
    const response = UrlFetchApp.fetch(`${API_URL}/leads`, {
      method: "post",
      contentType: "application/json",
      headers: { Authorization: `Bearer ${apiKey}`, "Idempotency-Key": idempotencyKey },
      payload: JSON.stringify(body),
      muteHttpExceptions: true,
    });
    const status = response.getResponseCode();
    let data = {};
    try {
      data = JSON.parse(response.getContentText());
    } catch (e) {}

    if (status === 201) return { status: "created", leadId: data.lead_id };
    if (status === 200) return { status: "enrolled", leadId: data.lead_id };
    if (data.error === "LEAD_ALREADY_IN_CAMPAIGN") return { status: "already in campaign" };
    if (data.error === "LEAD_SUPPRESSED") return { status: "suppressed" };
    if (data.error === "VALIDATION_ERROR") {
      const problems = (data.details && data.details.errors ? data.details.errors : []).map((e) => `${e.field} ${e.message}`);
      return { status: "invalid: " + problems.join("; ") };
    }
    if (data.error === "TRIAL_LEAD_CAP_REACHED") throw new Error("Trial lead cap reached; subscribe to add more leads");
    if (data.error === "CAMPAIGN_NOT_FOUND") throw new Error("Campaign not found; check CAMPAIGN_ID");
    const retryable = status === 429 || status === 503 || data.error === "IDEMPOTENCY_IN_PROGRESS";
    if (!retryable || attempt === 5) return { status: `failed: ${status} ${data.error || ""}` };
    const retryAfter = Number(response.getHeaders()["Retry-After"]) || Math.pow(2, attempt);
    Utilities.sleep(retryAfter * 1000);
  }
}
```

## 2. Store the key and campaign

In the Apps Script editor, open **Project Settings → Script Properties** and add `VICTORIA_API_KEY` (the key), `CAMPAIGN_ID` (the campaign's UUID) and, if the tab isn't called `Leads`, `SHEET_TAB`. Script properties aren't visible in the sheet, which is where an API key belongs.

## 3. Run it once, then schedule it

Select `syncLeads` in the editor toolbar and **Run**. The first run asks you to authorise the script to read the sheet and call an external service. When it finishes, each processed row has a **Status** and a **Synced at**; created and enrolled rows have a **Lead ID**.

To run it on a schedule, open **Triggers** (the clock icon), **Add Trigger**, choose `syncLeads`, **Time-driven**, and an interval such as every hour. A sheet that gains a few rows a day is comfortably inside Apps Script's daily quota for external requests.

## What each status means

| Status | What happened |
| - | - |
| `created` | A new lead was created and enrolled; **Lead ID** holds its id. |
| `enrolled` | A lead with this email or LinkedIn URL already existed in your organization and was enrolled as stored. |
| `already in campaign` | The lead was already in this campaign. Nothing changed. |
| `suppressed` | The contact is on your [Do Not Contact](https://docs.versionseven.ai/help/do-not-contact) list. The row is skipped on later runs. |
| `invalid: …` | The API refused the row, for example a missing last name or a malformed email. Fix the row and clear the status to retry it. |
| `failed: …` | A `429` or `503` that didn't clear after 5 attempts, or an unexpected error. The row is retried next run. |

Rows with a **Lead ID**, and rows marked `invalid` or `suppressed`, are skipped on later runs, so the script is safe to run as often as you like. When a row's data changes, the request body and so the `Idempotency-Key` change with it; the lead is then matched by email or LinkedIn URL, and a lead already in the campaign answers `already in campaign`. Editing a lead that's already in a campaign is done with `PATCH /v1/leads/{lead_id}`, not by re-syncing the row.

## 4. Check the result

[`GET /v1/leads`](https://docs.versionseven.ai/api-reference/leads/list-leads) with the campaign's id lists what the sheet added:

```bash
curl "https://api.versionseven.ai/v1/leads?campaign_id=$CAMPAIGN_ID&limit=100" \
  -H "Authorization: Bearer $VICTORIA_API_KEY"
```

Each lead's `custom_fields` carries the sheet's extra columns, with spaces turned into underscores: a column called **Plan** becomes `plan`, usable in a step as `{plan}`. The [readiness check](https://docs.versionseven.ai/help/preflight-checks) counts, before activation, how many leads lack a value for each variable a sequence uses.

## Next steps

- [Add CRM contacts to a campaign](https://docs.versionseven.ai/cookbook/add-crm-contacts), the same logic as a script you run yourself, in Node.js and Python.
- [Log prospect replies to Google Sheets](https://docs.versionseven.ai/cookbook/log-replies-to-google-sheets), the other direction.
- [Personalization fields](https://docs.versionseven.ai/help/personalization-fields) for how custom fields appear in messages.