LinkedIn profiles in Google Sheets: fill a column of LinkedIn URLs with Apps Script

• A Google account, personal or Google Workspace. Nothing to install: Apps Script is in every Google Sheet.
• A Datacircle API key: sign up with your work email and it's on your dashboard, with the $5 credit, 4,000 profiles through Up2Data.
• A sheet whose row 1 has a column named linkedin_url, each row a URL like https://www.linkedin.com/in/williamhgates. Other columns are left alone.

1. In the sheet, open Extensions, then Apps Script.
2. Replace what's in the editor with the script below, or the file, and save.
3. Reload the sheet. A Datacircle menu appears next to Help.
4. Datacircle, then Set API key, and paste your key. Google first asks you to authorize the script: it edits this sheet and calls api.datacircle.dev. If it says it hasn't verified the app, that's because the script is yours, not a published add-on: open Advanced and go on to it.
5. Open the sheet with your URLs, then Datacircle, Enrich LinkedIn URLs.

The key goes in your user properties for this script, so it's in neither the sheet nor the code: share either and the key stays yours. The first run adds nine columns after your last one, status to cost_usd, and fills them row by row.

/** @OnlyCurrentDoc */ // the script asks for this sheet only, not every spreadsheet in your Drive
// LinkedIn profiles in Google Sheets, through Datacircle: Up2Data at $1.25 per 1,000 profiles found, HarvestAPI past its daily limit.
// In your sheet: Extensions > Apps Script, paste this file, save, reload the sheet. Then Datacircle > Set API key, and Datacircle > Enrich
// LinkedIn URLs on a sheet whose row 1 has a linkedin_url column. https://datacircle.dev/blog/linkedin-profiles-google-sheets
const API = "https://api.datacircle.dev";
const COLUMNS = ["status", "full_name", "headline", "location", "company", "title", "industry", "headcount", "cost_usd"];
const STOP_AFTER_MS = 4 * 60 * 1000; // Apps Script ends a run at 6 minutes: this one starts no row after 4, and the next run goes on

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Datacircle")
    .addItem("Enrich LinkedIn URLs", "enrichRows")
    .addItem("Set API key", "setApiKey")
    .addToUi();
}

function setApiKey() {
  const ui = SpreadsheetApp.getUi();
  const answer = ui.prompt("Datacircle API key", "The key on your dashboard. It's kept in your user properties for this script, not in the sheet.", ui.ButtonSet.OK_CANCEL);
  if (answer.getSelectedButton() === ui.Button.OK) {
    PropertiesService.getUserProperties().setProperty("DATACIRCLE_API_KEY", answer.getResponseText().trim());
  }
}

function call(method, path, url, provider) {
  const options = {
    method: method,
    muteHttpExceptions: true, // a 422 or a 429 is an answer to read, not an exception
    headers: { Authorization: "Token " + PropertiesService.getUserProperties().getProperty("DATACIRCLE_API_KEY"), "X-Data-Provider": provider },
  };
  if (method === "post") {
    options.contentType = "application/json";
    options.payload = JSON.stringify({ url: url });
  } else {
    path += "?url=" + encodeURIComponent(url);
  }
  const response = UrlFetchApp.fetch(API + path, options);
  let body = {};
  try {
    body = JSON.parse(response.getContentText());
  } catch (error) {} // a gateway's HTML error page: the status says enough
  return { code: response.getResponseCode(), body: body };
}

function reason(body) {
  return (body.error && body.error.message) || body.error || "";
}

// One URL: the row's columns from status on. A wrong key or an empty balance stops the run
function enrich(url) {
  let answer;
  for (const wait of [0, 10]) { // Up2Data's own rate limit, or a provider failing: wait 10 s, then once more
    if (wait) Utilities.sleep(wait * 1000);
    answer = call("post", "/v1/profiles/enrich", url, "up2data");
    const rateLimited = answer.code === 429 && typeof answer.body.error === "object";
    if (!rateLimited && [502, 503, 504].indexOf(answer.code) < 0) break;
  }
  const body = answer.body;
  if (answer.code === 200) {
    const profile = body.data;
    const company = profile.current_company || {};
    return ["found", profile.full_name, profile.headline, (profile.location || {}).raw, company.name, company.title, company.industry,
      company.headcount, body.datacircle_meta.cost_usd];
  }
  if (answer.code === 422) return ["not found: private or deleted, free", "", "", "", "", "", "", "", 0];
  if (answer.code === 400) return ["not a profile URL, free", "", "", "", "", "", "", "", 0];
  if (answer.code === 429 && typeof body.error === "string") return harvestapi(url); // Datacircle's daily Up2Data limit: HarvestAPI, same key
  return failed(answer);
}

function harvestapi(url) {
  const answer = call("get", "/linkedin/profile", url, "harvestapi");
  const body = answer.body;
  if (answer.code === 400) return ["not a profile URL, free", "", "", "", "", "", "", "", 0];
  if (answer.code !== 200) return failed(answer);
  const person = body.element;
  if (!person) return ["not found (HarvestAPI)", "", "", "", "", "", "", "", body.datacircle_meta.cost_usd]; // HarvestAPI bills the lookup
  const job = (person.currentPosition || [])[0] || {};
  return ["found (HarvestAPI)", [person.firstName, person.lastName].filter(Boolean).join(" "), person.headline,
    (person.location || {}).linkedinText, job.companyName, job.position, "", "", body.datacircle_meta.cost_usd];
}

function failed(answer) {
  if (answer.code === 401 || answer.code === 402) throw new Error(answer.code + ": " + reason(answer.body));
  return ["error " + answer.code + ": " + reason(answer.body), "", "", "", "", "", "", "", 0]; // tried again on the next run
}

function enrichRows() {
  const started = Date.now();
  const ui = SpreadsheetApp.getUi();
  if (!PropertiesService.getUserProperties().getProperty("DATACIRCLE_API_KEY")) {
    ui.alert("Set your API key first: Datacircle > Set API key.");
    return;
  }
  const sheet = SpreadsheetApp.getActiveSheet();
  const header = sheet.getRange(1, 1, 1, Math.max(sheet.getLastColumn(), 1)).getValues()[0].map(function (name) {
    return String(name).trim().toLowerCase();
  });
  const urlColumn = header.indexOf("linkedin_url");
  if (urlColumn < 0) {
    ui.alert("Name the column of LinkedIn URLs linkedin_url, in row 1.");
    return;
  }
  let first = header.indexOf("status");
  if (first < 0) { // the first run adds the columns after the last one
    first = header.length;
    sheet.getRange(1, first + 1, 1, COLUMNS.length).setValues([COLUMNS]);
  }
  const rows = sheet.getLastRow() < 2 ? [] : sheet.getRange(2, 1, sheet.getLastRow() - 1, first + 1).getValues();
  let filled = 0;
  let again = 0; // an error, or past the 4 minutes: the next run does it
  let spent = 0;
  for (let i = 0; i < rows.length; i++) {
    const url = String(rows[i][urlColumn]).trim();
    const status = String(rows[i][first]);
    if (!url || (status && status.indexOf("error") !== 0)) continue; // filled in by an earlier run
    if (Date.now() - started > STOP_AFTER_MS) {
      again++;
      continue;
    }
    let row;
    try {
      row = enrich(url);
    } catch (error) {
      ui.alert("Stopped at row " + (i + 2) + ", " + error.message + ". " + filled + " rows filled, $" + spent.toFixed(5) + " spent.");
      return;
    }
    row = row.map(function (value) { return value === undefined || value === null ? "" : value; });
    sheet.getRange(i + 2, first + 1, 1, COLUMNS.length).setValues([row]);
    if (row[0].indexOf("error") === 0) again++;
    else filled++;
    spent += Number(row[8]) || 0;
  }
  ui.alert(filled + " rows filled, $" + spent.toFixed(5) + " spent." + (again ? " " + again + " to do: run it again to go on." : ""));
}

Each row is one POST to /v1/profiles/enrich with X-Data-Provider: up2data, the provider's own request. muteHttpExceptions makes a 422 or a 429 an answer to read rather than an error that stops the script. A 200 carries Up2Data's profile in data, and what the call cost and your balance after it in datacircle_meta. Every field, jobs, schools and skills included: LinkedIn Profile API.

The two 429s are told apart by their answer: Datacircle's daily limit is a sentence, Up2Data's rate limit is its own error object. Up2Data takes $1 a day per account (800 profiles), with a shared daily limit for all customers, then answers 429 until 00:00 UTC; HarvestAPI has no daily limit. So past 800 profiles in a day, the script sends each URL to HarvestAPI through the same key, a GET with the URL as a query parameter. HarvestAPI answers a 200 even when it finds nothing; then element is null and the lookup still costs $0.0023. Up2Data's own rate limit doesn't send anything to HarvestAPI, which costs three times as much: that row waits for the next run.

Run the menu again and it skips every row that has a status, except the ones that start with error: those are tried again. A call your balance can't cover answers 402. Add funds, from $5, on your dashboard. The run stops at that row, and the next one starts there.

From Google's Apps Script quotas, the ones this script meets:

A run that reaches 6 minutes is ended by Google wherever it is, so the script starts no new row after 4: a row that waits for both retries and goes on to HarvestAPI still ends in time. The alert says how many rows are left to do; run the menu again and it goes on from the first one. Each row is one URL Fetch call, two or three when it retries or goes to HarvestAPI, so a personal account's 20,000 calls a day cover 10,400 rows even when every row past Up2Data's 800 also goes to HarvestAPI.

Why a menu and not a formula like =LINKEDIN(A2): Google's custom functions guide says Sheets makes a separate call each time a custom function is used, and runs it again when you edit it or the cell it reads, and it must return within 30 seconds. Every call through us is live and billed, and a provider that takes longer than 30 seconds would leave the cell with an error. The menu writes values: a row is fetched once, when you run it.

We ran the file above as it is, in Node, against stand-ins of the four Apps Script services it calls (SpreadsheetApp, UrlFetchApp, PropertiesService, Utilities) and of the API, answering as its reference does. With ten rows, a header typed LinkedIn_URL and a URL with spaces around it, every row came out as the table above says: the private profile and the company page free, the profile past the daily limit found (HarvestAPI) at $0.0037, Up2Data's rate limit tried again after 10 seconds. The alert read 7 rows filled, $0.00975 spent. 2 to do, and the second run called only those two rows. A wrong key stopped the run at row 2 with nothing written. With each call made to take 50 seconds, ten rows took two runs, five each.

We didn't run it inside Google Sheets itself: the test proves the script's logic and its calls, not Google's menus.

From the pricing, per profile, taken from your balance with nothing added:

So a sheet of 1,000 URLs run in one day, every profile found, costs $1.00 for the first 800 through Up2Data and $0.74 for the other 200 through HarvestAPI: $1.74. Spread over two days, 500 a day, they fit in your account's Up2Data limit: $1.25. Each row's cost_usd is what that call cost, and the alert adds up the run.

The same call from Python: get LinkedIn profile data with Python. In an n8n workflow: LinkedIn profiles in n8n. From a Clay table: Clay alternative. Next to PhantomBuster, which runs on your LinkedIn cookie: PhantomBuster alternative; and every vendor's price per 1,000: LinkedIn profile API pricing compared.