# Company data in Google Sheets

=HAPPYFETCH("stripe.com", "employeeCount") puts company data in a cell. One script you paste once, and a menu that enriches a whole column.

## Set it up

1. In your sheet, open **Extensions > Apps Script**.
2. Replace what's in the editor with [the script below](#the-script), and save.
3. Reload the sheet. A **HappyFetch** menu appears next to Help.
4. Choose **HappyFetch > Set API key…** and paste a key from the console. Google asks you once to allow the
   script to connect to the API and to show its menu.

The key is stored in the script's properties. Everyone who can edit the sheet's script can read it, so
create a key just for this sheet and revoke it in the console when you no longer need it.
**HappyFetch > Remove API key** takes it out of the sheet.

## Formulas

| Formula | What you get |
|---|---|
| `=HAPPYFETCH(A2, "name")` | The name of the company behind the website in A2 |
| `=HAPPYFETCH(A2, "name", "industry", "employeeCount")` | Three cells in a row, one per field |
| `=HAPPYFETCH(A2, "name, industry")` | The same, as one comma-separated list |
| `=HAPPYFETCH(A2, $B$1:$F$1)` | The fields named in the header row B1:F1 |
| `=HAPPYFETCH_COMPANY("stripe", "employeeCount")` | A lookup by LinkedIn company URL, slug or numeric id |

`A2` can hold a domain (`stripe.com`) or any URL on the site. Fields are the keys of the
[company profile](/docs/company), with dots for nested ones:

| Field | Example, for stripe.com |
|---|---|
| `name` | `Stripe` |
| `industry` | `Technology, Information and Internet` |
| `websiteUrl` | `https://stripe.com` |
| `employeeCount` | `16592` |
| `employeeCountRange.start`, `employeeCountRange.end` | `5001`, `10000` |
| `headquarter.city`, `headquarter.country` | `South San Francisco`, `US` |
| `foundedOn.year` | `2010` |
| `linkedInUrl` | `https://www.linkedin.com/company/stripe` |
| `tagline`, `description` | Text from the company page |
| `specialities` | A list, comma-separated |

An unknown value leaves the cell empty. A company that doesn't exist reads `not found`. An empty input cell
gives an empty result, so you can fill a formula down past the end of your list.

## A whole column: enrich selected domains

1. Select the cells with the domains: one column, one domain per row.
2. Choose **HappyFetch > Enrich selected domains…** and name the fields, or keep the default
   (`name, industry, employeeCount, headquarter.city, headquarter.country, websiteUrl`).
3. The values land in the columns to the right of your selection, as plain values that never run again.

Rows that already have a value are skipped, and rows that failed stay empty, so running it again picks up
where it stopped without paying twice. It stops itself before Google's six-minute limit for scripts and
tells you how many companies it found and how many credits it charged.

## What it costs

Every lookup is billed like any API call:

| Formula | Fetched now | From our cache | No such company |
|---|---|---|---|
| `=HAPPYFETCH_COMPANY(…)`, by LinkedIn company URL | 2 credits | 1 credit | 1 credit |
| `=HAPPYFETCH(…)`, by website domain | 4 credits | 2 credits | 4 credits |

A "no such company" answer from our cache is free. Errors never cost anything, and the cell says what
happened: an invalid domain, an invalid key, or no credits left.

By LinkedIn company URL:

| Lookups a month | Smallest pack (Starter) | Biggest pack (Business) |
|---|---|---|
| 1,000 lookups by LinkedIn URL | $1.00 | $0.50 |
| 10,000 lookups by LinkedIn URL | $10.00 | $5.00 |
| 100,000 lookups by LinkedIn URL | $100.00 | $50.00 |

Every lookup fetched fresh, at 2 credits each. Lookups our cache answers cost half. A confirmed not-found costs 1 credit, and nothing from our cache. Starter: $10 for 20,000 credits. Business: $500 for 2,000,000 credits. Credits never expire.

By website domain:

| Lookups a month | Smallest pack (Starter) | Biggest pack (Business) |
|---|---|---|
| 1,000 lookups by website domain | $2.00 | $1.00 |
| 10,000 lookups by website domain | $20.00 | $10.00 |
| 100,000 lookups by website domain | $200.00 | $100.00 |

Every lookup fetched fresh, at 4 credits each. Lookups our cache answers cost half. A confirmed not-found costs the same, and nothing from our cache. Starter: $10 for 20,000 credits. Business: $500 for 2,000,000 credits. Credits never expire.

The sheet keeps every answer for six hours, so asking another field of the same company, or a
recalculation within those hours, costs nothing. Google re-runs custom formulas from time to time, for
example when their inputs change, and after six hours such a re-run is a new lookup. For a list you enrich
once, the menu is cheaper than formulas.

## Limits

- A formula gets 30 seconds, Google's limit for custom functions. Cached companies come back fastest; if a
  cell shows an error, recalculate it later or use the menu.
- One domain per formula. For a column, use the menu.
- Apps Script has a daily quota for outgoing requests: see
  [Google's quotas](https://developers.google.com/apps-script/guides/services/quotas).

## The script

```javascript title="HappyFetch.gs"
/**
 * @OnlyCurrentDoc
 *
 * HappyFetch for Google Sheets: company data in a cell.
 *
 *   =HAPPYFETCH("stripe.com", "employeeCount")
 *   =HAPPYFETCH(A2, "name", "industry", "employeeCount")    one row, a column per field
 *   =HAPPYFETCH(A2, $B$1:$E$1)                             field names from a header row
 *   =HAPPYFETCH_COMPANY("stripe", "name")                  by LinkedIn company URL, slug or numeric id
 *
 * Fields are the keys of the company profile (https://happyfetch.net/docs/company), with dots for nested
 * ones: "headquarter.city", "employeeCountRange.start", "foundedOn.year". Lists come back comma-separated.
 *
 * Setup: Extensions > Apps Script, paste this file, save, reload the sheet, then HappyFetch > Set API key.
 * For a whole column, select the domains and use HappyFetch > Enrich selected domains: it writes values
 * instead of formulas, so nothing is looked up again when the sheet recalculates.
 */

const HF_BASE_URL = "https://api.happyfetch.net";
const HF_KEY_PROPERTY = "HAPPYFETCH_API_KEY";
const HF_CACHE_SECONDS = 21600; // 6 hours, the longest Apps Script keeps a cached value
const HF_DEFAULT_FIELDS = "name, industry, employeeCount, headquarter.city, headquarter.country, websiteUrl";

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("HappyFetch")
    .addItem("Set API key…", "hfSetApiKey")
    .addItem("Enrich selected domains…", "hfEnrichSelection")
    .addSeparator()
    .addItem("Remove API key", "hfRemoveApiKey")
    .addToUi();
}

function hfSetApiKey() {
  const ui = SpreadsheetApp.getUi();
  const answer = ui.prompt(
    "HappyFetch API key",
    "Paste an API key from the HappyFetch console. Anyone who can edit this sheet's script can read it, so create a key just for this sheet.",
    ui.ButtonSet.OK_CANCEL,
  );
  if (answer.getSelectedButton() !== ui.Button.OK) return;
  const key = answer.getResponseText().trim();
  if (!key) return;
  PropertiesService.getScriptProperties().setProperty(HF_KEY_PROPERTY, key);
  ui.alert("HappyFetch", "Saved. =HAPPYFETCH() can now look up companies.", ui.ButtonSet.OK);
}

function hfRemoveApiKey() {
  PropertiesService.getScriptProperties().deleteProperty(HF_KEY_PROPERTY);
  SpreadsheetApp.getUi().alert("HappyFetch", "The API key is removed from this sheet.", SpreadsheetApp.getUi().ButtonSet.OK);
}

/**
 * Company data for a website. Repeats within 6 hours are answered from this sheet's cache, for free.
 *
 * @param {string} domain A website: a domain such as "stripe.com", or any URL on it.
 * @param {string} fields Field names such as "name", "employeeCount" or "headquarter.city", or a range of them. Defaults to "name".
 * @return The values, one column per field.
 * @customfunction
 */
function HAPPYFETCH(domain, fields) {
  return hfCell_("domain", domain, Array.prototype.slice.call(arguments, 1));
}

/**
 * Company data for a LinkedIn company URL, slug or numeric id. Repeats within 6 hours are answered from this sheet's cache, for free.
 *
 * @param {string} company A LinkedIn company URL, slug or numeric id.
 * @param {string} fields Field names such as "name", "employeeCount" or "headquarter.city", or a range of them. Defaults to "name".
 * @return The values, one column per field.
 * @customfunction
 */
function HAPPYFETCH_COMPANY(company, fields) {
  return hfCell_("company", company, Array.prototype.slice.call(arguments, 1));
}

function hfCell_(kind, input, fieldArgs) {
  if (Array.isArray(input)) {
    throw new Error("HappyFetch: pass one " + (kind === "domain" ? "domain" : "company") + " per formula. For a whole column, use HappyFetch > Enrich selected domains.");
  }
  const ref = String(input == null ? "" : input).trim();
  if (!ref) return ""; // empty rows stay empty, and cost nothing
  const fields = hfFields_(fieldArgs);
  const result = hfLookup_(kind, ref, hfApiKey_());
  const row = hfRow_(result, fields);
  return fields.length === 1 ? row[0] : [row];
}

/** Field names from the formula's arguments: strings, comma-separated lists or ranges. */
function hfFields_(args) {
  const out = [];
  const add = (v) => {
    if (Array.isArray(v)) return v.forEach(add);
    String(v == null ? "" : v).split(",").forEach((f) => {
      const name = f.trim();
      if (name) out.push(name);
    });
  };
  add(args);
  return out.length ? out : ["name"];
}

function hfApiKey_() {
  const key = PropertiesService.getScriptProperties().getProperty(HF_KEY_PROPERTY);
  if (!key) throw new Error("HappyFetch: no API key yet. Use the menu HappyFetch > Set API key.");
  return key;
}

function hfCacheKey_(kind, ref) {
  const raw = kind + ":" + ref.toLowerCase();
  if (raw.length <= 200) return "hf:" + raw;
  const digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, raw, Utilities.Charset.UTF_8);
  return "hf:" + digest.map((b) => ((b + 256) % 256).toString(16).padStart(2, "0")).join("");
}

/**
 * One lookup: { found: true, data, charged } or { found: false, reason, charged }. Answers, a company or a
 * confirmed "no such company", are cached for 6 hours, so a recalculation doesn't pay again.
 */
function hfLookup_(kind, ref, key) {
  const cache = CacheService.getScriptCache();
  const cacheKey = hfCacheKey_(kind, ref);
  const hit = cache.get(cacheKey);
  if (hit) {
    const cached = JSON.parse(hit);
    cached.charged = 0;
    return cached;
  }
  const result = hfParse_(UrlFetchApp.fetch(HF_BASE_URL + "/v1/" + kind, hfRequest_(kind, ref, key)), ref);
  cache.put(cacheKey, JSON.stringify({ found: result.found, data: result.data, reason: result.reason }), HF_CACHE_SECONDS);
  return result;
}

function hfRequest_(kind, ref, key) {
  return {
    method: "post",
    contentType: "application/json",
    headers: { Authorization: "Bearer " + key, "Idempotency-Key": Utilities.getUuid() },
    payload: JSON.stringify(kind === "domain" ? { domain: ref } : { url: ref }),
    muteHttpExceptions: true,
  };
}

/** A response as a result, or a clear error for the cell. Errors that aren't answers cost nothing. */
function hfParse_(res, ref) {
  const status = res.getResponseCode();
  let body = null;
  try {
    body = JSON.parse(res.getContentText());
  } catch (e) {
    body = null;
  }
  if (status === 200 && body && body.data) return { found: true, data: body.data, charged: body.meta ? body.meta.credits_charged : 0 };
  const error = body && body.error ? body.error : null;
  const charged = body && body.meta ? body.meta.credits_charged || 0 : 0;
  if (status === 404 && error && (error.code === "not_found" || error.code === "unresolvable_id")) {
    return { found: false, reason: error.reason || null, charged: charged };
  }
  if (status === 401) throw new Error("HappyFetch: the API key isn't valid. Set it again under HappyFetch > Set API key.");
  if (status === 402) throw new Error("HappyFetch: your workspace is out of credits. Buy a pack in the console. Nothing was charged.");
  if (status === 400 && error) throw new Error("HappyFetch: " + ref + ": " + error.message + " Nothing was charged.");
  if (error && error.retryable) throw new Error("HappyFetch: " + error.message + " Nothing was charged; recalculate in a minute.");
  if (error) throw new Error("HappyFetch: " + error.message + (error.doc_url ? " (" + error.doc_url + ")" : ""));
  throw new Error("HappyFetch: unexpected answer (HTTP " + status + "). Nothing was charged; try again later.");
}

/** The values of a result for the given fields. A company that doesn't exist reads "not found". */
function hfRow_(result, fields) {
  if (!result.found) return fields.map((f, i) => (i === 0 ? "not found" : ""));
  return fields.map((f) => hfValue_(result.data, f));
}

function hfValue_(data, path) {
  let v = data;
  for (const part of path.split(".")) {
    if (v == null || typeof v !== "object") return "";
    v = v[part];
  }
  if (v == null) return "";
  if (Array.isArray(v)) return v.map((x) => (x !== null && typeof x === "object" ? JSON.stringify(x) : String(x))).join(", ");
  if (typeof v === "object") return JSON.stringify(v);
  return v;
}

/**
 * Menu action: looks up every domain in the selected cells (one column) and writes the chosen fields as
 * values into the columns to its right. Rows whose first output cell is filled are skipped, so you can run
 * it again after it stops, without paying twice; rows that failed stay empty, so a new run retries them.
 * It stops itself before Apps Script's 6-minute limit.
 */
function hfEnrichSelection() {
  const ui = SpreadsheetApp.getUi();
  const range = SpreadsheetApp.getActiveRange();
  if (!range || range.getNumColumns() !== 1) {
    ui.alert("HappyFetch", "Select the cells with the domains first: one column, one domain per row.", ui.ButtonSet.OK);
    return;
  }
  const answer = ui.prompt("Enrich selected domains", "Which fields? They go into the columns to the right of the selection.\n\nDefault: " + HF_DEFAULT_FIELDS, ui.ButtonSet.OK_CANCEL);
  if (answer.getSelectedButton() !== ui.Button.OK) return;
  const fields = hfFields_([answer.getResponseText().trim() || HF_DEFAULT_FIELDS]);
  const summary = hfEnrichRange_(range, fields, hfApiKey_(), Date.now() + 5 * 60 * 1000);
  ui.alert("HappyFetch", summary, ui.ButtonSet.OK);
}

function hfEnrichRange_(range, fields, key, deadline) {
  const sheet = range.getSheet();
  const rows = range.getNumRows();
  const firstRow = range.getRow();
  const outColumn = range.getColumn() + 1;
  const inputs = range.getValues();
  const existing = sheet.getRange(firstRow, outColumn, rows, fields.length).getValues();
  const todo = [];
  for (let i = 0; i < rows; i++) {
    const ref = String(inputs[i][0] == null ? "" : inputs[i][0]).trim();
    if (ref && existing[i][0] === "") todo.push({ row: i, ref: ref });
  }
  const cache = CacheService.getScriptCache();
  let found = 0, missing = 0, failed = 0, charged = 0, done = 0, firstError = "";
  const BATCH = 20;
  for (let start = 0; start < todo.length; start += BATCH) {
    if (Date.now() > deadline) break;
    const batch = todo.slice(start, start + BATCH);
    const cached = batch.map((t) => cache.get(hfCacheKey_("domain", t.ref)));
    const toFetch = batch.filter((t, i) => !cached[i]);
    const responses = toFetch.length ? UrlFetchApp.fetchAll(toFetch.map((t) => Object.assign({ url: HF_BASE_URL + "/v1/domain" }, hfRequest_("domain", t.ref, key)))) : [];
    let next = 0;
    batch.forEach((t, i) => {
      let result;
      try {
        if (cached[i]) {
          result = JSON.parse(cached[i]);
          result.charged = 0;
        } else {
          result = hfParse_(responses[next++], t.ref);
          cache.put(hfCacheKey_("domain", t.ref), JSON.stringify({ found: result.found, data: result.data, reason: result.reason }), HF_CACHE_SECONDS);
        }
      } catch (e) {
        failed++;
        firstError = firstError || t.ref + ": " + String(e.message || e).replace(/^HappyFetch: /, "");
        return;
      }
      charged += result.charged || 0;
      if (result.found) found++; else missing++;
      // Row by row, so a stop keeps what was paid for and other cells (formulas too) stay untouched.
      sheet.getRange(firstRow + t.row, outColumn, 1, fields.length).setValues([hfRow_(result, fields)]);
    });
    done += batch.length;
  }
  const left = todo.length - done;
  return (
    "Looked up " + done + " of " + todo.length + " domains: " + found + " found, " + missing + " not found" +
    (failed ? ", " + failed + " failed" : "") + ". " + charged + " credits charged." +
    (failed ? "\n\nFailed rows cost nothing and stay empty, so running it again retries them. First failure: " + firstError : "") +
    (left ? "\n\n" + left + " domains are left: run it again to continue." : "")
  );
}
```