Market data in Google Sheets

const BASE = "https://jokerprice.com";
const blank = v => (v === null || v === undefined ? "" : v);

function api_(path) {
  const key = PropertiesService.getScriptProperties().getProperty("JOKERPRICE_API_KEY");
  if (!key) throw new Error("Add JOKERPRICE_API_KEY under Project Settings > Script properties.");
  const res = UrlFetchApp.fetch(BASE + path, { headers: { Authorization: "Bearer " + key }, muteHttpExceptions: true });
  const data = JSON.parse(res.getContentText());
  if (res.getResponseCode() !== 200) throw new Error(data.error.code + ": " + data.error.message);
  return data;
}

function sheet_(name) {
  const book = SpreadsheetApp.getActive();
  return book.getSheetByName(name) || book.insertSheet(name);
}

function refreshMarkets() {
  const header = ["slug", "name", "listings", "gathern", "airbnb", "median_sar", "p25_sar", "p75_sar", "maturity", "occupancy", "adr_sar", "revenue_per_listing_month"];
  const rows = api_("/api/v1/markets").markets.map(m => {
    const t = m.tracker && m.tracker.maturity === "estimating" ? m.tracker : null;
    return [m.slug, m.name, m.listings, m.sources.gathern, m.sources.airbnb, blank(m.nightly.median), blank(m.nightly.p25), blank(m.nightly.p75),
      m.tracker ? m.tracker.maturity : "", t ? t.occupancy : "", t ? blank(t.adr) : "", t ? blank(t.monthlyRevenuePerListing) : ""];
  });
  const sheet = sheet_("Markets");
  sheet.clearContents();
  sheet.getRange(1, 1, 1, header.length).setValues([header]).setFontWeight("bold");
  if (rows.length) sheet.getRange(2, 1, rows.length, header.length).setValues(rows);
  sheet.getRange("J2:J").setNumberFormat("0%");
  sheet.getRange("F2:H").setNumberFormat("#,##0");
  sheet.setFrozenRows(1);
}

// Listings for the slug typed in cell B1 of the Listings sheet.
function refreshListings() {
  const sheet = sheet_("Listings");
  const city = String(sheet.getRange("B1").getValue() || "").trim();
  if (!city) { sheet.getRange("A1:B1").setValues([["City slug:", "riyadh"]]); return; }
  const header = ["source", "id", "title", "district", "type_id", "bedrooms", "nightly_sar", "rating", "reviews", "latitude", "longitude", "url"];
  const rows = [];
  let cursor = "";
  do {
    const page = api_("/api/v1/listings?city=" + encodeURIComponent(city) + "&limit=500" + (cursor ? "&cursor=" + encodeURIComponent(cursor) : ""));
    page.listings.forEach(l => rows.push([l.source, l.id, l.title, blank(l.district), blank(l.typeId), blank(l.bedrooms), blank(l.nightly), blank(l.rating), blank(l.reviews), l.latitude, l.longitude, blank(l.url)]));
    cursor = page.nextCursor;
  } while (cursor);
  sheet.getRange(3, 1, sheet.getMaxRows() - 2, sheet.getMaxColumns()).clearContent();
  sheet.getRange(3, 1, 1, header.length).setValues([header]).setFontWeight("bold");
  if (rows.length) sheet.getRange(4, 1, rows.length, header.length).setValues(rows);
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu("JokerPrice")
    .addItem("Refresh markets", "refreshMarkets")
    .addItem("Refresh listings (city in Listings!B1)", "refreshListings")
    .addToUi();
}