/** @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." : ""));
}