Zillow API for Google Sheets

/**
 * PropertiesAPI for Google Sheets: Zillow data by formula.
 * Docs: https://propertiesapi.com/zillow-api/google-sheets
 * PropertiesAPI is an independent service, not affiliated with Zillow.
 *
 * Setup: Extensions > Apps Script, paste this file, save, reload the sheet,
 * then PropertiesAPI > Set API key. The key is kept in your own user
 * properties for this script, never in a cell.
 */
var PROPERTIESAPI_BASE = "https://api.propertiesapi.com/v1";
var PROPERTIESAPI_DASHBOARD = "https://propertiesapi.com/dashboard";
var PA_CACHE_SECONDS = 21600;     // found homes: 6 hours
var PA_MISS_SECONDS = 3600;       // not found / ambiguous: 1 hour
var PA_PER_REQUEST = 2;          // homes per batch call
var PA_START_BEFORE_MS = 8000;  // no new call after this (Google stops formulas at about 30 s)
var PA_KEY_PROP = "PROPERTIESAPI_KEY";
var PA_ROW = [["Address","address"],["Price","price"],["Status","homeStatus"],["Beds","beds"],["Baths","baths"],["Sqft","livingArea"],["Lot sqft","lotSize"],["Year built","yearBuilt"],["Zestimate","zestimate"],["Rent Zestimate","rentZestimate"],["Days on Zillow","daysOnZillow"],["zpid","zpid"],["Zillow link","detailUrl"]];
var PA_ALIASES = [["price","price"],["status","homeStatus"],["beds","beds"],["baths","baths"],["sqft","livingArea"],["lot","lotSize"],["year_built","yearBuilt"],["zestimate","zestimate"],["rent_zestimate","rentZestimate"],["days_on_zillow","daysOnZillow"],["zpid","zpid"],["url","detailUrl"],["address","address"]];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("PropertiesAPI")
    .addItem("Set API key", "paSetApiKey")
    .addItem("Remove API key", "paRemoveApiKey")
    .addItem("Clear cached results", "paClearCache")
    .addToUi();
}

function onInstall(e) {
  onOpen(e);
}

function paSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var res = ui.prompt("PropertiesAPI", "Paste your API key (from " + PROPERTIESAPI_DASHBOARD + "). It is stored in your user properties for this script, not in the sheet.", ui.ButtonSet.OK_CANCEL);
  if (res.getSelectedButton() !== ui.Button.OK) return;
  var key = String(res.getResponseText() || "").trim();
  if (!/^pa_live_[A-Za-z0-9_-]{43}$/.test(key)) {
    ui.alert("That doesn't look like a PropertiesAPI key (it starts with pa_live_). Nothing was saved.");
    return;
  }
  PropertiesService.getUserProperties().setProperty(PA_KEY_PROP, key);
  paClearCache();
  ui.alert("Saved. Your formulas use this key from now on.");
}

function paRemoveApiKey() {
  PropertiesService.getUserProperties().deleteProperty(PA_KEY_PROP);
  SpreadsheetApp.getUi().alert("Removed. Formulas will ask for a key until you set one again.");
}

/** Starts a fresh cache generation, so the next recalculation looks homes up again. */
function paClearCache() {
  var props = PropertiesService.getUserProperties();
  props.setProperty("PROPERTIESAPI_CACHE_GEN", String(Number(props.getProperty("PROPERTIESAPI_CACHE_GEN") || 0) + 1));
}

/**
 * Zillow data for a home: the price, or the field you name.
 *
 * @param {string} input A Zillow link, a zpid or an address (or a range of them).
 * @param {string} field Optional: price (default), status, beds, baths, sqft, lot, year_built, zestimate, rent_zestimate, days_on_zillow, zpid, url, address, or any API field name.
 * @return The value, or a message when the home can't be looked up.
 * @customfunction
 */
function ZILLOW(input, field) {
  var name = paField_(field);
  if (name.error) return name.error;
  return paMap_(input, function (r) {
    return r.data ? paValue_(r.data[name.field]) : r.message;
  });
}

/**
 * The main fields for a home as one row: address, price, status, beds, baths, sqft, lot, year built, Zestimate, rent Zestimate, days on Zillow, zpid and link.
 *
 * @param {string} input A Zillow link, a zpid or an address (or a column of them).
 * @return One row per home.
 * @customfunction
 */
function ZILLOW_ROW(input) {
  var inputs = paFlatten_(input);
  var results = paLookupAll_(inputs);
  return results.map(function (r) {
    if (!r.data) {
      var row = PA_ROW.map(function () { return ""; });
      row[0] = r.message;
      return row;
    }
    return PA_ROW.map(function (f) { return paValue_(r.data[f[1]]); });
  });
}

/**
 * The header row that matches =ZILLOW_ROW.
 *
 * @return The column names.
 * @customfunction
 */
function ZILLOW_HEADERS() {
  return [PA_ROW.map(function (f) { return f[0]; })];
}

/**
 * Zillow's Zestimate for the home, when Zillow shows one.
 *
 * @param {string} input A Zillow link, a zpid or an address (or a range of them).
 * @return The Zestimate in dollars.
 * @customfunction
 */
function ZESTIMATE(input) {
  return ZILLOW(input, "zestimate");
}

// ---------------------------------------------------------------- internals

function paField_(field) {
  var f = String(field == null || field === "" ? "price" : field).trim();
  for (var i = 0; i < PA_ALIASES.length; i++) if (PA_ALIASES[i][0] === f.toLowerCase()) return { field: PA_ALIASES[i][1] };
  if (/^[A-Za-z][A-Za-z0-9]*$/.test(f)) return { field: f };
  return { error: "Error: unknown field \"" + f + "\". Try price, status, beds, baths, sqft, lot, year_built, zestimate, rent_zestimate, days_on_zillow, zpid, url or address." };
}

function paValue_(v) {
  if (v == null) return "";
  if (Array.isArray(v)) {
    if (v.every(function (x) { return x == null || typeof x !== "object"; })) return v.join(", ");
    return JSON.stringify(v).slice(0, 50000);
  }
  if (typeof v === "object") return JSON.stringify(v).slice(0, 50000);
  return v;
}

function paInput_(v) {
  if (v == null) return "";
  if (typeof v === "number") return isFinite(v) ? String(Math.trunc(v)) : "";
  return String(v).trim();
}

function paFlatten_(input) {
  if (!Array.isArray(input)) return [paInput_(input)];
  return input.map(function (row) { return paInput_(Array.isArray(row) ? row[0] : row); });
}

/** Runs fn over each cell of input (a value or a range) and returns the same shape. */
function paMap_(input, fn) {
  if (!Array.isArray(input)) return fn(paLookupAll_([paInput_(input)])[0]);
  var flat = [];
  input.forEach(function (row) { (Array.isArray(row) ? row : [row]).forEach(function (c) { flat.push(paInput_(c)); }); });
  var results = paLookupAll_(flat);
  var k = 0;
  return input.map(function (row) {
    return (Array.isArray(row) ? row : [row]).map(function () { return fn(results[k++]); });
  });
}

function paCacheKey_(gen, input) {
  var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, input.toLowerCase(), Utilities.Charset.UTF_8);
  return "pa" + gen + ":" + Utilities.base64EncodeWebSafe(digest);
}

/** Looks up each input (with the cache), returning one { data } or { message } per input, in order. */
function paLookupAll_(inputs) {
  var t0 = Date.now();
  var out = inputs.map(function (x) { return x ? null : { message: "" }; });
  var key = PropertiesService.getUserProperties().getProperty(PA_KEY_PROP);
  if (!key) {
    return out.map(function (r) { return r || { message: "Error: no API key. Use the menu PropertiesAPI > Set API key." }; });
  }
  var gen = PropertiesService.getUserProperties().getProperty("PROPERTIESAPI_CACHE_GEN") || "0";
  var cache = CacheService.getUserCache();

  // distinct inputs not answered yet, and what the cache already has
  var todo = [];
  var where = {};
  inputs.forEach(function (x, i) {
    if (!x) return;
    if (!where[x]) { where[x] = []; todo.push(x); }
    where[x].push(i);
  });
  var keys = todo.map(function (x) { return paCacheKey_(gen, x); });
  var hits = keys.length ? cache.getAll(keys) : {};
  var miss = [];
  todo.forEach(function (x, j) {
    var hit = hits[keys[j]];
    if (hit) {
      var r = JSON.parse(hit);
      where[x].forEach(function (i) { out[i] = r; });
    } else {
      miss.push({ input: x, cacheKey: keys[j] });
    }
  });

  for (var s = 0; s < miss.length; s += PA_PER_REQUEST) {
    if (s > 0 && Date.now() - t0 > PA_START_BEFORE_MS) break; // leave the rest for the next run of the formula
    var part = miss.slice(s, s + PA_PER_REQUEST);
    var answers = paBatch_(key, part.map(function (m) { return m.input; }));
    part.forEach(function (m, j) {
      var r = answers[j];
      where[m.input].forEach(function (i) { out[i] = r; });
      if (r.cacheFor) {
        var text = JSON.stringify({ data: r.data, message: r.message });
        if (text.length > 90000 && r.data) text = JSON.stringify({ data: paSmall_(r.data) });
        if (text.length <= 90000) cache.put(m.cacheKey, text, r.cacheFor);
      }
    });
  }
  return out.map(function (r) {
    return r ? { data: r.data, message: r.message } : { message: "Not looked up yet (Google's time limit for one formula). Enter the formula again to continue; homes already found come from the cache." };
  });
}

/** The fields =ZILLOW_ROW needs, for a record too large to cache whole. */
function paSmall_(d) {
  var o = {};
  PA_ROW.forEach(function (f) { o[f[1]] = d[f[1]]; });
  return o;
}

/** One POST /v1/zillow/batch call. Returns one { data, cacheFor } or { message, cacheFor? } per item. */
function paBatch_(key, items) {
  var res;
  try {
    res = UrlFetchApp.fetch(PROPERTIESAPI_BASE + "/zillow/batch", {
      method: "post",
      contentType: "application/json",
      headers: { "x-api-key": key },
      payload: JSON.stringify({ items: items }),
      muteHttpExceptions: true,
    });
  } catch (e) {
    return items.map(function () { return { message: "Error: couldn't reach PropertiesAPI. Try again shortly." }; });
  }
  var code = res.getResponseCode();
  var body = null;
  try { body = JSON.parse(res.getContentText()); } catch (e) { body = null; }
  if (code !== 200 || !body || !Array.isArray(body.data)) {
    var err = (body && body.error) || { code: "http_" + code, message: "PropertiesAPI answered HTTP " + code + "." };
    var msg = paError_(err, code);
    return items.map(function () { return { message: msg }; });
  }
  return items.map(function (_, i) {
    var r = body.data[i];
    if (r && r.ok) return { data: r.data, cacheFor: PA_CACHE_SECONDS };
    var e = (r && r.error) || { code: "unknown", message: "No answer for this item." };
    return { message: paError_(e, 0), cacheFor: e.code === "not_found" || e.code === "address_ambiguous" ? PA_MISS_SECONDS : 0 };
  });
}

/** The message a cell shows for an API error. */
function paError_(e, status) {
  var d = e.details || {};
  switch (e.code) {
    case "missing_api_key":
    case "invalid_api_key":
      return "Error: invalid API key. Set it again with PropertiesAPI > Set API key.";
    case "revoked_api_key":
      return "Error: this API key was revoked. Create a new one at " + PROPERTIESAPI_DASHBOARD + " and set it with PropertiesAPI > Set API key.";
    case "credits_exhausted":
      return "Error: out of credits. Upgrade or check your usage at " + (d.upgrade_url || PROPERTIESAPI_DASHBOARD + "#billing");
    case "spend_limit_reached":
      return "Error: your monthly spending limit is reached. Raise or remove it at " + (d.dashboard_url || PROPERTIESAPI_DASHBOARD);
    case "address_ambiguous":
      return "Error: address ambiguous. Zillow's closest match is a different home" + (d.candidate && d.candidate.detailUrl ? ": " + d.candidate.detailUrl + " (use that link if it's the one)" : "") + ". Or add the unit, city or ZIP.";
    case "not_found":
      return "Not found";
    case "bad_param":
    case "missing_param":
      return "Error: not a Zillow link, zpid or address (" + e.message + ")";
    case "rate_limited":
      return "Error: too many lookups this minute. Try again in " + (d.retry_after || 60) + " seconds.";
    default:
      return "Error: " + (e.message || e.code || "HTTP " + status);
  }
}