Reseller spreadsheet. With live sold prices.

/** * Soldgraph reseller spreadsheet: fills in sold prices from eBay, Mercari and Poshmark. * Get a free API key at https://soldgraph.com/signup * @OnlyCurrentDoc */const API = "https://api.soldgraph.com";const SHEET = "Inventory";const HEADERS = ["Item", "Marketplace", "Cost", "Median sold", "Typical low", "Typical high", "Sales found", "Est. fees", "Est. profit", "Updated"];const MARKETS = {  ebay: { endpoint: "ebay/sold", count: 200 },  mercari: { endpoint: "mercari/sold", count: 100 },  poshmark: { endpoint: "poshmark/sold" },};// The Free plan allows 10 searches in flight at once, so batches stay at 10.const BATCH_SIZE = 10;// Apps Script stops a run after 6 minutes. Leave room to write what came back.const TIME_LIMIT_MS = 4.5 * 60 * 1000;const FEES =  '=ARRAYFORMULA(IF(D2:D="", "", IF(B2:B="mercari", D2:D*0.1, IF(B2:B="poshmark", IF(D2:D<15, 2.95, D2:D*0.2), D2:D*0.136 + IF(D2:D>10, 0.4, 0.3)))))';const PROFIT = '=ARRAYFORMULA(IF(D2:D="", "", D2:D - H2:H - C2:C))'; function onOpen() {  SpreadsheetApp.getUi()    .createMenu("Soldgraph")    .addItem("Fill in missing prices", "fillMissingPrices")    .addItem("Refresh all prices", "refreshAllPrices")    .addSeparator()    .addItem("Set up the Inventory sheet", "setUpSheet")    .addItem("Set API key", "setApiKey")    .addToUi();} function setApiKey() {  const ui = SpreadsheetApp.getUi();  const res = ui.prompt("Soldgraph API key", "Paste your key. It starts with sg_.", ui.ButtonSet.OK_CANCEL);  if (res.getSelectedButton() !== ui.Button.OK) return;  const key = res.getResponseText().trim();  if (!/^sg_\S+$/.test(key)) return ui.alert("That doesn't look like a Soldgraph key. Keys start with sg_.");  PropertiesService.getScriptProperties().setProperty("SOLDGRAPH_KEY", key);  ui.alert("Key saved. It's kept in this sheet's script, not in a cell.");} function setUpSheet() {  const ui = SpreadsheetApp.getUi();  const ss = SpreadsheetApp.getActive();  let sheet = ss.getSheetByName(SHEET);  if (sheet && sheet.getLastRow() > 0) return ui.alert("There's already an Inventory sheet with data in it. Rename it to set up a new one.");  if (!sheet) sheet = ss.insertSheet(SHEET);  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]).setFontWeight("bold");  sheet.setFrozenRows(1);  sheet.getRange(2, 1, 1, 3).setValues([["nintendo switch oled -case -dock", "ebay", 120]]);  sheet.getRange("B2:B").setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(Object.keys(MARKETS), true).build());  sheet.getRange("C2:F").setNumberFormat("$#,##0.00");  sheet.getRange("H2:I").setNumberFormat("$#,##0.00");  sheet.getRange("H2").setFormula(FEES);  sheet.getRange("I2").setFormula(PROFIT);  sheet.setColumnWidth(1, 300);  sheet.setColumnWidths(2, HEADERS.length - 1, 110);  ss.setActiveSheet(sheet);  ui.alert("Inventory sheet ready. Put each item in column A and its marketplace in column B, then choose Soldgraph → Fill in missing prices.");} function fillMissingPrices() {  updatePrices_(false);} function refreshAllPrices() {  updatePrices_(true);} function updatePrices_(all) {  const ui = SpreadsheetApp.getUi();  const key = PropertiesService.getScriptProperties().getProperty("SOLDGRAPH_KEY");  if (!key) return ui.alert("Add your API key first: Soldgraph → Set API key.");  const ss = SpreadsheetApp.getActive();  const sheet = ss.getSheetByName(SHEET);  if (!sheet) return ui.alert("Set up the sheet first: Soldgraph → Set up the Inventory sheet.");   const last = sheet.getLastRow();  const rows = last < 2 ? [] : sheet.getRange(2, 1, last - 1, HEADERS.length).getValues();  const todo = [];  rows.forEach(function (r, i) {    const q = String(r[0]).trim();    if (!q || (!all && (r[3] !== "" || String(r[9]).indexOf("No sales found") === 0))) return;    const market = String(r[1]).trim().toLowerCase() || "ebay";    if (!MARKETS[market]) return note_(sheet, i + 2, "Pick ebay, mercari or poshmark");    todo.push({ row: i + 2, q: q, market: market, tries: 0 });  });  if (!todo.length) {    return ui.alert(all ? "No items to price yet. Add them in column A." : "Every item already has a price. Use Refresh all prices to update them.");  }  const go = ui.alert("Look up " + todo.length + (todo.length === 1 ? " item?" : " items?"), "Each lookup uses 1 Soldgraph request.", ui.ButtonSet.YES_NO);  if (go !== ui.Button.YES) return;   const started = Date.now();  const zone = ss.getSpreadsheetTimeZone();  let priced = 0;  let failed = 0;  let stop = "";  while (todo.length && !stop) {    if (Date.now() - started > TIME_LIMIT_MS) {      stop = "Ran out of time with " + todo.length + " left. Choose Fill in missing prices to finish them.";      break;    }    const chunk = todo.splice(0, BATCH_SIZE);    const items = runBatch_(key, chunk, started);    const now = Utilities.formatDate(new Date(), zone, "yyyy-MM-dd HH:mm");    chunk.forEach(function (t, i) {      const item = items[i] || {};      const code = item.error ? item.error.code : "";      if (item.status === "complete") {        const s = item.summary || {};        const median = s.median_excluding_best_offer != null ? s.median_excluding_best_offer : s.median;        if (median == null) {          sheet.getRange(t.row, 4, 1, 4).setValues([["", "", "", 0]]);          note_(sheet, t.row, "No sales found " + now);        } else {          sheet.getRange(t.row, 4, 1, 4).setValues([[median, s.p25, s.p75, s.priced_count]]);          note_(sheet, t.row, now);        }        priced++;      } else if (code === "too_many_pending" && ++t.tries < 3) {        todo.push(t);      } else {        if (code === "quota_exceeded") stop = "You're out of Soldgraph requests. Add more at soldgraph.com, then run this again.";        note_(sheet, t.row, "Error: " + (code || item.status || "no answer"));        failed++;      }    });    SpreadsheetApp.flush();  }  const summary = "Priced " + priced + (priced === 1 ? " item." : " items.") + (failed ? " " + failed + " couldn't be priced; see column J." : "");  ui.alert(stop ? summary + " " + stop : summary);} function runBatch_(key, chunk, started) {  const searches = chunk.map(function (t) {    const m = MARKETS[t.market];    const search = { endpoint: m.endpoint, q: t.q };    if (m.count) search.count = m.count;    return search;  });  let body = call_(key, "post", "/v1/batches", { searches: searches });  while (body.status === "pending") {    if (Date.now() - started > TIME_LIMIT_MS + 60 * 1000) throw new Error("Soldgraph is taking longer than usual. Try again in a minute.");    body = call_(key, "get", "/v1/batches/" + body.batch_id + "?wait=20");  }  const byIndex = [];  (body.items || []).forEach(function (item) {    byIndex[item.index] = item;  });  return byIndex;} function call_(key, method, path, payload) {  const options = { method: method, headers: { Authorization: "Bearer " + key }, muteHttpExceptions: true };  if (payload) {    options.contentType = "application/json";    options.payload = JSON.stringify(payload);  }  const res = UrlFetchApp.fetch(API + path, options);  const status = res.getResponseCode();  let body = {};  try {    body = JSON.parse(res.getContentText());  } catch (e) {    throw new Error("Soldgraph sent an unreadable answer (HTTP " + status + "). Try again in a minute.");  }  if (status === 401) throw new Error("Soldgraph didn't accept your key. Set it again: Soldgraph → Set API key.");  if (body.error && body.error.code === "quota_exceeded") throw new Error("You're out of Soldgraph requests. Add more at soldgraph.com, then run this again.");  if (status >= 400) throw new Error("Soldgraph error: " + (body.error ? body.error.code : "HTTP " + status));  return body;} function note_(sheet, row, text) {  sheet.getRange(row, 10).setValue(text);}