Cookbook
View as Markdown

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.

Last updated

  • Google Sheets
  • Google Apps Script

Endpoints used

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, 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.
  • 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.

// 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

StatusWhat happened
createdA new lead was created and enrolled; Lead ID holds its id.
enrolledA lead with this email or LinkedIn URL already existed in your organization and was enrolled as stored.
already in campaignThe lead was already in this campaign. Nothing changed.
suppressedThe contact is on your 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 with the campaign's id lists what the sheet added:

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 counts, before activation, how many leads lack a value for each variable a sequence uses.

Next steps