Can Looker Studio call the API directly?

Use Google Sheets for this setup. Its standard connector reads worksheet cells and does not send your API authorization header. Apps Script sends that header with UrlFetchApp, writes the response into Sheets, and runs independently of the report.

Does hourly collection fit the free tier?

Yes. Hourly collection uses twenty four calls a day, about seven hundred and twenty a month. Free includes 5,000 monthly calls and allows ten requests a minute. Setup runs, manual fetches and other projects using your account also consume calls.

Are these live gold prices?

Free prices are fifteen minutes late. Paid plans return live prices, but this tutorial collects them hourly. Looker Studio also caches its data source, so check the calculation time shown beside the scorecard before treating a displayed value as current.

Why does the scorecard use a separate tab?

A scorecard aggregates its input rows. Pointing it at the full history can show a sum or average across past prices. GoldLatest contains only the latest successful observation, so its Average metric displays that observation's price.

Can report viewers see my API key?

The API key stays in Script Properties and is never written to the worksheet or report by this script. People with access to edit the bound script can read its properties. Limit spreadsheet editing to people you trust.

Can Claude help me write this?

Yes. Ask Claude to write or review the Apps Script, keep the key in Script Properties, and preserve the separate history and latest tabs. Share sample responses instead of your real API key, then verify the script in your spreadsheet.

Can I use a custom function instead?

For a price inside a worksheet cell, follow the Google Sheets tutorial . This Looker Studio setup uses an hourly trigger because the report needs saved observations for its time series and a predictable collection schedule.

How do I check whether a price is stale?

Read is_stale alongside the calculation time. This script keeps stale responses and records their flag, so show that field beside the scorecard. Also inspect the time: a failed hourly run leaves the previous observation in place, even if its stored flag is false.

Does the script preserve decimal prices?

The API returns price as decimal text. This script converts it to a numeric sheet cell for chart display. For exact money calculations, keep the original decimal string in your calculation code and use decimal arithmetic before converting for display.

Gold price in Looker Studio: chart and scorecard

To show gold prices in Looker Studio, fetch /v1/spot/XAU-USD-SPOT from goldprice.dev with Apps Script, then connect Google Sheets to your report. Store the API key in Script Properties. An hourly trigger records price, calculation time and stale status. Use these rows for a time series chart and a separate latest row for the scorecard.

Get your key and create a spreadsheet

Get a free API key at /onboarding. No credit card is required.

Create a new Google spreadsheet. Open Extensions, then Apps Script. In Project Settings, add a Script Property named GOLDPRICE_API_KEY. Set its value to your key, which starts with ga_live_, and save. Keep the key out of cells and source code.

Add the Apps Script

Replace Code.gs with this script. GoldHistory keeps each observation. GoldLatest holds only the latest successful row, so the scorecard shows that price. Both tabs use time, price and is_stale.

time comes from computed_at and displays in UTC. Prices are USD per troy ounce. The script checks the response and honors Retry-After without immediate retries. Reserve these tabs for the script.

Code.gs

function installHourlyTrigger() {  const book = SpreadsheetApp.getActiveSpreadsheet();  if (!book) throw new Error('Open this script from Google Sheets.');  book.setSpreadsheetTimeZone('Etc/UTC');  PropertiesService.getScriptProperties()    .setProperty('GOLDPRICE_SHEET_ID', book.getId());  ScriptApp.getProjectTriggers().forEach(function (trigger) {    if (trigger.getHandlerFunction() === 'appendGoldPrice') {      ScriptApp.deleteTrigger(trigger);    }  });  ScriptApp.newTrigger('appendGoldPrice').timeBased().everyHours(1).create();  appendGoldPrice();}function appendGoldPrice() {  const lock = LockService.getScriptLock();  lock.waitLock(5000);  try {    const props = PropertiesService.getScriptProperties();    const key = (props.getProperty('GOLDPRICE_API_KEY') || '').trim();    const sheetId = props.getProperty('GOLDPRICE_SHEET_ID');    if (!key) throw new Error('Set GOLDPRICE_API_KEY in Script Properties.');    if (!sheetId) throw new Error('Run installHourlyTrigger first.');    if (Date.now() < Number(props.getProperty('GOLDPRICE_RETRY_AT') || 0)) {      throw new Error('Rate limit pause: wait before fetching again.');    }    const response = UrlFetchApp.fetch(      'https://api.goldprice.dev/v1/spot/XAU-USD-SPOT',      {headers: {'X-API-Key': key}, muteHttpExceptions: true}    );    const status = response.getResponseCode();    if (status === 429) {      const headers = response.getAllHeaders();      const name = Object.keys(headers).find(function (name) {        return name.toLowerCase() === 'retry-after';      });      const value = String(headers[name] || '').trim();      let retryAt = /^\d+$/.test(value)        ? Date.now() + Number(value) * 1000 : Date.parse(value);      if (!Number.isFinite(retryAt)) retryAt = Date.now() + 3600000;      props.setProperty('GOLDPRICE_RETRY_AT', String(retryAt));      throw new Error('HTTP 429. Retry-After: ' + (value || 'pause for an hour'));    }    if (status !== 200) throw new Error('goldprice.dev HTTP ' + status);    const data = JSON.parse(response.getContentText());    const time = new Date(data.computed_at);    if (data.symbol !== 'XAU' || data.quote_currency !== 'USD' ||        data.unit !== 'troy_ounce' || data.contract_type !== 'spot' ||        typeof data.price !== 'string' || !/^\d+(\.\d+)?$/.test(data.price) ||        typeof data.computed_at !== 'string' || !Number.isFinite(time.getTime()) ||        typeof data.is_stale !== 'boolean') {      throw new Error('Unexpected spot response. Check the API reference.');    }    // Convert only for chart display. Do not use this number for money math.    const price = Number(data.price);    if (!Number.isFinite(price) || price <= 0) throw new Error('Invalid price.');    const row = [time, price, data.is_stale];    const book = SpreadsheetApp.openById(sheetId);    const history = goldSheet_(book, 'GoldHistory');    const latest = goldSheet_(book, 'GoldLatest');    writeGoldRow_(history, history.getLastRow() + 1, row);    writeGoldRow_(latest, 2, row);    props.deleteProperty('GOLDPRICE_RETRY_AT');    SpreadsheetApp.flush();  } finally {    lock.releaseLock();  }}function goldSheet_(book, name) {  const sheet = book.getSheetByName(name) || book.insertSheet(name);  if (sheet.getLastRow() === 0) {    sheet.getRange(1, 1, 1, 3).setValues([['time', 'price', 'is_stale']]);  }  return sheet;}function writeGoldRow_(sheet, rowNumber, values) {  if (rowNumber > sheet.getMaxRows()) {    sheet.insertRowsAfter(sheet.getMaxRows(), rowNumber - sheet.getMaxRows());  }  sheet.getRange(rowNumber, 1, 1, 3).setValues([values]);  sheet.getRange(rowNumber, 1).setNumberFormat('yyyy-mm-dd hh:mm:ss');  sheet.getRange(rowNumber, 2).setNumberFormat('0.00########');}

Install the hourly trigger

Save the script. Select installHourlyTrigger in the function menu and click Run. Authorize it using the Google account that owns the spreadsheet. It installs an hourly trigger and fetches an initial row.

Check the Triggers panel for appendGoldPrice. Reinstall using the same account. The installer replaces that account's matching triggers. Other accounts' triggers remain separate.

Hourly collection uses twenty four calls a day, about seven hundred and twenty a month, within Free's 5,000 monthly calls. Manual runs use extra calls.

Connect the spreadsheet to Looker Studio

In Looker Studio, create a data source with the Google Sheets connector. Select your spreadsheet and the GoldHistory worksheet. Enable Use first row as headers. Leave Optional Range empty so future rows are included. Connect it and create a report.

Add GoldLatest through the same connector as another data source in that report. For both sources, set time to Date & Time, price to Currency, USD, and is_stale to Boolean. Set data freshness to hourly. The report cache and script schedule run separately.

Build the chart and scorecard

Add a time series using GoldHistory. Set its dimension to time with Date Hour granularity and its metric to price with Average aggregation. Keep cumulative display off. Each point shows the average of observations in that hour.

Add a scorecard using GoldLatest, with price as its metric and Average aggregation. Label it Gold price, USD per troy ounce. Add a small GoldLatest table showing time and is_stale beside it. Label the time as UTC.

On Free, place a visible link to goldprice.dev beside the prices in your report. Choose a chart date range that includes your saved observations.

Expected output

JSON

{  "symbol": "XAU",  "quote_currency": "USD",  "unit": "troy_ounce",  "contract_type": "spot",  "price": "4023.54",  "is_stale": false,  "computed_at": "2026-10-23T10:51:32.965761+00:00"}

The JSON shows the API response before writing cells. GoldHistory appends time, price and is_stale. GoldLatest replaces its data row after each successful fetch. Time is a date cell from computed_at, shown in UTC. Price is a numeric chart cell; is_stale is a Boolean. Failed requests leave the previous values in place.

Common errors

Code	What you see	Fix
401	The execution fails with `goldprice.dev HTTP 401`	Check `GOLDPRICE_API_KEY` in Script Properties. Replace it with a valid key from your account, save, then run `appendGoldPrice`. Do not paste the key into cells or logs.
429	The execution reports `HTTP 429` or a rate limit pause	Read `Retry-After` in the execution error. The script stores a pause until that time and skips earlier fetches. If the header is missing or invalid, it pauses fetching for an hour. Check for duplicate triggers and other uses of your quota before trying again.
N/A	The scorecard shows a total across saved prices	Use `GoldLatest` as the scorecard data source and `price` with Average aggregation. Keep only the script's header and data row in that tab. Use `GoldHistory` for the time series.
N/A	The sheet updates but the report still shows an older value	Check each data source's freshness setting. In report view, open More options and choose Refresh data. Compare the displayed `time` with the sheet. Refreshing the report reads Sheets; only the script calls the API.
N/A	The tabs remain empty or hourly rows stop appearing	Open Apps Script Executions and inspect the latest failure. Confirm the trigger owner still has spreadsheet access and has authorized the script. After fixing the error, run `appendGoldPrice`. The previous scorecard value can remain visible after a failed fetch.

Going further

Add a date range control to explore saved observations
Filter the time series by is_stale when comparing observations
Build a separate EUR view using XAU-EUR-SPOT and label its currency clearly
Add daily history from /v1/bars, within your plan's history allowance

How to get live gold prices in Gold price in Looker Studio