procure.blogProcureIndex

Google Sheets & Excel live connector

Live ProcureIndex data in a spreadsheet with zero auth. Pick the path that fits: a one-line IMPORTDATA formula for ad-hoc work, or a reusable Apps Script function for team-wide use.

Option 1 · One-line IMPORTDATA formula

Paste this into any cell. Google Sheets will parse the CSV response into a two-column table (date, value) and refresh every hour, matching our cache.

=IMPORTDATA("https://procure-index.vercel.app/api/v1/series?id=copper&count=24&format=csv")
  • Swap id=copper for any ID from the catalog or /api/v1/catalog.
  • count accepts 1–120. Default is 24.
  • Data refreshes roughly every hour (our cache TTL).
  • Header row is date, <id> (<unit>) so you can chart it directly.

Option 2 · Apps Script GET_INDEX()

Prefer a function that returns just the latest value? Drop this into Extensions → Apps Script and paste the code. Then use =GET_INDEX("copper") in any cell.

/**
 * Fetches the latest value of a ProcureIndex series.
 * @param {string} id Index ID (e.g. "copper", "brent", "natgas_eu")
 * @param {string=} field Optional field: "value" (default), "change1m", "change3m", "change12m"
 * @return The requested number.
 * @customfunction
 */
function GET_INDEX(id, field) {
  if (!id) throw new Error('id is required');
  const url = 'https://procure-index.vercel.app/api/v1/series?id=' + encodeURIComponent(id);
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  const data = JSON.parse(res.getContentText());
  if (data.error) throw new Error(data.error);
  const key = (field || 'value').toLowerCase();
  if (key === 'value') return data.currentValue;
  if (key === 'change1m') return data.change1m;
  if (key === 'change3m') return data.change3m;
  if (key === 'change12m') return data.change12m;
  throw new Error('Unknown field: ' + field);
}

/**
 * Fetches the full observation series as a 2-column range.
 * @param {string} id Index ID
 * @param {number=} count Number of data points (default 24, max 120)
 * @return A range of [date, value] rows.
 * @customfunction
 */
function GET_INDEX_SERIES(id, count) {
  if (!id) throw new Error('id is required');
  const url = 'https://procure-index.vercel.app/api/v1/series?id=' + encodeURIComponent(id) + '&count=' + (count || 24);
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  const data = JSON.parse(res.getContentText());
  if (data.error) throw new Error(data.error);
  return (data.observations || []).map(function (o) { return [o.date, o.value]; });
}
  • In the sheet: =GET_INDEX("copper"), or =GET_INDEX("copper","change12m") for the 12-month change.
  • For a full history range: =GET_INDEX_SERIES("copper",36).
  • Apps Script caches function output for 6 hours by default — force refresh by editing the formula.

Excel

Excel supports the same CSV endpoint via Data → From Web (Power Query).

  1. Data tab → From Web.
  2. Paste https://procure-index.vercel.app/api/v1/series?id=copper&count=24&format=csv.
  3. Load. Right-click the table → Refresh to pull fresh data; set a refresh schedule in Data → Queries & Connections.

A native Excel add-in is on the roadmap. For now, Power Query handles the same job.

Need the raw JSON?

Drop format=csv and you get the full JSON response described in the main API docs— including category, region, unit, currentValue, change1m/3m/12m, and the full observations array.