In this tutorial, we’ll teach you how to use the API Connector add-on for Sheets to pull gold and silver data from the Metals API directly into Google Sheets. We’ll get an API key from Metals-API first, and then create a request to get metals data into your spreadsheet.
BEFORE YOU GET STARTED
Download the API Connector add-on from the Google Marketplace by clicking here.
PART 1: GET YOUR METALS-API API KEY
- If you haven’t already, navigate to https://metals-api.com/ and click GET API KEY in the top right corner.
- They have a variety of plans available, but for this example, we’ll start with the free one. Click the Get API Key.
- You’ll be prompted to create an account, and you’ll get a verification email shortly after. When you click on the email to check your account, you will be taken to the latest Metals-API dashboard. In the left-hand sidebar, click the ‘Dashboard’ icon.
- You should now be able to see your API access key. Create a copy of it and keep it somewhere safe; we’ll need it soon. You have now gained access to the Metals API.
PART 2: CREATE YOUR API REQUEST URL
We’ll start by following the Metals-API documentation to get the most recent gold and silver prices in USD.
- API root: https://metals-api.com/api
- Endpoint: /latest
- Query strings: ?base=USD&symbols=XAU,XAG&access_key=YOUR_ACCESS_KEY
When we bring it all 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: INSERT METALS API DATA INTO GOOGLE SHEETS
Now let’s copy and paste that URL into API Connector.
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- We don’t need any headers, so leave that segment clean. We don’t require any additional authentication, so leave authentication set to none.
- To use a new tab as your data destination, create a new tab and press ‘Set current.’
- Insert a name for your request and hit the Run button. You’ll see the most recent gold and silver information in your sheet a moment later.
General Observations:
- The timestamp is a UNIX timestamp. . You will use the Sheets feature to translate it back to a standard human-readable date. =B2/60/60/24 + DATE(1970,1,1)
- 1/rate will give you the price per unit in the currency you’ve chosen. For example, 1/.00056221356 = 1788.68 USD per ounce of gold.
PART 4: ADDITIONAL API URLs
To see other types of metals info, you can experiment with endpoints and query strings as clearly defined in the documentation, but if you just want to hop in and get a feel for it, play around with the URLs you enter in the API URL path sector. Try (one at a time) the following, substituting your own symbols and dates if desired.
- The most recent conversion rates for a category of currencies against the US dollar. Cash, crypto coins, and metals are examples of potential symbols, which are described below: 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 - Gold data fluctuation on a weekly basis. The maximum timeframe allowed 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
Also published on Medium.