Silver price in Google Sheets

Track silver holdings in Google Sheets with a downloadable workbook and scheduled XAG refresh.

Updated September 29, 2026

Download the silver portfolio workbook, open it in Google Sheets, and use the companion Apps Script to refresh XAG-USD-SPOT every six hours from goldprice.dev. Store your Pro or Realtime Pro key in Script Properties, never in cells. The workbook converts troy ounces to grams, keeps purity and cost per holding, and calculates indicative spot value and change.

1.Download and import the workbook
Download the silver portfolio workbook, upload it to Google Drive, and choose Open with → Google Sheets. Under File → Settings, set the time zone to GMT+00:00 (UTC) so timestamps match the labels. Replace the example holdings and refresh the sample quote before using the valuation. The formulas calculate fine silver grams, spot value, change, and change percentage.
Get a silver API keySilver requires Pro or Realtime Pro.
Compare plans →
2.Keep the API key out of cells
XAG spot requires a Pro or Realtime Pro key. In the Sheet, open Extensions → Apps Script → Project Settings → Script properties and add SILVER_API_KEY. Store the key there, rather than in a visible cell or formula. A six-hour refresh makes four calls per day, about 120 calls per 30-day month.

3.Paste the scheduled refresh script

Download the companion Code.gs and paste it into the Apps Script editor. Run installSilverRefresh once and approve the Google permissions. It replaces duplicate refresh triggers, fetches the authenticated XAG quote, validates the symbol, currency, price, and timestamp, then updates the Price Data cells together.

JAVASCRIPT · Code.gs — core request

const ENDPOINT = "https://api.goldprice.dev/v1/spot/XAG-USD-SPOT";

function fetchSilverQuote_() {
  const key = PropertiesService.getScriptProperties().getProperty("SILVER_API_KEY");
  if (!key) throw new Error("Add SILVER_API_KEY in Script properties.");
  const response = UrlFetchApp.fetch(ENDPOINT, {
    headers: { Authorization: "Bearer " + key },
    muteHttpExceptions: true,
  });
  const status = response.getResponseCode();
  if (status === 403) throw new Error("XAG requires a Pro or Realtime Pro key.");
  if (status === 429) throw new Error("Rate limit reached; wait before retrying.");
  if (status !== 200) throw new Error("Goldprice returned HTTP " + status);
  const data = JSON.parse(response.getContentText());
  if (data.is_stale) throw new Error("The API marked this quote stale; the previous valid quote remains in the sheet.");
  const value = Number(data.price);
  if (data.symbol !== "XAG" || data.quote_currency !== "USD" || !Number.isFinite(value) || value <= 0) {
    throw new Error("Invalid XAG/USD response");
  }
  return data;
}

4.Check the refreshed valuation
After the first refresh, Price Data shows the quote timestamp, silver price per troy ounce, and the calculated price per gram. Portfolio references those cells and multiplies fine silver grams by the current per-gram spot price. The result is an indicative spot valuation, not a dealer buyback quote or appraisal.
5.Set a schedule that fits your quota
The script uses one request per scheduled refresh. Six-hour refreshes use about 120 calls/month; hourly refreshes use about 720. Add retries only for transient errors and keep the schedule below your plan quota. If a shared workbook has many editors, use a server-side proxy and keep the API key out of the Sheet.
JAVASCRIPT · Code.gs — trigger
```
ScriptApp.newTrigger("refreshSilverData")
  .timeBased()
  .everyHours(6)
  .create();
```

Expected output

The API returns this shape:

JSON · GET /v1/spot/XAU-USD-SPOT

{
  "symbol": "XAG",
  "quote_currency": "USD",
  "price": "32.50",
  "computed_at": "2026-09-29T10:00:00+00:00"
}

Price Data shows the authenticated quote and timestamp; Portfolio shows fine silver grams, current spot value, change, and change percentage for each holding.

Common errors

Code	Symptom	Fix
401	Apps Script reports HTTP 401.	Check the exact `SILVER_API_KEY` Script Property and keep the `Bearer` prefix in the request header.
403	Apps Script reports that XAG is gated.	Use a Pro or Realtime Pro key. The workbook does not claim silver access on Free.
429	The scheduled refresh reports HTTP 429.	Remove duplicate triggers, keep the six-hour schedule, and wait for the retry window. One refresh should make one API call.

FAQ

Does the workbook work on the Free tier?

No. The workbook calls XAG-USD-SPOT, which requires Pro or Realtime Pro. The key is stored in Script Properties and never written to cells.

How is silver converted from ounces to grams?

The workbook uses 31.1034768 grams per troy ounce, then multiplies fine silver grams by the refreshed XAG/USD price per gram.

How many calls does a six-hour schedule use?

Four refreshes per day, or about 120 calls in a 30-day month. Hourly refreshes use about 720 calls before any manual runs or retries.

Why does the valuation differ from a dealer quote?

The sheet uses international spot and excludes dealer premiums, spreads, taxes, fabrication, and condition. Use a physical dealer quote for a sell or buy decision.

Can Claude or another AI agent set this up?

Yes. The goldprice.dev MCP server can provide current API documentation to an AI assistant. Keep the API key in Apps Script properties and review generated code before running it.

What happens when a refresh fails?

The script validates the response before writing. A failed request leaves the prior price and timestamp in place, so formulas do not silently switch to a blank or invalid quote.

Going further

Add a second sheet that records each successful quote timestamp for an audit trail.
Add a local-currency conversion through a server-side service that keeps credentials private.
Compare the indicative spot value with separately collected dealer sell and buyback quotes.