Google 表格与 Excel 实时金价：通过 API 自动获取

在电子表格中使用实时金价

如今大量黄金定价工作仍在电子表格中完成：珠宝商的价目表、金条库存、旧金回收报价单或投资组合追踪表。在 Google 表格（Google Sheets）或 Excel 中使用实时金价后，这些数字会自动更新，而不必再从网站上复制。本指南将同时实现两者：在 Google 表格中用 Apps Script 编写的 =GOLDPRICE("EUR", "gram") 自定义函数，以及 Excel 中的 Power Query 表格，两者都会自动刷新，并且 API 密钥不会出现在单元格中。

两者都需要编写少量代码。MetalAPI 返回 JSON，而这两款电子表格都没有能从 URL 解析 JSON 并在请求头中发送 API 密钥的工作表公式。Apps Script 和 Power Query 则两者都能做到。

您需要一个 MetalAPI 密钥：创建免费账户即可获得，无需信用卡，每月 100 次请求，价格每日更新。对于 Excel，您还需要一个带有 Power Query（获取和转换）的版本，例如 Microsoft 365 版 Excel，或 Windows 上的 Excel 2016 及更高版本。价格的新鲜程度取决于您的套餐：Free 每天更新，Lite 每 30 分钟，Standard 每 10 分钟，Enterprise 最快每 5 秒。

如何在 Google 表格中获取实时金价

所需的设置是一个绑定到电子表格的小型 Apps Script 项目：它从脚本属性中读取密钥，用 UrlFetchApp 获取价格，用 CacheService 缓存价格，并为单元格提供自定义函数。时间驱动的触发器则让价格保持最新。

1. 打开电子表格，选择 扩展程序 → Apps 脚本（Extensions → Apps Script）。这会创建一个绑定到该文件的脚本项目。
2. 在 Apps Script 编辑器中，打开 项目设置（Project Settings，齿轮图标），滚动到 脚本属性（Script Properties），然后点击 添加脚本属性（Add script property）。将属性名设为 METALAPI_KEY，把您的密钥粘贴为值，然后点击 保存脚本属性（Save script properties）。
3. 回到 编辑器（Editor），将 Code.gs 的内容替换为下面的两段代码并保存。
4. 在函数菜单中选择 refreshPrices，然后点击 运行（Run）。Google 会请求您授权一次，允许脚本连接到外部服务并编辑电子表格。

不要把 API 密钥放在单元格中

不要把密钥粘贴到单元格中，再用公式拼接 URL。单元格中的密钥对所有能打开该电子表格的人都可见，会随每一份副本和导出文件一起传播，并且即使删除后也会保留在版本历史记录中。任何拿到密钥的人都可以消耗您的配额。

脚本属性是更好的存放位置：它们不属于表格网格，因此不会出现在单元格、导出文件或版本历史记录中。不过，它们也不是保险箱。任何拥有该电子表格编辑权限的人都可以打开 扩展程序 → Apps 脚本（Extensions → Apps Script）并读取它们。因此：

• 为每个电子表格使用单独的密钥，这样您就可以撤销它而不影响其他集成。身份验证指南介绍了如何创建和轮换密钥。
• 只把编辑权限授予您愿意交出密钥的人。查看者和评论者无法打开脚本编辑器，但电子表格的副本会包含其绑定脚本的副本。对于广泛共享的文件，请在共享设置中禁止查看者和评论者复制。
• 在请求头中发送密钥。脚本使用 X-API-Key，而不是 api_key 参数，因此密钥不会出现在被记录的 URL 中。
• 有疑虑时就轮换密钥：创建新密钥，更新脚本属性，然后在控制台中撤销旧密钥。

适用于 Google 表格的 GOLDPRICE 自定义函数

将以下代码粘贴到 Code.gs 中。METALPRICE 涵盖四种贵金属，GOLDPRICE 则是黄金的快捷方式。一次请求会返回全部四种金属，因此相同货币和单位下的黄金与白银共享同一个缓存响应。

// 适用于 Google 表格的 MetalAPI 价格。API 密钥从脚本属性
// METALAPI_KEY（项目设置）中读取，绝不从单元格中读取。
const API = "https://metalapi.com/api/v1";
const SYMBOLS = ["XAU", "XAG", "XPT", "XPD"];
const CACHE_SECONDS = 3600; // 1 小时；Apps Script 最多允许 21600

function fetchLatest_(base, unit) {
  const key = PropertiesService.getScriptProperties().getProperty("METALAPI_KEY");
  if (!key) throw new Error("Add the script property METALAPI_KEY first.");
  const url = API + "/latest?base=" + encodeURIComponent(base) +
    "&currencies=" + SYMBOLS.join(",") + "&unit=" + encodeURIComponent(unit);
  const res = UrlFetchApp.fetch(url, {
    headers: { "X-API-Key": key },
    muteHttpExceptions: true, // 返回错误响应，而不是抛出异常
  });
  let data;
  try {
    data = JSON.parse(res.getContentText());
  } catch (e) {
    throw new Error("MetalAPI: unexpected HTTP " + res.getResponseCode() + " response");
  }
  if (!data.success) {
    throw new Error("MetalAPI " + data.error.code + " (" + data.error.type + "): " + data.error.info);
  }
  return data;
}

function cachedLatest_(base, unit, refresh) {
  const cache = CacheService.getScriptCache();
  const cacheKey = ["latest", base, unit, refresh].join("|");
  const hit = cache.get(cacheKey);
  if (hit) return JSON.parse(hit);
  const data = fetchLatest_(base, unit);
  cache.put(cacheKey, JSON.stringify(data), CACHE_SECONDS);
  return data;
}

/**
 * 来自 MetalAPI 的贵金属最新价格。
 *
 * @param {string} symbol XAU（黄金）、XAG（白银）、XPT（铂金）或 XPD（钯金）。
 * @param {string} currency 货币代码，例如 USD、EUR 或 GBP。默认为 USD。
 * @param {string} unit troy_oz（默认）、gram 或 kilogram。
 * @param {any} refresh 可选的单元格，例如 Prices!B1。其值变化时会重新获取价格。
 * @return 1 单位该金属以指定货币计的价格。
 * @customfunction
 */
function METALPRICE(symbol, currency, unit, refresh) {
  const s = String(symbol || "XAU").trim().toUpperCase();
  const base = String(currency || "USD").trim().toUpperCase();
  const u = String(unit || "troy_oz").trim().toLowerCase();
  const data = cachedLatest_(base, u, String(refresh || ""));
  const price = data.rates[data.base + s];
  if (price === undefined) throw new Error("No " + s + " price in " + base + ".");
  return price;
}

/**
 * 来自 MetalAPI 的黄金最新价格，例如 =GOLDPRICE("EUR", "gram")。
 *
 * @param {string} currency 货币代码，例如 USD、EUR 或 GBP。默认为 USD。
 * @param {string} unit troy_oz（默认）、gram 或 kilogram。
 * @param {any} refresh 可选的单元格，例如 Prices!B1。其值变化时会重新获取价格。
 * @return 以指定货币计的每单位黄金价格。
 * @customfunction
 */
function GOLDPRICE(currency, unit, refresh) {
  return METALPRICE("XAU", currency, unit, refresh);
}

现在，在任意单元格中输入公式：

工作原理：

• @customfunction 会把这些函数加入公式自动补全，并将注释用作帮助文本。
• muteHttpExceptions: true 会返回错误响应而不是抛出异常，因此单元格会显示 #ERROR!，鼠标悬停时可以看到 MetalAPI 的错误代码和消息，例如未知货币对应的 201（invalid_base）。
• CacheService 会将每个响应保留 CACHE_SECONDS 秒，最长六小时。货币、单位和刷新值相同的单元格共享同一个缓存响应。
• 每种未缓存的货币和单位组合消耗一次请求，而且每个公式单元格都是对 Google 服务器的一次单独调用。对于包含数百个价格单元格的表格，请改为引用下文的 Prices 工作表。

未知货币等校验错误会计费，因此请及时修正出错的公式。速率限制指南列出了哪些请求会计费。

用时间驱动的触发器刷新 Google 表格中的金价

Google 表格会在自定义函数的参数发生变化时重新运行它，而不是按时钟运行；文件 → 设置 → 计算（File → Settings → Calculation）下的重新计算设置只影响 NOW 和 RAND 等函数。如需定时更新，请让时间驱动的触发器把价格写入一个工作表。将以下代码添加到第 1 部分下方：

/** 将最新价格写入名为 "Prices" 的工作表。请通过时间驱动的触发器运行。 */
function refreshPrices() {
  const data = fetchLatest_("USD", "troy_oz");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Prices") || ss.insertSheet("Prices");
  const rows = [
    ["Updated", new Date(data.timestamp * 1000)],
    ["Symbol", data.base + " per troy oz"],
  ];
  SYMBOLS.forEach(function (s) {
    rows.push([s, data.rates[data.base + s]]);
  });
  sheet.getRange(1, 1, rows.length, 2).setValues(rows);
}

/** 在编辑器中运行一次。会替换 refreshPrices 现有的所有计划。 */
function installTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === "refreshPrices") ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger("refreshPrices").timeBased().everyHours(1).create();
}

在编辑器中运行一次 installTrigger，或者在 触发器（Triggers，时钟图标）→ 添加触发器（Add Trigger）中手动添加触发器，选择 refreshPrices、时间驱动（Time-driven）和一个时间间隔。

每次运行消耗一次请求，并将数据时间写入 Prices!B1，下方每种金属占一行；Prices!B3 是以美元/金衡盎司计的金价。您可以直接引用这些单元格，也可以把 Prices!B1 作为刷新参数传入：=GOLDPRICE("EUR", "gram", Prices!B1) 会在触发器写入新的数据时间时重新获取价格，每种货币和单位组合一次；而在周末，由于时间不变，则完全不会重新获取。如需让 Prices 工作表使用其他货币或单位，请修改 refreshPrices 中 fetchLatest_ 的参数。

请让间隔与您的套餐相匹配；刷新频率高于价格更新频率只会得到重复的数字：

带刷新参数的自定义函数，每次刷新时每种货币和单位组合还会再增加一次请求。在 Lite 套餐中，每小时触发一次再加上 GOLDPRICE("EUR", "gram", Prices!B1)，每月可能达到约 1,500 次请求，超出该套餐的 1,000 次；请改用 .everyHours(2)，或只使用 Prices 工作表。触发器会在接近而非恰好在计划时间运行，并且 atHour 使用脚本的时区。

用 Power Query 在 Excel 中获取金价

在 Excel 中，由 Power Query 调用 API、解析 JSON，并加载一个可以按计划刷新的表格：

1. 在 数据（Data）选项卡上，选择 获取数据 → 自其他源 → 空白查询（Get Data → From Other Sources → Blank Query）。
2. 在 Power Query 编辑器中，选择 开始 → 高级编辑器（Home → Advanced Editor），粘贴下面的查询，然后点击 完成（Done）。
3. 当系统询问如何连接到 https://metalapi.com/ 时，点击 编辑凭据（Edit Credentials），选择 Web API，将密钥粘贴到 密钥（Key）中，然后点击 连接（Connect）。
4. 将查询重命名为 GoldPrices，然后选择 开始 → 关闭并上载（Home → Close & Load）。Excel 会将其作为以查询命名的表格加载到一个新工作表中。

let
    BaseCurrency = "EUR",
    MassUnit = "gram",
    Symbols = {"XAU", "XAG", "XPT", "XPD"},
    Response = Json.Document(
        Web.Contents(
            "https://metalapi.com/api/v1/latest",
            [
                Query = [base = BaseCurrency, currencies = Text.Combine(Symbols, ","), unit = MassUnit],
                ApiKeyName = "api_key"
            ]
        )
    ),
    Updated = #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Response[timestamp]),
    Prices = Table.FromRecords(
        List.Transform(
            Symbols,
            (symbol) => [
                Symbol = symbol,
                Price = Record.Field(Response[rates], Response[base] & symbol),
                Currency = Response[base],
                Unit = MassUnit,
                #"Updated (UTC)" = Updated
            ]
        )
    ),
    Typed = Table.TransformColumnTypes(
        Prices,
        {{"Symbol", type text}, {"Price", type number}, {"Currency", type text},
         {"Unit", type text}, {"Updated (UTC)", type datetime}}
    )
in
    Typed

该查询读取以响应中的 base 为前缀的键（EUR 对应 EURXAU），并将 Unix timestamp 转换为 UTC 时间。您可以根据需要修改 BaseCurrency 和 MassUnit。

ApiKeyName = "api_key" 让密钥不会出现在文件中：Power Query 从 Web API 凭据中获取密钥，并将其作为 api_key 查询参数附加到请求中，这是 MetalAPI 接受密钥的三种方式之一。凭据保存在您计算机上 Excel 的数据源设置中，而不在工作簿里，因此打开该文件的同事会被要求输入他们自己的密钥。您可以在 数据 → 获取数据 → 数据源设置（Data → Get Data → Data Source Settings）中修改或删除它。

Web.Contents 也接受 Headers = [#"X-API-Key" = "..."]，但这会把密钥保存在工作簿中。请只在从不离开您计算机的文件中使用这种方式。

在公式中，请在表格中查找价格：=XLOOKUP("XAU", GoldPrices[Symbol], GoldPrices[Price]) 返回以欧元/克计的金价；在较旧的版本中，可以用 INDEX 配合 MATCH 实现同样的效果。

自动刷新 Excel 中的金价

Power Query 表格不会自行刷新。打开 数据 → 查询和连接（Data → Queries & Connections），右键单击 GoldPrices 查询并选择 属性（Properties）。在 刷新控件（Refresh control）下：

• 刷新频率 N 分钟（Refresh every N minutes）会按定时器重新运行查询。
• 打开文件时刷新数据（Refresh data when opening the file）会在每次打开工作簿时获取最新价格。
• 允许后台刷新（Enable background refresh）让 Excel 在查询运行期间仍可正常使用。

数据 → 全部刷新（Data → Refresh All，Ctrl+Alt+F5）可按需刷新。定时器只在工作簿打开时运行。

每次刷新消耗一次请求。在每天八小时的工作时间内每小时刷新一次，每月约 180 次；每 10 分钟刷新一次，每月约 1,050 次。在价格每日更新的 Free 套餐中，打开文件时刷新就足够了。

Google 表格和 Excel 中的黄金历史价格

过去的收盘价来自 /timeframe：start_date 与 end_date 之间每个交易日一条记录，Free 套餐每次请求最多 30 天，付费套餐最多 365 天。在 Excel 中，再添加一个查询即可得到一个日期列和每种金属各一列：

let
    Response = Json.Document(
        Web.Contents(
            "https://metalapi.com/api/v1/timeframe",
            [
                Query = [start_date = "2026-09-24", end_date = "2026-10-23", currencies = "XAU,XAG"],
                ApiKeyName = "api_key"
            ]
        )
    ),
    Days = Record.ToTable(Response[rates]),
    History = Table.FromRecords(
        List.Transform(
            Table.ToRecords(Days),
            (row) => [
                Date = Date.FromText(row[Name]),
                Gold = Record.FieldOrDefault(row[Value], Response[base] & "XAU", null),
                Silver = Record.FieldOrDefault(row[Value], Response[base] & "XAG", null)
            ]
        )
    ),
    Typed = Table.TransformColumnTypes(History, {{"Date", type date}, {"Gold", type number}, {"Silver", type number}})
in
    Typed

周末和市场假日的数据会缺失，因此行数会少于日历天数。在 Apps Script 中，请像 fetchLatest_ 调用 /latest 那样用 UrlFetchApp 调用 /timeframe，并通过一次 setValues 调用写入所有行。

过去的收盘价永远不会改变，因此只需加载一次区间数据并保存下来。通过 API 获取黄金和白银历史价格介绍了长区间回填、存储和图表绘制。

后续步骤

创建免费账户，将密钥添加为脚本属性或 Web API 凭据，大约十分钟内就能在电子表格中看到金价。Free 套餐的每日价格适合每日价目表或投资组合表格；如需更新鲜的价格或更多刷新次数，请在定价页面上比较各套餐。API 文档列出了可以用同样方式调用的所有端点，包括 /convert，以及付费套餐中提供 K 金价格的 /carat。

常见问题

可以改用 IMPORTDATA 或 IMPORTXML 导入金价吗？

无法从 MetalAPI 导入。这些函数读取的是 CSV、TSV、XML 或 HTML，而 API 返回的是 JSON；而且它们无法发送请求头，因此密钥只能写在公式中，任何查看者都能看到。

电子表格中的金价多久更新一次？

取决于您的刷新频率和套餐允许的频率：Free 每天一次，Lite 每 30 分钟，Standard 每 10 分钟，更高的套餐更快。从纽约时间周五 17:00 到周日 17:00，现货市场休市，价格保持在周五的收盘价。

我的电子表格会消耗多少次 API 请求？

Prices 工作表或 Power Query 表格每刷新一次消耗一次请求；当自定义函数获取最新价格时，每种货币和单位组合再各消耗一次。每小时运行的触发器每月约 744 次请求，每天运行的约 31 次。/usage 会报告您的用量，且本身不计入配额。

共享一个获取金价的电子表格安全吗？

安全，前提是密钥不出现在单元格中：在 Google 表格中使用脚本属性，并只向您信任、愿意交出密钥的人授予编辑权限；在 Excel 中使用 Web API 凭据。请为每个共享文件使用单独的密钥，以便单独撤销。

可以获取每克金价或以本地货币计的金价吗？

可以。传入货币代码以及 gram 或 kilogram，例如 =GOLDPRICE("INR", "gram")，或者修改 Power Query 中的 BaseCurrency 和 MassUnit。API 会基于同一快照完成这两种换算。