Escalake for Google Sheets

Install (2 minutes, no account needed)

1. Open the Google Sheet you want to use this in.
2. Extensions → Apps Script.
3. Delete anything in the editor and paste in the script below.
4. Save (the disk icon, or Ctrl/Cmd+S). Name the project anything.
5. Back in your sheet, type =ESCALAKE("pcu333611333611") in any cell.

The first call may prompt you to authorize the script. That is Google's standard permission for a script to make outbound web requests, which is all this does. It never reads or writes anything else in your account.

The script

/**
 * Escalake for Google Sheets — free tier.
 *
 * Pulls the latest value for any index at escalake.com/indices/<slug>
 * straight into a cell. See README.md in this directory for install steps.
 *
 * Covers the public index catalog only (escalake.com/indices) — the same
 * set anyone can browse with no account.
 */

/**
 * Looks up an Escalake price index.
 *
 * @param {string} indexSlug The index's slug from escalake.com/indices/<slug>, e.g. "pcu333611333611".
 * @param {string} field Optional. One of "value" (default), "yoy", "date", "name", or "unit".
 * @return The requested figure for that index.
 * @customfunction
 */
function ESCALAKE(indexSlug, field) {
  if (!indexSlug) {
    throw new Error(
      'ESCALAKE: pass an index slug, e.g. =ESCALAKE("pcu333611333611")'
    );
  }
  var normalizedField = (field || 'value').toString().toLowerCase();

  var data = fetchEscalakeIndex_(indexSlug.toString().toLowerCase());

  switch (normalizedField) {
    case 'value':
      return data.latestValue;
    case 'yoy':
      return data.yoyChangePercent;
    case 'date':
      return data.latestDate ? new Date(data.latestDate) : null;
    case 'name':
      return data.name;
    case 'unit':
      return data.unit;
    default:
      throw new Error(
        'ESCALAKE: unknown field "' +
          field +
          '". Use "value", "yoy", "date", "name", or "unit".'
      );
  }
}

/**
 * Fetches and caches one index's data from the public Escalake API.
 * Cached for an hour per index — the underlying data only refreshes once a
 * day (Escalake's ingestion cron), while a Sheet can re-run its custom
 * functions on every open or recalculation. Without this cache a
 * multi-cell sheet referencing the same index would call the API on every
 * single recalc.
 */
function fetchEscalakeIndex_(indexSlug) {
  var cache = CacheService.getScriptCache();
  var cacheKey = 'escalake_index_' + indexSlug;
  var cached = cache.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  var url =
    'https://escalake.com/api/indices/' +
    encodeURIComponent(indexSlug) +
    '/value';
  var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

  if (response.getResponseCode() === 404) {
    throw new Error(
      'ESCALAKE: no index found for "' +
        indexSlug +
        '". Browse escalake.com/indices for valid slugs.'
    );
  }
  if (response.getResponseCode() !== 200) {
    throw new Error(
      'ESCALAKE: request failed (HTTP ' +
        response.getResponseCode() +
        '). Try again shortly.'
    );
  }

  var data = JSON.parse(response.getContentText());
  cache.put(cacheKey, JSON.stringify(data), 3600);
  return data;
}

Function reference

The slug is the last part of the index's URL. Find it by browsing the index lookup.