=VERIFY_EMAIL() and =FIND_EMAIL() in Google Sheets

// Cold Leads custom functions for Google Sheets (Extensions → Apps Script, paste into Code.gs).
// Key: Project Settings → Script Properties → Add script property COLDLEADS_API_KEY = sk_...
const COLDLEADS_API = 'https://coldleads.app/api/v1';
const CACHE_SECONDS = 21600; // 6 hours, the longest CacheService keeps an entry

/**
 * Verifies an e-mail address with Cold Leads. Fills three cells: status, score, last reason code.
 * Costs 1 Cold Leads credit unless the same address was answered from this script's cache.
 *
 * @param {string} email The address, for example A2.
 * @return Status, score and reason.
 * @customfunction
 */
function VERIFY_EMAIL(email) {
  const address = String(email || '').trim().toLowerCase();
  if (!address) return '';
  const r = cachedCall_('verify:' + address, '/verify', { email: address, timeout_ms: 20000 }, function (res) {
    return res.reasons[res.reasons.length - 1] !== 'timeout'; // timeouts are worth checking again
  });
  return [[r.status, r.score, r.reasons[r.reasons.length - 1]]];
}

/**
 * Finds the most likely e-mail address for a person at a company domain. Fills three cells:
 * address, method ("verified" only when a mail server confirmed the mailbox, otherwise "pattern"), confidence.
 * Costs 1 Cold Leads credit unless answered from this script's cache.
 *
 * @param {string} first First name, for example B2.
 * @param {string} last Last name, for example C2.
 * @param {string} domain Company domain or website, for example D2.
 * @return Address, method and confidence.
 * @customfunction
 */
function FIND_EMAIL(first, last, domain) {
  const body = { first: String(first || '').trim(), last: String(last || '').trim(), domain: String(domain || '').trim().toLowerCase() };
  if (!body.domain || !(body.first || body.last)) return '';
  const key = 'find:' + [body.first, body.last, body.domain].join('|').toLowerCase();
  const r = cachedCall_(key, '/find', body, function () { return true; });
  return [[r.email || '', r.method, r.confidence]];
}

function cachedCall_(cacheKey, path, payload, cacheable) {
  const cache = CacheService.getScriptCache();
  // cache keys are limited to 250 characters, so store a hash of the key
  const id = Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, cacheKey, Utilities.Charset.UTF_8));
  const hit = cache.get(id);
  if (hit) return JSON.parse(hit);
  const apiKey = PropertiesService.getScriptProperties().getProperty('COLDLEADS_API_KEY');
  if (!apiKey) throw new Error('Add the script property COLDLEADS_API_KEY (Project Settings → Script Properties).');
  const res = UrlFetchApp.fetch(COLDLEADS_API + path, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': apiKey },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  });
  const data = JSON.parse(res.getContentText() || '{}');
  if (res.getResponseCode() !== 200) throw new Error('Cold Leads: ' + (data.error || 'HTTP ' + res.getResponseCode()));
  if (cacheable(data)) cache.put(id, JSON.stringify(data), CACHE_SECONDS);
  return data;
}