eBay sold prices in Google Sheets: free template (2026)

/**
 * eBay Sold Listings API for Google Sheets
 * https://ebaysoldlistingsapi.com/tools/google-sheets-template
 *
 *   =ESLA_MEDIAN("nintendo switch oled")         median sold price
 *   =ESLA_MEDIAN(A2, "ebay.co.uk")               another marketplace, local currency
 *   =ESLA_SOLD(A2)                               the 10 most recent sales as rows
 *   =ESLA_SOLD(A2, "ebay.com", 50)               up to 240 rows
 *   =ESLA_COUNT(A2)                              number of sales found
 *
 * Setup: reload the sheet, then use the menu "Sold prices" > "Set API key".
 * Your key is saved in this script's properties, never in a cell.
 */

var ESLA_API = "https://api.ebaysoldlistingsapi.com/scrape";
var ESLA_CACHE_SECONDS = 6 * 60 * 60; // repeat formulas within 6 hours reuse the answer (no extra request)

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Sold prices")
    .addItem("Set API key", "eslaSetApiKey")
    .addItem("Test connection", "eslaTest")
    .addToUi();
}

function eslaSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var res = ui.prompt(
    "eBay Sold Listings API key",
    "Paste your API key (it starts with eb_). It is stored in this script's properties, not in the sheet.",
    ui.ButtonSet.OK_CANCEL
  );
  if (res.getSelectedButton() !== ui.Button.OK) return;
  var key = res.getResponseText().trim();
  if (key.indexOf("eb_") !== 0) {
    ui.alert("That does not look like an API key. Keys start with eb_. Copy yours from your dashboard.");
    return;
  }
  PropertiesService.getScriptProperties().setProperty("ESLA_API_KEY", key);
  ui.alert("Saved. Your sold price formulas are ready.");
}

function eslaTest() {
  var prices = eslaFetch_("nintendo switch oled", "ebay.com");
  SpreadsheetApp.getUi().alert("Connected. Found " + prices.length + " sales for nintendo switch oled.");
}

function eslaFetch_(keyword, site) {
  keyword = String(keyword || "").trim();
  site = String(site || "ebay.com").trim();
  if (!keyword) throw new Error("Add a search, for example nintendo switch oled.");
  var key = PropertiesService.getScriptProperties().getProperty("ESLA_API_KEY");
  if (!key) throw new Error("Add your API key: menu Sold prices > Set API key.");

  var cache = CacheService.getScriptCache();
  var cacheKey = "esla:" + site + ":" + keyword.toLowerCase();
  var hit = cache.get(cacheKey);
  if (hit) return JSON.parse(hit);

  var url = ESLA_API + "?keyword=" + encodeURIComponent(keyword) +
    "&ebaySite=" + encodeURIComponent(site) + "&count=240&bestOfferPrice=true";
  var res = UrlFetchApp.fetch(url, {
    headers: { Authorization: "Bearer " + key },
    muteHttpExceptions: true
  });
  var code = res.getResponseCode();
  var body = {};
  try { body = JSON.parse(res.getContentText()); } catch (e) { body = {}; }
  if (code === 401) throw new Error("API key not accepted. Set it again from the Sold prices menu.");
  if (code === 402) throw new Error("Monthly requests used up. Upgrade at ebaysoldlistingsapi.com/#pricing");
  if (code === 429) throw new Error("Too many requests this minute. The cell retries when the sheet recalculates.");
  if (code !== 200) throw new Error(body.error || ("Request failed (" + code + "). Try again shortly."));

  var sales = (body.results || []).map(function (r) {
    // Best Offer sales: use the price the buyer actually paid when it is known.
    var paid = r.acceptedOfferPriceStatus === "found" && r.acceptedOfferPrice ? Number(r.acceptedOfferPrice) : Number(r.soldPrice);
    return {
      date: r.endedAt || r.soldDate || "",
      title: r.title || "",
      price: paid,
      shipping: r.shippingType === "free" ? 0 : Number(r.shippingPrice || 0),
      condition: r.condition || "",
      url: r.url || ""
    };
  }).filter(function (s) { return s.price > 0; });

  try { cache.put(cacheKey, JSON.stringify(sales), ESLA_CACHE_SECONDS); } catch (e) { /* too large to cache */ }
  return sales;
}

/**
 * Median eBay sold price for a search.
 * @param {string} keyword What to search, e.g. "nintendo switch oled" or a cell like A2.
 * @param {string} site Optional marketplace: ebay.com (default), ebay.co.uk, ebay.de, ebay.com.au, ebay.ca, ebay.fr, ebay.it, ebay.es.
 * @param {any} refresh Optional. Put TODAY() here to refresh once a day.
 * @return The median sold price in the marketplace's currency.
 * @customfunction
 */
function ESLA_MEDIAN(keyword, site, refresh) {
  var prices = eslaFetch_(keyword, site).map(function (s) { return s.price; }).sort(function (a, b) { return a - b; });
  if (!prices.length) return "No sales found";
  var mid = Math.floor(prices.length / 2);
  return prices.length % 2 ? prices[mid] : (prices[mid - 1] + prices[mid]) / 2;
}

/**
 * Recent eBay sales for a search, one row per sale.
 * @param {string} keyword What to search, or a cell like A2.
 * @param {string} site Optional marketplace, default ebay.com.
 * @param {number} rows Optional number of sales to show, default 10, up to 240.
 * @return Rows of sold date, title, price paid, shipping, condition and link.
 * @customfunction
 */
function ESLA_SOLD(keyword, site, rows) {
  var limit = Math.max(1, Math.min(240, Number(rows) || 10));
  var sales = eslaFetch_(keyword, site).slice(0, limit);
  var out = [["Sold date", "Title", "Price paid", "Shipping", "Condition", "Link"]];
  sales.forEach(function (s) { out.push([s.date, s.title, s.price, s.shipping, s.condition, s.url]); });
  if (out.length === 1) out.push(["No sales found", "", "", "", "", ""]);
  return out;
}

/**
 * Number of recent eBay sales found for a search.
 * @param {string} keyword What to search, or a cell like A2.
 * @param {string} site Optional marketplace, default ebay.com.
 * @customfunction
 */
function ESLA_COUNT(keyword, site) {
  return eslaFetch_(keyword, site).length;
}