Market data in Google Sheets: update forex, gold and crypto prices with Apps Script

Use Google Sheets to keep EURUSD, gold and Bitcoin prices in a small watchlist. This Apps Script recipe reads a venue and symbol from each row, calls a REST endpoint, and writes the price alongside its timestamp. Run it manually first, then optionally schedule updates.

The result is a polled watchlist. Each refresh stores a snapshot, and the sheet shows that snapshot until the next run, so nothing here is streaming or suitable for execution decisions. If you need continuous updates, REST snapshots and WebSocket streams covers that choice. For the basics of the forex endpoints themselves, see how to get real-time forex prices from SiftingIO.

The endpoint and the response shape#

All three rows use GET /v1/last/trade/:venue/:symbol. This recipe deliberately supports only forex, commodities and crypto, with the three symbols below. It is not a general symbol validator for every asset class.

curl -H "X-API-Key: $SIFTING_KEY" \
  "https://api.sifting.io/v1/last/trade/forex/EURUSD"

The docs describe a four-field response. The values below are a synthetic fixture chosen to be internally consistent. They were not observed from a live call.

{ "s": "EURUSD", "p": "1.17320", "P": "100000", "t": 1789723798800 }

s is the symbol. p and P are the last price and size, sent as strings to preserve precision. t is an integer in Unix epoch milliseconds. Treat t as the time of the published reference value: SiftingIO aggregates each price across multiple venues, so it is a reference price and t is no single venue's trade time.

Handle the HTTP status before reading a price. A 401 points to authentication, a 403 to access permissions, a 404 to an unavailable symbol, and a 429 to a rate limit or exhausted quota. A 503 means the service could not provide the snapshot. Error bodies can contain an error field and, for a stale snapshot, a last_t timestamp. The code below displays those fields when present; it does not depend on an exact error string or assume every 503 has the same body. See errors and rate limits.

Set up the sheet#

Use a new spreadsheet or an empty tab named Watchlist. Put these headers in separate cells A1:G1: venue, symbol, price, value time (UTC), age at fetch (s), fetched at (UTC), status. Then fill three input rows:

Columns A and B are yours. The script owns columns C to G and overwrites them on every run.

Keep the API key in a script property#

Open Extensions > Apps Script from the spreadsheet. In the script editor, open Project Settings. Under Script Properties, add a property named SIFTING_KEY with your sft_ key as the value, then save.

This keeps the key out of cells, source code and the URL. The URL part matters because request URLs tend to end up in error messages and logs. The recipe sends the key in the X-API-Key header and never logs it.

A script property is configuration storage, not a secret vault. Google's Properties service guide describes script properties as shared configuration. Bound-script permissions let spreadsheet editors run the attached script, while view-only collaborators can view it. An editor could add a line that reads the property and writes it to a cell. Keep this spreadsheet private or share it only with trusted collaborators. Use a dedicated, revocable API key if your plan allows an additional key.

The Apps Script code#

Paste this into Code.gs, replacing the default content.

const SHEET_NAME = 'Watchlist';
const API_BASE = 'https://api.sifting.io/v1/last/trade/';
const VENUES = ['forex', 'commodities', 'crypto'];
const SYMBOL_RE = /^[A-Z0-9]{6,12}$/;
const MIN_T_MS = Date.UTC(2015, 0, 1);
const MAX_AHEAD_MS = 5000;

function refreshWatchlist() {
  const key = PropertiesService.getScriptProperties().getProperty('SIFTING_KEY');
  if (!key) {
    throw new Error('Script property SIFTING_KEY is not set.');
  }
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  if (!sheet) {
    throw new Error('No sheet named ' + SHEET_NAME + '.');
  }
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    return;
  }
  const inputs = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  const output = inputs.map(function (row) {
    return refreshRow(row[0], row[1], key);
  });
  sheet.getRange(2, 3, output.length, 5).setValues(output);
}

// Returns [price, value time (UTC), age at fetch (s), fetched at (UTC), status].
function refreshRow(venueCell, symbolCell, key) {
  const venue = String(venueCell).trim().toLowerCase();
  const symbol = String(symbolCell).trim().toUpperCase();
  if (venue === '' && symbol === '') {
    return ['', '', '', '', ''];
  }
  if (VENUES.indexOf(venue) === -1 || !SYMBOL_RE.test(symbol)) {
    return ['', '', '', '', 'not requested: check venue and symbol'];
  }

  let response;
  try {
    response = UrlFetchApp.fetch(API_BASE + venue + '/' + symbol, {
      method: 'get',
      headers: { 'X-API-Key': key },
      muteHttpExceptions: true,
      followRedirects: false
    });
  } catch (err) {
    return ['', '', '', new Date().toISOString(), 'request failed: no HTTP response'];
  }

  const fetchedMs = Date.now();
  const fetchedAt = new Date(fetchedMs).toISOString();
  const code = response.getResponseCode();

  let body = null;
  try {
    body = JSON.parse(response.getContentText());
  } catch (err) {
    body = null;
  }

  if (code !== 200) {
    const label = body && typeof body.error === 'string' ? body.error : 'no error code';
    let lastValueTime = '';
    if (body && isPlausibleMs(body.last_t)) {
      lastValueTime = new Date(body.last_t).toISOString();
    }
    return ['', lastValueTime, '', fetchedAt, 'HTTP ' + code + ': ' + label];
  }

  const validPrice = body && typeof body.p === 'string' &&
    /^(?:\d+(?:\.\d+)?|\.\d+)$/.test(body.p);
  const price = validPrice ? Number(body.p) : NaN;
  if (!body || typeof body.s !== 'string' || body.s.toUpperCase() !== symbol ||
      !validPrice || !isFinite(price) || price <= 0) {
    return ['', '', '', fetchedAt, 'HTTP 200 but unusable body'];
  }
  if (!isPlausibleMs(body.t)) {
    return [price, '', '', fetchedAt, 'price without a valid timestamp'];
  }
  const ageMs = fetchedMs - body.t;
  if (ageMs < -MAX_AHEAD_MS) {
    return [price, '', '', fetchedAt, 'timestamp ahead of script clock'];
  }
  const valueTime = new Date(body.t).toISOString();
  return [price, valueTime, Math.round(ageMs / 100) / 10, fetchedAt, 'fetched'];
}

function isPlausibleMs(t) {
  return typeof t === 'number' && isFinite(t) && Math.floor(t) === t &&
    t >= MIN_T_MS && t <= 8640000000000000;
}

Four design choices are worth knowing before you change anything:

• A completed run rewrites all five output columns for every row. HTTP and network failures blank the affected price and set a status. A run that cannot start or cannot write to the sheet leaves previous output untouched, so always check fetched at too.
• muteHttpExceptions: true makes UrlFetchApp return the response on a 4xx or 5xx instead of throwing. One bad symbol therefore costs one row, not the whole run.
• p arrives as a string and is converted with Number() so the cell is numeric. That is fine for a watchlist. Keep the string if you need exact decimal digits.
• Other asset classes are deliberately outside this recipe. Keep the input to the three example rows until the manual test works.

Run it by hand first#

Select refreshWatchlist in the editor toolbar and click Run. The first run asks you to authorize the script to edit the spreadsheet and call an external service.

The rows should then look something like this. The table is a synthetic fixture chosen for illustration. It is not output from a real run.

The two time columns answer different questions. value time is the API's t, the moment the published value belongs to. fetched at is when the script received the HTTP response.

A fetched status only means the request returned HTTP 200 with a usable body. It does not say the price is fresh. age at fetch is the gap between the two times, computed once. It does not advance while the sheet sits open, so at 09:50 that EURUSD row is a 20-minute-old snapshot whatever column E says.

What each status means#

• In the synthetic HTTP 503: stale_snapshot example, the price cell is blank and value time comes from last_t. For a different error response, that timestamp may be absent too.
• An HTTP 404 usually means a mistyped symbol or an unavailable snapshot. For a 401, check the key in Script Properties; for a 403, check access to that market. The text after the status is the server's error field, so wording can vary.
• price without a valid timestamp and timestamp ahead of script clock are kept separate. A missing or non-integer t, or a t more than five seconds ahead of the script's clock, leaves the age blank. The age is never clamped to zero. A small negative age is written as it is, because clock skew of a second or two is normal and hiding it would misreport what was measured.

Age alone doesn't tell you why a value is old, and an unchanged price does not necessarily mean its timestamp stopped advancing. Check the value timestamp, fetch time and market schedule separately. The delayed vs live prices guide discusses what a display label can and cannot establish.

Add a time-driven trigger (optional)#

In the script editor, open Triggers, click Add Trigger, and configure it:

• Function: refreshWatchlist.
• Event source: time-driven.
• Interval: for example, every 15 minutes.

Then save. Create only one trigger for refreshWatchlist; check for an existing one before adding another. To stop updates or remove a duplicate, open Triggers and choose More > Delete trigger beside it. Agree on one trigger owner if others edit the project: each account can see only its own triggers.

Google's installable-trigger documentation states two things to plan around. First, the trigger runs under its creator's account and uses that account's authorization and quotas. Google notifies the owner about failed executions, but HTTP errors handled as row statuses do not fail the execution.

Second, trigger timing can be slightly randomized, and Google publishes no timing guarantee. Treat the interval as approximate. Use the fetched at column to confirm when the last run happened, and don't assume it ran on schedule.

With one trigger every 15 minutes, budget about 96 runs per day. This three-row watchlist makes three requests per run: about 288 calls per day, or 8,640 calls in a 30-day month, before manual refreshes or other API use.

As of September 18, 2026, the SiftingIO pricing page lists the Free tier at 10,000 REST calls per month and 60 requests per minute, with no card required. Check your account's market access and usage allowance before shortening the interval.

On the Google side, daily UrlFetchApp calls and total trigger runtime are capped. Limits differ between consumer and Workspace accounts and can change, so check Google's current quotas.

Limits of this design#

Rows are fetched one after another, which is fine for a handful of symbols. A long watchlist will eventually hit the per-execution runtime limit.

There is no retry and no backoff. A 429 or a network failure marks the row, and the next scheduled run tries again. There is no history either: every run overwrites the previous snapshot. If you need a time series, append rows to a second tab rather than reworking this function.

The sheet has no automatic alert for a stopped trigger. Keep fetched at visible and check Apps Script's Executions page if updates stop. Avoid simultaneous manual refreshes, and pause the trigger while changing inputs; this small example does not lock the sheet against concurrent runs or edits.

The code was checked against the documentation and exercised with offline JavaScript fixtures for successful responses, malformed data, HTTP failures and sheet writes. Those tests mocked the Apps Script services: they were not a run in a Google account or a call to the live API. Run the script manually and check each row before relying on its trigger.

Read the docs