Цена золота в Google Таблицах и Excel: актуальные цены через API

Актуальные цены на золото в электронной таблице

Многие расчёты с ценами на золото по-прежнему ведутся в электронных таблицах: прайс-лист ювелира, учёт слитков, таблица для оценки золотого лома или трекер портфеля. Если в Google Таблицах или Excel есть актуальная цена золота, эти числа обновляются сами, а не копируются с сайта. В этом руководстве мы построим оба варианта: пользовательскую функцию =GOLDPRICE("EUR", "gram") в Google Таблицах на Apps Script и таблицу Power Query в Excel — обе обновляются автоматически, а ключ API не хранится в ячейках.

Для обоих вариантов понадобится немного кода. MetalAPI возвращает JSON, а ни в одной из программ нет формулы листа, которая разбирала бы JSON по URL и передавала ключ API в заголовке. Apps Script и Power Query умеют и то и другое.

Вам нужен ключ MetalAPI: создайте бесплатный аккаунт — без банковской карты, со 100 запросами в месяц и ежедневным обновлением цен. Для Excel также нужна версия с Power Query («Получить и преобразовать данные», Get & Transform), например Excel для Microsoft 365 или Excel 2016 и новее для Windows. Цены настолько свежие, насколько позволяет ваш тариф: раз в день на Free, каждые 30 минут на Lite, каждые 10 минут на Standard и вплоть до каждых 5 секунд на Enterprise.

Как получать актуальные цены на золото в Google Таблицах

Основа — небольшой проект Apps Script, привязанный к вашей таблице: он читает ключ из свойства скрипта, получает цены через UrlFetchApp, кэширует их с помощью CacheService и предоставляет пользовательские функции для ячеек. Актуальность цен поддерживает триггер по времени.

1. Откройте таблицу и выберите Расширения → Apps Script (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 Script (Extensions → Apps Script) и прочитать их. Поэтому:

• Используйте отдельный ключ для каждой таблицы, чтобы отозвать его, не нарушая другие интеграции. О создании и ротации ключей рассказано в руководстве по аутентификации.
• Давайте право редактирования только тем, кому доверили бы ключ. Читатели и комментаторы не могут открыть редактор скриптов, но копия таблицы включает копию привязанного к ней скрипта. Для файлов с широким доступом запретите читателям и комментаторам копирование в настройках доступа.
• Передавайте ключ в заголовке. Скрипт использует X-API-Key, а не параметр api_key, поэтому ключ не попадает в журналы с URL.
• При малейших сомнениях меняйте ключ: создайте новый ключ, обновите свойство скрипта, а затем отзовите старый ключ в личном кабинете.

Пользовательская функция GOLDPRICE для Google Таблиц

Вставьте этот код в Code.gs. METALPRICE охватывает четыре драгоценных металла, а GOLDPRICE — сокращённый вариант для золота. Один запрос возвращает все четыре металла, поэтому золото и серебро в одной валюте и единице используют один закэшированный ответ.

// Цены MetalAPI для Google Таблиц. Ключ 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 Цена одной единицы металла в валюте.
 * @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 показывал другую валюту или единицу, измените аргументы fetchLatest_ в refreshPrices.

Подберите интервал под свой тариф: обновление чаще, чем меняются цены, лишь повторяет их:

Пользовательские функции с аргументом обновления добавляют по одному запросу на каждое обновление для каждой валюты и единицы. На Lite ежечасный триггер вместе с GOLDPRICE("EUR", "gram", Prices!B1) может дойти примерно до 1 500 запросов в месяц — больше, чем 1 000 по тарифу; используйте .everyHours(2) или только лист Prices. Триггеры срабатывают примерно, а не точно в запланированное время, а atHour использует часовой пояс скрипта.

Цена золота в Excel через Power Query

В Excel Power Query вызывает API, разбирает JSON и загружает таблицу, которая обновляется по расписанию:

1. На вкладке Данные (Data) выберите Получить данные → Из других источников → Пустой запрос (Get Data → From Other Sources → Blank Query).
2. В редакторе Power Query выберите Главная → Расширенный редактор (Home → Advanced Editor), вставьте запрос ниже и нажмите Готово (Done).
3. Когда Excel спросит, как подключаться к https://metalapi.com/, нажмите Изменить учетные данные (Edit Credentials), выберите Веб-API (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 ответа (EURXAU для EUR) и переводит Unix-время timestamp в UTC. Измените BaseCurrency и MassUnit под свои нужды.

ApiKeyName = "api_key" не даёт ключу попасть в файл: Power Query берёт его из учётных данных Веб-API (Web API) и добавляет как параметр запроса api_key — это один из трёх способов, которыми MetalAPI принимает ключ. Учётные данные хранятся в настройках источников данных Excel на вашем компьютере, а не в книге, поэтому коллега, открывший файл, должен будет ввести собственный ключ. Изменить или удалить их можно в разделе Данные → Получить данные → Параметры источника данных (Data → Get Data → Data Source Settings).

Web.Contents также принимает Headers = [#"X-API-Key" = "..."], но тогда ключ сохраняется внутри книги. Используйте этот вариант только для файлов, которые никогда не покидают ваш компьютер.

В формулах ищите цены в таблице: =XLOOKUP("XAU", GoldPrices[Symbol], GoldPrices[Price]) возвращает цену золота в евро за грамм (в русской версии Excel эта функция называется ПРОСМОТРX, а аргументы разделяются точкой с запятой), а в более старых версиях то же самое делает 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, до 30 дней на запрос на Free и до 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 вызывайте /timeframe через UrlFetchApp так же, как fetchLatest_ вызывает /latest, и записывайте все строки одним вызовом setValues.

Цены закрытия прошедших дней не меняются, поэтому загрузите диапазон один раз и сохраните его. О загрузке длинной истории, её хранении и графиках рассказано в статье «Исторические цены на золото и серебро через API».

Что дальше

Создайте бесплатный аккаунт, добавьте ключ как свойство скрипта или как учётные данные Веб-API — и примерно через десять минут цена золота появится в вашей таблице. Ежедневные цены тарифа Free подходят для ежедневного прайс-листа или таблицы портфеля; если нужны более свежие цены или более частые обновления, сравните тарифы на странице тарифов. В документации API перечислены все эндпоинты, которые можно вызывать так же, включая /convert и /carat для цен на золото по каратам на платных тарифах.

Частые вопросы

Можно ли вместо этого импортировать цены на золото через IMPORTDATA или IMPORTXML?

Из MetalAPI — нет. Эти функции читают CSV, TSV, XML или HTML, а API возвращает JSON. К тому же они не умеют передавать заголовки, поэтому ключ оказался бы в формуле на виду у каждого, кто открывает таблицу.

Как часто обновляется цена золота в моей таблице?

Так часто, как вы её обновляете и как позволяет тариф: раз в день на Free, каждые 30 минут на Lite, каждые 10 минут на Standard и чаще на старших тарифах. С 17:00 пятницы до 17:00 воскресенья по нью-йоркскому времени спотовый рынок закрыт, и цена остаётся на уровне закрытия пятницы.

Сколько запросов к API будет расходовать моя таблица?

Один на каждое обновление листа Prices или таблицы Power Query, плюс по одному на каждую валюту и единицу, когда пользовательские функции запрашивают свежие цены. Ежечасный триггер — это около 744 запросов в месяц, ежедневный — около 31. /usage показывает ваш расход и сам квоту не расходует.

Безопасно ли делиться таблицей, которая загружает цены на золото?

Да, если ключ не хранится в ячейках: в Google Таблицах — в свойстве скрипта, а право редактирования есть только у тех, кому вы доверяете ключ; в Excel — в учётных данных Веб-API. Используйте отдельный ключ для каждого общего файла, чтобы его можно было отозвать независимо от других.

Можно ли получить цену золота за грамм или в моей местной валюте?

Да. Передайте код валюты и gram или kilogram, например =GOLDPRICE("INR", "gram"), или измените BaseCurrency и MassUnit в запросе Power Query. API пересчитывает и то и другое из одного снимка цен.