Zillow API for Google Sheets

// Extensions → Apps Script. Paste this, then save.
// Project Settings → Script properties → add PROPERTIESAPI_KEY = your key.
const BASE = "https://api.propertiesapi.com/v1";

function zillowLookup_(input) {
  input = String(input || "").trim();
  if (!input) return null;

  // Cache for 6 hours so recalculating the sheet doesn't repeat lookups.
  const cache = CacheService.getScriptCache();
  const cacheKey = Utilities.base64EncodeWebSafe(input).slice(0, 240);
  const hit = cache.get(cacheKey);
  if (hit) return JSON.parse(hit);

  const param = /^https?:\/\//i.test(input) ? "url" : "address";
  const res = UrlFetchApp.fetch(BASE + "/zillow/property?" + param + "=" + encodeURIComponent(input), {
    headers: { "x-api-key": PropertiesService.getScriptProperties().getProperty("PROPERTIESAPI_KEY") },
    muteHttpExceptions: true,
  });
  const code = res.getResponseCode();
  if (code === 404) return null;                       // no matching Zillow property (not billed)
  if (code !== 200) throw new Error("PropertiesAPI HTTP " + code);

  const data = JSON.parse(res.getContentText()).data;   // body is { data, meta }
  const text = JSON.stringify(data);
  if (text.length < 90000) cache.put(cacheKey, text, 21600);
  return data;
}

/** Listing price from a Zillow link or address. @customfunction */
function ZILLOW_PRICE(input) { const h = zillowLookup_(input); return h ? h.price : "not found"; }

/** Zestimate from a Zillow link or address. @customfunction */
function ZILLOW_ZESTIMATE(input) { const h = zillowLookup_(input); return h ? h.zestimate : "not found"; }

/** Rent Zestimate from a Zillow link or address. @customfunction */
function ZILLOW_RENT(input) { const h = zillowLookup_(input); return h ? h.rentZestimate : "not found"; }

/** Listing status from a Zillow link or address. @customfunction */
function ZILLOW_STATUS(input) { const h = zillowLookup_(input); return h ? h.homeStatus : "not found"; }

/** Any top-level field, e.g. =ZILLOW_FIELD(A2, "beds"). @customfunction */
function ZILLOW_FIELD(input, field) { const h = zillowLookup_(input); return h ? h[field] : "not found"; }

// For long lists: one menu click fills a whole column block using the batch endpoint.
// Column A = Zillow links or addresses (row 1 is a header). Writes columns B to H.
function onOpen() {
  SpreadsheetApp.getUi().createMenu("Zillow").addItem("Fill sheet", "fillSheet").addToUi();
}

function fillSheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const inputs = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues().map((r) => String(r[0]).trim());
  const key = PropertiesService.getScriptProperties().getProperty("PROPERTIESAPI_KEY");
  const out = [];

  for (let i = 0; i < inputs.length; i += 50) {
    const chunk = inputs.slice(i, i + 50);
    const items = chunk.filter(Boolean);                 // links or addresses
    const res = UrlFetchApp.fetch("https://api.propertiesapi.com/v1/zillow/batch", {
      method: "post",
      contentType: "application/json",
      headers: { "x-api-key": key },
      payload: JSON.stringify({ items }),
      muteHttpExceptions: true,
    });
    if (res.getResponseCode() !== 200) throw new Error("PropertiesAPI HTTP " + res.getResponseCode());
    const results = JSON.parse(res.getContentText()).data;   // same order as items, each { index, ok, data }
    let j = 0;
    chunk.forEach((x) => {
      const r = x ? results[j++] : null;
      const h = r && r.ok ? r.data : null;
      out.push(h ? [h.homeStatus, h.price, h.zestimate, h.rentZestimate, h.beds, h.baths, h.livingArea] : ["not found", "", "", "", "", "", ""]);
    });
  }
  sheet.getRange(1, 2, 1, 7).setValues([["Status", "Price", "Zestimate", "Rent Zestimate", "Beds", "Baths", "Sqft"]]);
  sheet.getRange(2, 2, out.length, 7).setValues(out);
}