Import Metals API Data to Google Sheets

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
• Part 1: Get your Metals-API API Key
• Part 2: Create your API Request URL
• Part 3: Pull API data into Sheets
• Part 4: More Example API URLs
• Part 5: Handle Pagination

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.
   
2. They offer a range of plans, but for this example we’ll start with the free plan. Click Get API Key.
   
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.
   
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.
   

PART 2: CREATE YOUR API REQUEST URL

We’ll first follow the Metals-API documentation to access the latest price of gold and silver in USD.

• API root: https://metals-api.com/api
• Endpoint: /latest
• Query strings: ?base=USD&symbols=XAU,XAG&access_key=YOUR_ACCESS_KEY

Putting it together, we get the full API Request URL:

https://metals-api.com/api/latest?base=USD&symbols=XAU,XAG&access_key=YOUR_ACCESS_KEY

PART 3: PULL METALS API DATA INTO GOOGLE SHEETS

Now let’s copy and paste that URL into API Connector.

1. Open up Google Sheets and click Add-ons > API Connector > Open.
2. In the Create tab, enter the API URL we just created.
    
3. We don’t need any headers so just leave that section blank. We don’t need any extra authentication, either, so leave authentication set to None.
4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
5. Name your request and click Run. A moment later you’ll see the latest gold and silver data in your sheet.
   

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 4: 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 if you just want to jump in and get a feel for it, play around with the URLs you enter in the API URL path field. Try the following (one at a time), substituting in your own symbols and dates if you like.

• 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 2016-01-01

  https://metals-api.com/api/2016-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 2020-01-01 and 2020-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=2020-01-01&end_date=2020-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=2021-01-01&end_date=2021-01-31&access_key=API_KEY