gold-api-example.ts

type GoldApiResponse = {
  timestamp: number;
  metal: string;
  currency: string;
  exchange: string;
  symbol: string;
  prev_close_price: number;
  open_price: number;
  low_price: number;
  high_price: number;
  open_time: number;
  price: number;
  ch: number;
  chp: number;
  ask: number;
  bid: number;
  price_gram_24k: number;
  price_gram_22k: number;
  price_gram_21k: number;
  price_gram_20k: number;
  price_gram_18k: number;
  price_gram_16k: number;
  price_gram_14k: number;
  price_gram_10k: number;
};

type GoldApiErrorResponse = {
  error?: string;
  message?: string;
};

async function main(
  workbook: ExcelScript.Workbook,
  goldApiToken: string,
  metal: string = "XAU",
  currency: string = "USD",
) {
  if (!goldApiToken) {
    throw new Error("Missing GoldAPI.io token.");
  }

  const data = await fetchGoldPrice(goldApiToken, metal, currency);
  writeGoldPrice(workbook, data);
}

async function fetchGoldPrice(
  token: string,
  metalSymbol: string,
  currencySymbol: string,
): Promise<GoldApiResponse> {
  const endpoint = `https://www.goldapi.io/api/${encodeURIComponent(metalSymbol)}/${encodeURIComponent(currencySymbol)}`;

  const response = await fetch(endpoint, {
    method: "GET",
    headers: {
      "x-access-token": token,
      Accept: "application/json",
    },
  });

  const payload = (await response.json().catch(() => null)) as
    | GoldApiResponse
    | GoldApiErrorResponse
    | null;

  if (!response.ok) {
    const errorPayload = payload as GoldApiErrorResponse | null;
    const message = errorPayload?.error ?? errorPayload?.message ?? response.statusText;
    throw new Error(`GoldAPI.io request failed (${response.status}): ${message}`);
  }

  if (!payload) {
    throw new Error("GoldAPI.io returned an empty or invalid JSON response.");
  }

  return payload as GoldApiResponse;
}

function writeGoldPrice(workbook: ExcelScript.Workbook, data: GoldApiResponse) {
  const sheet = getOrCreateWorksheet(workbook, "GoldAPI");
  const usedRange = sheet.getUsedRange();

  if (usedRange) {
    usedRange.clear();
  }

  const summaryRows: (string | number)[][] = [
    ["Field", "Value"],
    ["Metal", data.metal],
    ["Currency", data.currency],
    ["Price", data.price],
    ["Bid", data.bid],
    ["Ask", data.ask],
    ["Change", data.ch],
    ["Change Percent", `${data.chp}%`],
    ["Exchange", data.exchange],
    ["Symbol", data.symbol],
    ["Timestamp", data.timestamp],
  ];

  const summaryRange = sheet.getRangeByIndexes(0, 0, summaryRows.length, 2);
  summaryRange.setValues(summaryRows);
  summaryRange.getFormat().autofitColumns();

  const headerRange = sheet.getRange("A1:B1");
  headerRange.getFormat().getFont().setBold(true);

  sheet.getRange("D1").setValue("Full response");
  sheet.getRange("D1").getFormat().getFont().setBold(true);
  sheet.getRange("D2").setValue(JSON.stringify(data, null, 2));
  sheet.getRange("D2").getFormat().setWrapText(true);
  sheet.getRange("D:D").getFormat().setColumnWidth(420);
}

function getOrCreateWorksheet(
  workbook: ExcelScript.Workbook,
  name: string,
): ExcelScript.Worksheet {
  return workbook.getWorksheet(name) ?? workbook.addWorksheet(name);
}