Get quote data from justETF and simpletoolsforinvestors

GETBONDPRICE.gs

/**
 * Returns the End-of-Day market price for a bond given its ISIN.
 * @param {string} isin - Bond ISIN
 * @return {Array} [price, "CACHE"/"FETCH"] — spreads across 2 columns.
 * Use =INDEX(GETBONDPRICE(A1),1) to get only the price in one cell.
 * @customfunction
 */
function GETBONDPRICE(isin) {
  if (!isin) return ["Missing ISIN", "N/A"];

  var cacheStatus = "FETCH";

  try {
    // ── Cache read ───────────────────────────────────────────────────────────
    var cache, map = null;
    try {
      cache = CacheService.getScriptCache();
      var cached = cache.get("BOND_PRICE_MAP");
      if (cached) {
        map = JSON.parse(cached);
        cacheStatus = "CACHE";
      }
    } catch (e) { /* CacheService non disponibile, si procede con fetch */ }

    // ── Fetch CSV ────────────────────────────────────────────────────────────
    if (!map) {
      var pageUrl = "https://www.simpletoolsforinvestors.eu/documentivari.php";
      var pageResp = UrlFetchApp.fetch(pageUrl, { muteHttpExceptions: true });
      if (pageResp.getResponseCode() !== 200)
        return ["Page HTTP " + pageResp.getResponseCode(), "N/A"];

      var html = pageResp.getContentText();

      // Cerca il link CSV End of Day (href relativo o assoluto)
      var re = /Rendimenti e durate calcolati End of Day[\s\S]*?<a[^>]+href=['"](data\/export\/[^'"]+\.csv)['"]/i;
      var m = html.match(re);
      if (!m) return ["CSV link not found", "N/A"];

      var href = "https://www.simpletoolsforinvestors.eu/" + m[1];

      var csvResp = UrlFetchApp.fetch(href, { muteHttpExceptions: true });
      if (csvResp.getResponseCode() !== 200)
        return ["CSV HTTP " + csvResp.getResponseCode(), "N/A"];

      var rows = Utilities.parseCsv(csvResp.getContentText("UTF-8"), ";");
      var header = rows[0];

      var isinIdx  = header.indexOf("isincode");
      var priceIdx = header.indexOf("price");       // ← era "settlementprice": BUG!
      var typeIdx  = header.indexOf("pricetype");

      if (isinIdx === -1 || priceIdx === -1)
        return ["Columns not found: " + header.join("|"), "N/A"];

      map = {};
      for (var i = 1; i < rows.length; i++) {
        var row = rows[i];
        // Opzionale: includi solo prezzi RP (Reference Price = EoD ufficiale)
        // Rimuovi il commento se vuoi solo prezzi EoD e non LP (intraday)
        // if (typeIdx !== -1 && row[typeIdx] !== "RP") continue;

        var val = row[priceIdx];
        if (val && row[isinIdx]) {
          map[row[isinIdx]] = parseFloat(String(val).replace(",", "."));
        }
      }

      // ── Cache write (max ~95KB per sicurezza) ─────────────────────────────
      try {
        var serialized = JSON.stringify(map);
        if (cache && serialized.length < 95000)
          cache.put("BOND_PRICE_MAP", serialized, 3600);
      } catch (e) { /* Cache write fallita — non critico */ }
    }

    return map[isin] !== undefined
      ? [map[isin], cacheStatus]
      : ["ISIN not found", cacheStatus];

  } catch (e) {
    return ["Error: " + e.message, "N/A"];
  }
}

GETQUOTEDATA.gs

/**
 * Returns quote data for an ETF from JustETF.
 *
 * @param {string} isin        - ETF ISIN (e.g. IE00BK5BQT80)
 * @param {string} dataPoint   - What to return:
 *                               "latest"        → latest price
 *                               "previous"      → previous close price
 *                               "change%"       → day-to-day % change (e.g. -0.90)
 *                               "change"        → day-to-day amount change
 *                               "venue"         → trading venue (e.g. "XETRA")
 *                               "low"           → 52-week low
 *                               "high"          → 52-week high
 *                               "latestdate"    → date of latest quote (YYYY-MM-DD)
 *                               "previousdate"  → date of previous quote
 * @return {string|number}
 * @customfunction
 */
function GETQUOTEDATA(isin, dataPoint) {
  if (!isin)      return "Error: ISIN required";
  if (!dataPoint) return "Error: dataPoint required";

  var url = "https://www.justetf.com/api/etfs/" + isin.trim() + "/quote?currency=EUR&locale=it";

  try {
    var response = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true,
      headers: {
        "Accept":     "application/json",   // ← forza JSON; senza questo il server può rispondere XML
        "User-Agent": "Mozilla/5.0 (compatible; GoogleAppsScript)"
      }
    });

    var statusCode = response.getResponseCode();
    if (statusCode === 404) return "Error: ISIN not found";
    if (statusCode !== 200) return "Error: HTTP " + statusCode;

    var raw = response.getContentText();

    // Guardia difensiva: se arriva ancora XML nonostante l'header, segnalalo chiaramente
    if (raw.trim().startsWith("<")) return "Error: unexpected XML response (Accept header ignored?)";

    var data = JSON.parse(raw);

    switch (dataPoint.trim().toLowerCase()) {
      case "latest":        return data.latestQuote.raw;
      case "previous":      return data.previousQuote.raw;
      case "change%":       return data.dtdPrc.raw;
      case "change":        return data.dtdAmt.raw;
      case "venue":         return data.quoteTradingVenue;
      case "low":           return data.quoteLowHigh.low.raw;
      case "high":          return data.quoteLowHigh.high.raw;
      case "latestdate":    return data.latestQuoteDate;
      case "previousdate":  return data.previousQuoteDate;
      default:
        return "Error: invalid dataPoint. Use: latest, previous, change%, change, venue, low, high, latestdate, previousdate";
    }

  } catch (error) {
    return "Error: " + error.message;
  }
}