Search API Connector Documentation

Print

Import Metals API Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull gold and silver data from the Metals API directly into Google Sheets, using the API Connector add-on for Sheets. We'll first get an API key from Metals-API, and then set up a request to pull in metals data to your spreadsheet.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your Metals-API API Key

  1. If you haven't already, navigate to https://metals-api.com/ and click GET API KEY in the top right corner.
    metals-api-img1
  2. They offer a range of plans, but for this example we'll start with the free plan. Click Get API Key.
    metals-api-img2
  3. You'll be prompted to create an account, and a moment later will receive a verification email. Click on the email to verify your account, and you'll be redirected into your new Metals-API dashboard. Click the Dashboard link in the left-hand sidebar.
    metals-api-img3
  4. You'll now see your API access key. Copy it and keep it safe, we'll need it in a moment. Congrats, you now have access to the Metals API.
    metals-api-img4

Part 2: Pull Metals API Data into Google Sheets

For this example, we'll get the latest price of gold and silver in USD.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • Application: Custom
    • Method: GET
    • Request URL: https://metals-api.com/api/latest?base=USD&symbols=XAU,XAG&access_key=YOUR_ACCESS_KEY
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. Name your request and click Run. A moment later you’ll see the latest gold and silver data in your sheet.
    metals-api-img6

Notes:

  • The timestamp is a UNIX timestamp. You can convert it back to a regular human-readable date with the Sheets function =B2/60/60/24 + DATE(1970,1,1)
  • 1/rate will give you the price per unit in your selected currency. For example, 1/.00056221356 = 1788.68 USD per ounce of gold.

Part 3: More Example API URLs

You can experiment with endpoints and query strings as described in the documentation to see other types of metals data, but here are some examples to get started:

  • latest conversion rates for a set of currencies against USD. Possible symbols include currencies, crypto coins, and metals, and are listed here: https://metals-api.com/currencies
    https://metals-api.com/api/latest?base=USD&symbols=GBP,JPY,EUR&access_key=API_KEY
  • historical price of gold and silver on 2022-01-01
    https://metals-api.com/api/2022-01-01?base=USD&symbols=XAU,XAG&access_key=API_KEY
  • conversion rate of USD$25 to gold on 2012-01-01
    https://metals-api.com/api/convert?from=USD&to=XAU&amount=25&date=2012-01-01&access_key=API_KEY
  • time-series data for gold between 2023-01-01 and 2023-01-05 (daily time series data ranges are limited to 5 days and a single symbol at a time)
    https://metals-api.com/api/timeseries?base=USD&symbols=XAU&start_date=2023-01-01&end_date=2023-01-05&access_key=API_KEY
  • weekly data fluctuations for gold. The max allowed timeframe is 365 days.
    https://metals-api.com/api/fluctuation?base=USD&symbols=XAU&type=weekly&start_date=2023-01-01&end_date=2023-01-31&access_key=API_KEY

Part 4: API Documentation

Official API documentation: https://metals-api.com/documentation

Leave a Comment