Keeping a short market watchlist should not start with writing an API client. If you want stock, crypto, forex and commodity prices in the same spreadsheet, the SiftingIO Google Sheets template gives you a starting point you can copy and change.
Choose a market, type a supported symbol and select SiftingIO → Refresh prices. The sheet fills in the latest available price, its source timestamp and a status for each row. You can track up to 20 symbols in one copy.
Copy the Google Sheets market watchlist
You will need a Google account and your own SiftingIO API key. The template includes the script, so there is no code to paste or add-on to install. API access and usage follow your SiftingIO plan.
What you can put in the watchlist#
The Market column has four choices: Crypto, Forex, Commodities and US stocks. The Symbol column is editable text. BTCUSD, EURUSD and XAUUSD are starting examples; replace them or add your own supported symbols in the yellow cells.
For example, you could follow:
- Crypto: ETHUSD or BTCEUR.
- Forex: GBPUSD or EURUSD.
- Commodities: XAUUSD for gold.
- US stocks: AAPL.
Those are the sheet's own suggested examples; swap in whatever supported symbols you want to follow. Symbol availability still depends on the market data available through your API access.
Prices stay in each instrument's quote currency. BTCEUR is priced in EUR; ETHUSD is priced in USD. The template does not convert everything into a single currency.
Set up your own copy#
1. Copy the template#
Open the template link and choose Make a copy. Google copies the Watchlist and Guide tabs along with the attached Apps Script. You work in your own file rather than editing the public template.
Use Google Sheets in a desktop browser for this menu-based workflow. If the SiftingIO menu has not appeared after the copy opens, reload the sheet.
2. Connect your SiftingIO API key#
Open SiftingIO → Set API key. On first use, Google asks you to authorize the script in your copy. Review the permissions, then enter your API key from the SiftingIO dashboard.
The script uses the current spreadsheet and makes requests to the SiftingIO API. If your Google Workspace administrator blocks the authorization, that needs to be resolved before the menu can fetch prices.
Your key is saved in Apps Script user properties for your Google account in that copy. It is not written into a spreadsheet cell or included in the public template. Keep your working copy private or share it only with trusted editors, who can also change its attached script.
3. Choose your symbols and refresh#
Edit the yellow Market and Symbol cells, then choose SiftingIO → Refresh prices. Leave the Watchlist tab name and the output columns in place so the script can find them.
The results are written into columns C through G. Prices are numeric cells, so you can use them in your own spreadsheet calculations. Editing a market or symbol clears its previous result; refresh again to fetch the new selection.
Read the timestamp as well as the price#
Each result has three time fields:
- Value time (UTC): the timestamp returned with the price.
- Refreshed at (UTC): when the sheet received that row's API response.
- Age at refresh (s): the difference between those times, in seconds, measured during that refresh.
Suppose you refresh on Sunday and a stock's source timestamp is from Friday. The sheet keeps that source time visible. Fetching a result now does not make the underlying price new.
The status Latest available snapshot means a usable price and timestamp were returned. It is not a guarantee that the price was recorded a moment ago. The age cell also stays fixed until the next refresh; it does not count upward while the sheet is idle.
How refreshes use your API allowance#
Refresh is manual. Opening the spreadsheet or editing a symbol does not fetch prices, and the template does not schedule background updates.
Each valid row that is requested makes one API call. For a successful refresh of 10 filled rows, that means 10 requests. Requests run one at a time with a short pause between them, so a longer watchlist takes a little longer to finish. Your account's rate limit, remaining quota and market permissions still apply.
The Status column explains problems on the affected row. An unavailable symbol can fail while the other rows continue. An invalid API key or a rate limit stops the remaining requests, so the sheet does not keep sending calls that cannot succeed. Check the message, correct the issue or wait as instructed, then refresh again.
Troubleshooting#
You cannot edit the template or see the SiftingIO menu#
Use the copy link and choose Make a copy while signed in to Google. The public template is view-only; its attached script is copied with it. Open your copy in a desktop browser and reload if the SiftingIO menu is missing. Keep the Watchlist tab name and edit only the yellow Market and Symbol cells.
Google asks you to authorize Apps Script#
First use requires your permission to work with the current spreadsheet and contact the SiftingIO API. Review the requested permissions for your own copy. If Google Workspace blocks authorization, ask your administrator; reloading or changing the API key will not resolve that policy. The template does not require an add-on or code pasted into Apps Script. See Google's script authorization guide.
Where should you put your API key, and what does HTTP 401 mean?#
Choose SiftingIO → Set API key in your private copy and enter a valid key from your SiftingIO dashboard, without spaces. The key is saved in per-user Apps Script properties, not a cell. Keep the copy private or share only with trusted editors, who can change its script. A missing key shows setup guidance; HTTP 401 stops the remaining requests until you correct the key. Remove API key removes it from this script only; it does not revoke the key in SiftingIO. See Apps Script property scopes.
One symbol fails while the other rows work#
Check that the Market matches the Symbol and use the symbol catalog: for example, Crypto / BTCUSD, not BTC/USD. Check market and symbol means the input failed the local format check and no request was sent for that row. HTTP 404 means no snapshot was available; check coverage rather than assuming the symbol never exists. Other rows continue. HTTP 403 indicates missing market access for your plan or key. Correct the input, then refresh; editing the yellow cells clears that row's old output.
Refresh finishes but the price or value time does not change#
Compare Value time (UTC) with Refreshed at (UTC). A new request can return the same latest available snapshot, especially while a market is closed or the source has no newer observation. Check market hours; an open market alone does not prove that a particular symbol is fresh. Latest available snapshot is not a freshness guarantee. Age at refresh (s) is measured once during refresh and stays fixed until you refresh again. Repeated clicks cannot create a new observation.
You see HTTP 429 or an Apps Script quota error#
For an API rate limit, wait at least the seconds shown in Status before refreshing. A monthly API allowance message requires checking usage and your plan in the dashboard; a short wait does not restore monthly allowance. The template stops the remaining API requests after a 429 and does not automatically retry. Google Apps Script quotas are separate: a Google quota or execution-limit message is not the same as an API 429. Follow the reported limit and review Google's quotas. Refresh is manual, so avoid repeatedly starting it or adding background triggers while diagnosing the issue.
When to use this template#
Use it for a small watchlist, a research worksheet or a price check alongside your own calculations. It gives you an editable set of symbols and visible timestamps without building the spreadsheet integration yourself.
It does not stream prices or save a history of previous refreshes. For historical candles and CSV or XLSX downloads, use the market data export tool. If you want to build your own integration instead, the Apps Script tutorial explains that approach.
To start, make your own copy of the market watchlist, add your API key and replace the examples with the symbols you want to follow.
