The data behind THE ENTITY, as an API

/**
 * THE ENTITY Data — Google Sheets client.
 *
 * INSTALL (no Marketplace needed):
 *   1. Extensions > Apps Script. Delete any sample, paste THIS file as "Code.gs".
 *   2. Add an HTML file named exactly "Sidebar" and paste the Sidebar snippet.
 *   3. Save, then reload the Sheet. A "THE ENTITY Data" menu appears.
 *   4. THE ENTITY Data > Set API key — paste your entk_live_ key ONCE
 *      (this also grants the one-time network permission; custom functions
 *      can't prompt for it themselves).
 * Then: =ENTITY("/graph/nodes","q=hbm")  — or  THE ENTITY Data > Pull to table.
 *
 * Your key is stored in your user properties (never in a cell) and sent only in
 * the Authorization header. Read-only: every call is a GET.
 *
 * SHARED SHEETS: by a Google rule, =ENTITY() in a cell reads the SHEET OWNER'S
 * key (not the invoking editor's). So on a solo/owned sheet =ENTITY is fully
 * per-you; on a sheet shared with other editors, those editors should use
 * "THE ENTITY Data > Pull to table" (the sidebar runs as the active user and
 * reads THAT user's key), not =ENTITY.
 */
var ENTITY_BASE = 'https://joinentity.com/api/v1';
var ENTITY_KEY_PROP = 'ENTITY_API_KEY';
var ENTITY_PAGE_LIMIT = 200;        // server max page size
var ENTITY_CELL_ROW_CAP = 1000;     // =ENTITY() in-cell row cap (the 30s wall)
var ENTITY_CELL_TIME_MS = 25000;    // =ENTITY() time budget, under the 30s wall
var ENTITY_IMPORT_ROW_CAP = 50000;  // sidebar "Pull to table" cap
var ENTITY_IMPORT_TIME_MS = 300000; // sidebar time budget (server ~6-min limit)

/* ---- menu + key management (public: reachable from the menu / dialog) ---- */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('THE ENTITY Data')
    .addItem('Set API key…', 'entitySetKey')
    .addItem('Test connection', 'entityTestConnection')
    .addSeparator()
    .addItem('Pull to table…', 'entityShowSidebar')
    .addSeparator()
    .addItem('Clear API key', 'entityClearKey')
    .addToUi();
}

function entitySetKey() {
  var html = HtmlService.createHtmlOutput(
    '<div style="font:13px -apple-system,Segoe UI,Roboto,sans-serif;padding:4px">' +
    '<p>Paste your <b>entk_live_</b> key. Stored privately (never in a cell).</p>' +
    '<form onsubmit="return save()">' +
    '<input id="k" type="password" placeholder="entk_live_…" style="width:100%;box-sizing:border-box;padding:6px">' +
    '<p style="text-align:right;margin:10px 0 0"><button type="submit">Save key</button></p></form>' +
    '<script>function save(){var v=document.getElementById("k").value;' +
    'google.script.run.withSuccessHandler(function(){google.script.host.close()})' +
    '.withFailureHandler(function(e){alert(e.message)}).entitySaveKey(v);return false;}</script></div>'
  ).setWidth(380).setHeight(150);
  SpreadsheetApp.getUi().showModalDialog(html, 'Set API key');
}

// PUBLIC (no trailing underscore) so the dialog's google.script.run can reach it.
function entitySaveKey(key) {
  key = String(key || '').trim();
  if (!key) throw new Error('No key provided.');
  PropertiesService.getUserProperties().setProperty(ENTITY_KEY_PROP, key);
  // Touch the network so the script.external_request scope is granted now.
  try { UrlFetchApp.fetch(ENTITY_BASE + '/whoami', { method: 'get', muteHttpExceptions: true, headers: { Authorization: 'Bearer ' + key } }); } catch (e) {}
  return true;
}

function entityClearKey() {
  PropertiesService.getUserProperties().deleteProperty(ENTITY_KEY_PROP);
  SpreadsheetApp.getUi().alert('THE ENTITY Data', 'API key cleared.', SpreadsheetApp.getUi().ButtonSet.OK);
}

function entityTestConnection() {
  var ui = SpreadsheetApp.getUi();
  try {
    // /whoami is NOT enveloped — tier/scopes/usage sit at the top level.
    var body = entityFetch_('/whoami', '');
    var info = { tier: body.tier, scopes: body.scopes, usage: body.usage };
    ui.alert('THE ENTITY Data', 'Connected. ' + JSON.stringify(info).slice(0, 400), ui.ButtonSet.OK);
  } catch (e) {
    ui.alert('THE ENTITY Data', 'Failed: ' + e.message, ui.ButtonSet.OK);
  }
}

function entityShowSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('THE ENTITY Data — Pull to table');
  SpreadsheetApp.getUi().showSidebar(html);
}

/* ---- the =ENTITY() custom function ---- */

/**
 * Pull a THE ENTITY Data endpoint into cells (spills to the right + down).
 *   =ENTITY("/smart-money/funds","scores=true")
 *   =ENTITY("/graph/nodes/hbm")
 * @param {string} path      Endpoint path, e.g. "/graph/nodes".
 * @param {string} query     Optional query string, e.g. "q=hbm&limit=20".
 * @param {any} refreshToken Optional: reference a cell you bump to force a refresh
 *                           (custom functions do not auto-refresh on a timer).
 * @return A table (header row + rows), or a readable message.
 * @customfunction
 */
function ENTITY(path, query, refreshToken) {
  if (path === undefined || path === null || path === '') return 'Usage: =ENTITY("/graph/nodes","q=hbm")';
  try {
    var res = entityCollect_(String(path), query == null ? '' : String(query), ENTITY_CELL_ROW_CAP, ENTITY_CELL_TIME_MS);
    if (res.degraded) return 'THE ENTITY: ' + res.degraded;
    if (!res.rows.length) return [['(no rows)' + (res.note ? ' — ' + res.note : '')]];
    return res.rows;
  } catch (e) {
    return 'THE ENTITY: ' + e.message;
  }
}

/* ---- sidebar server function (PUBLIC: reached via google.script.run) ---- */

function entityImportToActiveCell(path, query) {
  var res = entityCollect_(String(path || ''), String(query || ''), ENTITY_IMPORT_ROW_CAP, ENTITY_IMPORT_TIME_MS);
  if (res.degraded) throw new Error(res.degraded);
  var rows = res.rows.length ? res.rows : [['(no rows)' + (res.note ? ' — ' + res.note : '')]];
  var width = 1;
  for (var i = 0; i < rows.length; i++) width = Math.max(width, rows[i].length);
  for (var j = 0; j < rows.length; j++) { while (rows[j].length < width) rows[j].push(''); }
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var range = sheet.getRange(cell.getRow(), cell.getColumn(), rows.length, width);
  range.setValues(rows);
  // Honesty note on the top-left cell: the data's own date vs when we ingested it.
  var note = [];
  if (res.asOf) note.push('as_of: ' + res.asOf);
  if (res.detectedAt) note.push('detected_at: ' + res.detectedAt);
  if (res.capped) note.push('(capped at ' + ENTITY_IMPORT_ROW_CAP + ' rows)');
  else if (res.note) note.push('(' + res.note + ')');
  if (note.length) sheet.getRange(cell.getRow(), cell.getColumn()).setNote(note.join('\n'));
  return { rows: rows.length, cols: width, capped: !!res.capped };
}

// The endpoint catalog for the sidebar dropdown (PUBLIC: read via google.script.run).
function entityEndpointCatalog() {
  return [{"path":"/search","dataset":"graph","summary":"Full-text search across the graph — nodes, domains, themes and companies (ranked).","example":"/search?q=hbm"},{"path":"/graph/layers","dataset":"graph","summary":"The five value-chain layers and their domains (full taxonomy).","example":"/graph/layers"},{"path":"/graph/nodes","dataset":"graph","summary":"List causal-graph nodes (summary projection), filterable + paginated.","example":"/graph/nodes?layerId=chips&limit=20"},{"path":"/graph/nodes/{id}","dataset":"graph","summary":"One node, full detail (all summaries, watch items, metrics, sources).","example":"/graph/nodes/{id}"},{"path":"/graph/edges","dataset":"graph","summary":"Company↔company relationships (supplier/customer/partner/competitor).","example":"/graph/edges?company=nvidia"},{"path":"/graph/relations","dataset":"graph","summary":"Typed node→node causal relations (strength, elasticity, latency).","example":"/graph/relations?nodeId=foundry-capacity"},{"path":"/graph/facilities","dataset":"graph","summary":"Physical sites (fabs, plants, ports) geolocated + hooked to nodes.","example":"/graph/facilities?geographyId=taiwan"},{"path":"/graph/chokepoints","dataset":"graph","summary":"Structural pinch points (straits, ports, jurisdictions) + affected nodes.","example":"/graph/chokepoints?kind=strait"},{"path":"/graph/companies/{id}/exposures","dataset":"graph","summary":"One company's exposures to chain nodes (intensity 0–100).","example":"/graph/companies/{id}/exposures"},{"path":"/engine/nowcast","dataset":"engine","summary":"Constraint Nowcast — system tightness index (0–100), per-layer + movers.","example":"/engine/nowcast"},{"path":"/engine/min-cut","dataset":"engine","summary":"Min cut of the Energy→Applications flow network (structural exhibit).","example":"/engine/min-cut"},{"path":"/engine/forecast","dataset":"engine","summary":"Probabilistic bottleneck-migration forecast (seeded Monte-Carlo).","example":"/engine/forecast?horizonQuarters=6"},{"path":"/engine/conviction","dataset":"engine","summary":"Conviction record from D1 (per-company score/verdict/drivers).","example":"/engine/conviction?company=nvidia"},{"path":"/engine/node-scores","dataset":"engine","summary":"Daily node-score record from D1 (global/tension/news/timing).","example":"/engine/node-scores?node=hbm&days=30"},{"path":"/screener/accumulation","dataset":"smart-money","summary":"DERIVED screen: companies the tracked elite are net-accumulating AND the engine agrees (ranked).","example":"/screener/accumulation?min_holders=3&min_conviction=60"},{"path":"/smart-money/funds","dataset":"smart-money","summary":"The tracked-elite fund roster (optionally scored), + a labeled platform sidecar.","example":"/smart-money/funds?scores=true"},{"path":"/smart-money/funds/{cik}/holdings","dataset":"smart-money","summary":"One fund's archived 13F book across quarters (filing-dated archive).","example":"/smart-money/funds/{cik}/holdings"},{"path":"/smart-money/consensus","dataset":"smart-money","summary":"Universe consensus: companies ≥1 tracked-elite fund holds, joined to the verdict.","example":"/smart-money/consensus?min_holders=3"},{"path":"/smart-money/tape","dataset":"smart-money","summary":"THE TAPE — archive-basis fund entry/exit/accel/cool events (display-only).","example":"/smart-money/tape?type=entry"},{"path":"/smart-money/clone-book","dataset":"smart-money","summary":"The Long/Short Clone Book snapshot + backtest curve (display-only, filing-archive).","example":"/smart-money/clone-book"},{"path":"/smart-money/companies/{company}/census","dataset":"smart-money","summary":"One company's FULL-census 13F holder history (all filers, the honest denominator).","example":"/smart-money/companies/{company}/census"},{"path":"/smart-money/companies/{company}/holders","dataset":"smart-money","summary":"The tracked-elite fund book for one company (value ladder + optional trajectory).","example":"/smart-money/companies/{company}/holders?history=90"},{"path":"/feeds/insider-trades","dataset":"feeds","summary":"SEC Form 4 open-market insider buys/sells (transaction-dated, not our detection).","example":"/feeds/insider-trades?company=NVDA"},{"path":"/feeds/insider-positions","dataset":"feeds","summary":"SEC Form 3/5 insider holdings (positions owned) — periodOfReport / filedAt / detectedAt.","example":"/feeds/insider-positions?company=NVDA"},{"path":"/feeds/ownership-events","dataset":"feeds","summary":"Filing-sourced ownership events (13D/G, Form 144, congress PTRs, new-insiders, reveals).","example":"/feeds/ownership-events?window_days=60"},{"path":"/feeds/short-interest","dataset":"feeds","summary":"FINRA bi-monthly short interest — a symbol's cycles, or the latest crowded names.","example":"/feeds/short-interest?ticker=NVDA"},{"path":"/feeds/short-volume","dataset":"feeds","summary":"FINRA daily short-volume (tradeDate vs detectedAt; regSho tri-state, never coerced).","example":"/feeds/short-volume?ticker=NVDA&days=30"},{"path":"/cited/chain-mentions","dataset":"graph","summary":"Grounded, verbatim value-chain mentions from earnings calls (cited, source-linked).","example":"/cited/chain-mentions?company=NVDA&node=hbm"},{"path":"/cited/extracted-metrics","dataset":"graph","summary":"Corroboration-gated metric series extracted from cited sources (uncorroborated suppressed).","example":"/cited/extracted-metrics?node=cowos"}];
}

/* ---- internals (trailing underscore = private to the script) ---- */

// Fetch ONE page. Throws a readable Error on any {ok:false}/HTTP>=400.
function entityFetch_(path, query) {
  var url = ENTITY_BASE + entityNormPath_(path);
  if (query) url += (url.indexOf('?') >= 0 ? '&' : '?') + String(query).replace(/^[?&]/, '');
  var resp = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true,
    headers: { Authorization: 'Bearer ' + entityGetKey_() },
  });
  var code = resp.getResponseCode();
  var body = {};
  try { body = JSON.parse(resp.getContentText() || '{}'); } catch (e) { body = {}; }
  if (code >= 400 || body.ok === false) {
    throw new Error(entityErrMsg_((body && body.error) || ('http_' + code), resp));
  }
  return body;
}

// Collect (paginating under caps) + flatten to a 2-D array. Buffers all rows so
// the header set is the UNION across every page (a key that first appears on
// page 2 still becomes a column). Returns { rows, asOf, detectedAt, meta, capped,
// degraded?, note? }.
function entityCollect_(path, query, rowCap, timeMs) {
  var start = Date.now();
  var objects = [];   // accumulated row-objects (array-of-objects endpoints)
  var primitives = []; // accumulated primitive rows (array-of-primitives)
  var kv = null;      // a single detail object → field/value
  var offset = 0;
  var userPaged = /(?:^|[?&])(?:limit|offset)=/.test(query || '');
  var last = null;
  var capped = false;
  var sawCollection = false;
  var stoppedEarly = false;
  while (true) {
    var q = userPaged ? query : entityMergeQuery_(query, 'limit=' + ENTITY_PAGE_LIMIT + '&offset=' + offset);
    var body = entityFetch_(path, q);
    last = body;
    var data = body.data;
    if (data === null || data === undefined) {
      if (sawCollection) { stoppedEarly = true; break; } // a later page degraded — flag it, don't present partial as full
      return { rows: [], degraded: 'unavailable' + (entityMetaNote_(body) ? ' — ' + entityMetaNote_(body) : ''), meta: body.meta || null };
    }
    // The collection: an array, or (defensive) the sole array field of an object
    // that carries a top-level page — a nested paginated collection.
    var coll = null;
    if (Array.isArray(data)) coll = data;
    else if (data && typeof data === 'object' && body.page) coll = entitySoleArrayField_(data);

    if (coll !== null) {
      sawCollection = true;
      for (var i = 0; i < coll.length; i++) {
        var v = coll[i];
        if (v !== null && typeof v === 'object') objects.push(v); else primitives.push(v);
        if (objects.length + primitives.length >= rowCap) { capped = true; break; }
      }
      var page = body.page;
      if (userPaged || capped || !page || page.hasMore !== true) break;
      offset = (page.nextOffset !== null && page.nextOffset !== undefined) ? page.nextOffset : (offset + ENTITY_PAGE_LIMIT);
      if (Date.now() - start > timeMs) { capped = true; break; }
      continue;
    }
    if (data && typeof data === 'object') { kv = data; break; } // detail object (not paginated)
    primitives.push(data); break; // a bare primitive
  }

  var out = [];
  if (kv) {
    out.push(['field', 'value']);
    var keys = Object.keys(kv);
    for (var k = 0; k < keys.length; k++) out.push([keys[k], entityCell_(kv[keys[k]])]);
  } else if (objects.length) {
    var headers = entityUnionKeys_(objects); // union across ALL pages
    out.push(headers);
    for (var r = 0; r < objects.length; r++) {
      var o = objects[r];
      out.push(headers.map(function (h) { return entityCell_(o ? o[h] : null); }));
    }
  } else if (primitives.length) {
    out.push(['value']);
    for (var p = 0; p < primitives.length; p++) out.push([entityCell_(primitives[p])]);
  } else {
    // Empty collection — valid, just no rows. Surface any meta status honestly.
    return { rows: [], note: entityMetaNote_(last) || 'no rows', asOf: last ? (last.as_of || null) : null, meta: last ? (last.meta || null) : null };
  }
  return {
    rows: out,
    asOf: last ? (last.as_of || null) : null,
    detectedAt: last ? (last.detected_at || null) : null,
    meta: last ? (last.meta || null) : null,
    capped: capped,
    note: stoppedEarly ? 'incomplete — a later page was unavailable' : (capped ? 'capped' : ''),
  };
}

function entityGetKey_() {
  var k = PropertiesService.getUserProperties().getProperty(ENTITY_KEY_PROP);
  if (!k) throw new Error('set your API key first (THE ENTITY Data > Set API key).');
  return k;
}

function entityNormPath_(path) {
  path = String(path || '').trim();
  if (path.indexOf('http') === 0) path = path.replace(ENTITY_BASE, ''); // tolerate a full URL
  if (path.charAt(0) !== '/') path = '/' + path;
  return path;
}

function entityMergeQuery_(base, extra) {
  base = String(base || '').replace(/^[?&]/, '');
  return base ? base + '&' + extra : extra;
}

function entityUnionKeys_(arr) {
  var keys = [];
  var seen = {};
  for (var i = 0; i < arr.length; i++) {
    var o = arr[i];
    if (o && typeof o === 'object') {
      for (var k in o) { if (Object.prototype.hasOwnProperty.call(o, k) && !seen[k]) { seen[k] = 1; keys.push(k); } }
    }
  }
  return keys.length ? keys : ['value'];
}

function entityCell_(v) {
  if (v === null || v === undefined) return '';
  if (typeof v === 'object') { try { return JSON.stringify(v); } catch (e) { return String(v); } }
  return v;
}

// A PURE single-list wrapper's array (e.g. { rows: [...] }), else null. A record
// that carries a list ALONGSIDE other fields (a detail object like census
// { latest, trend, … }) is NOT a wrapper and must keep all its fields.
function entitySoleArrayField_(obj) {
  var keys = Object.keys(obj);
  if (keys.length === 1 && Array.isArray(obj[keys[0]])) return obj[keys[0]];
  return null;
}

// A short human status from the envelope meta (empty / rebuilding / not found …), or ''.
function entityMetaNote_(body) {
  var m = body && body.meta;
  if (!m) return '';
  if (m.reason) return String(m.reason);
  if (m.rebuilding) return 'rebuilding';
  if (m.empty) return 'no data yet';
  if (m.found === false) return 'not found';
  if (m.available === false) return 'not available';
  if (m.degraded) return 'degraded';
  if (m.note) return String(m.note);
  return '';
}

function entityErrMsg_(code, resp) {
  var map = {
    missing_key: 'no API key sent',
    invalid_key: 'invalid API key',
    inactive_addon: 'your THE ENTITY Data plan is not active',
    surface_not_in_tier: 'this surface is not in your tier',
    scope_denied: 'your key lacks the scope for this dataset',
    rate_limited: 'rate limited',
    quota_exceeded: 'monthly quota exhausted',
    unavailable: 'temporarily unavailable — try again shortly',
  };
  var msg = map[code] || code;
  if (code === 'rate_limited' && resp) {
    try {
      var h = resp.getHeaders() || {};
      var ra = h['Retry-After'] || h['retry-after'];
      if (ra) msg += ' — retry in ' + ra + 's';
    } catch (e) {}
  }
  return msg;
}

<!-- THE ENTITY Data — add this as an HTML file named exactly "Sidebar". -->
<!DOCTYPE html>
<html>
  <head><base target="_top">
    <style>
      body { font: 13px -apple-system, Segoe UI, Roboto, sans-serif; margin: 0; padding: 12px; color: #1a1c1f; }
      label { display: block; margin: 10px 0 4px; font-weight: 600; }
      select, input { width: 100%; box-sizing: border-box; padding: 6px; }
      button { margin-top: 12px; padding: 8px 12px; cursor: pointer; }
      .hint { color: #6b7076; font-size: 12px; margin: 6px 0 0; }
      #status { margin-top: 12px; white-space: pre-wrap; }
    </style>
  </head>
  <body>
    <p class="hint">Pulls the FULL result (all pages) into a static table starting at your active cell.</p>
    <label for="ep">Endpoint</label>
    <select id="ep"><option value="">Loading…</option></select>
    <label for="path">Path</label>
    <input id="path" placeholder="/graph/nodes">
    <label for="q">Query (optional)</label>
    <input id="q" placeholder="q=hbm">
    <button id="go" onclick="pull()">Pull to table</button>
    <p id="status" class="hint"></p>
    <script>
      var CAT = [];
      function fill() {
        google.script.run.withSuccessHandler(function (rows) {
          CAT = rows || [];
          var sel = document.getElementById('ep');
          sel.innerHTML = '<option value="">— choose an endpoint —</option>';
          CAT.forEach(function (e, i) {
            var o = document.createElement('option');
            o.value = String(i); o.textContent = '[' + e.dataset + '] ' + e.path;
            sel.appendChild(o);
          });
        }).entityEndpointCatalog();
      }
      document.getElementById('ep').addEventListener('change', function () {
        var e = CAT[this.value];
        if (!e) return;
        var ex = String(e.example || e.path);
        var qi = ex.indexOf('?');
        document.getElementById('path').value = qi >= 0 ? ex.slice(0, qi) : ex;
        document.getElementById('q').value = qi >= 0 ? ex.slice(qi + 1) : '';
        var ph = /\{[^}]*\}/.test(document.getElementById('path').value);
        document.getElementById('status').textContent = (e.summary || '') + (ph ? '  ⚠ replace the {…} in the path with a real value' : '');
      });
      function pull() {
        var path = document.getElementById('path').value.trim();
        var q = document.getElementById('q').value.trim();
        if (!path) { document.getElementById('status').textContent = 'Enter a path first.'; return; }
        if (/\{[^}]*\}/.test(path)) { document.getElementById('status').textContent = 'Replace the {…} placeholder in the path with a real value first.'; return; }
        var btn = document.getElementById('go'); btn.disabled = true;
        document.getElementById('status').textContent = 'Pulling…';
        google.script.run
          .withSuccessHandler(function (r) { btn.disabled = false; document.getElementById('status').textContent = 'Wrote ' + r.rows + ' rows × ' + r.cols + ' cols' + (r.capped ? ' (capped)' : '') + '.'; })
          .withFailureHandler(function (e) { btn.disabled = false; document.getElementById('status').textContent = 'THE ENTITY: ' + e.message; })
          .entityImportToActiveCell(path, q);
      }
      fill();
    </script>
  </body>
</html>