Get Zalando and Otto product data into Google Sheets

const API = "https://api.everydata.io";

/**
 * One GET request to everydata.io. The key lives in the script properties, not in the sheet.
 * Waits when rate limited (429) and retries a failed page load (5xx) once; other errors come back as { error }.
 */
function apiGet(path, params) {
  const key = PropertiesService.getScriptProperties().getProperty("EVERYDATA_API_KEY");
  const query = Object.keys(params)
    .map((k) => encodeURIComponent(k) + "=" + encodeURIComponent(params[k]))
    .join("&");
  let retried = false;
  for (let attempt = 0; attempt < 4; attempt++) {
    const res = UrlFetchApp.fetch(API + path + "?" + query, {
      headers: { "x-api-key": key },
      muteHttpExceptions: true,
    });
    const code = res.getResponseCode();
    if (code === 429) {
      // Too many requests at once. The answer says how long to wait: "... Try again in 12 seconds."
      const wait = res.getContentText().match(/in (\d+) seconds/);
      Utilities.sleep(Math.min(wait ? Number(wait[1]) + 1 : 60, 60) * 1000);
      continue;
    }
    if (code >= 500 && !retried) {
      retried = true; // 5xx answers are not counted against your quota
      Utilities.sleep(5000);
      continue;
    }
    if (code >= 400) return { error: code };
    return JSON.parse(res.getContentText());
  }
  return { error: 429 };
}

/** Sheet "Zalando": product URLs in column A from row 2; writes columns B–G. */
function refreshZalando() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Zalando");
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;
  const urls = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
  const rows = urls.map(([url]) => {
    if (!url) return ["", "", "", "", "", ""];
    const p = apiGet("/zal/zalando-lookup-product", { url: url });
    if (p.error) return ["Error " + p.error, "", "", "", "", new Date()];
    return [p.productTitle, p.manufacturer, p.price, p.retailPrice, p.warehouseAvailability, new Date()];
  });
  sheet.getRange(2, 2, rows.length, 6).setValues(rows);
}

/** Sheet "Otto": search term in B1; writes the first page of results from row 4. */
function searchOtto() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Otto");
  const keyword = sheet.getRange("B1").getValue();
  const data = apiGet("/ott/otto-search-by-keyword", { keyword: keyword, page: 1 });
  if (sheet.getLastRow() >= 4) sheet.getRange(4, 1, sheet.getLastRow() - 3, 6).clearContent();
  if (data.error) {
    sheet.getRange("A4").setValue("Error " + data.error);
    return;
  }
  const rows = (data.searchProductDetails || []).map((p) => [
    p.productDescription, p.manufacturer, p.price, p.retailPrice, p.productRating, "https://www.otto.de" + p.dpUrl,
  ]);
  if (rows.length) sheet.getRange(4, 1, rows.length, 6).setValues(rows);
}

/** Adds an "everydata" menu to the spreadsheet. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("everydata")
    .addItem("Refresh Zalando prices", "refreshZalando")
    .addItem("Search Otto", "searchOtto")
    .addToUi();
}

/** Run once from the editor: refreshes the Zalando sheet every morning between 7 and 8. */
function installDailyTrigger() {
  ScriptApp.newTrigger("refreshZalando").timeBased().everyDays(1).atHour(7).create();
}