We’ll show you how to utilize the API Connector add-on for Sheets to directly pull gold data from the Metals API into Google Sheets. We’ll start by getting an API key from Metals-API, then make a request to obtain gold data into your spreadsheet.
PRIOR TO BEGINNING, GET THE API CONNECTOR ADD-ON FROM THE GOOGLE MARKETPLACE BY CLICKING HERE.
FIRST SECTION: GET YOUR METALS-API API KEY
1- Go to https://metals-api.com/ and click GET API KEY in the top right corner if you haven’t already.
2- They offer a range of plans, but we’ll start with the free one for this demonstration. Select Get API Key from the drop-down menu.
3- You’ll be asked to establish an account, after which you’ll receive a verification email. You will be brought to the most recent Metals-API dashboard when you click on the email to check your account. Click the ‘Dashboard’ icon in the left-hand sidebar.
4- Your API access key should now be visible to you. Make a copy of it and store it safely; we’ll need it soon. The Metals API is now available to you.
SECOND SECTION: GENERATE AN API REQUEST URL
To retrieve the most recent gold prices in USD, we’ll start by following the Metals-API documentation.
- API root: https://metals-api.com/api
- Endpoint: /latest
- Query strings: ?base=USD&symbols=XAU,XAG&access_key=YOUR_ACCESS_KEY
When we combine everything, we get the following API Request URL: https://metals-api.com/api/latest?base=USD&symbols=XAU,XAG&access_key=YOUR_ACCESS_KEY
THIRD SECTION: INSERT DATA FROM THE GOLD API INTO GOOGLE SHEETS
Let’s take that URL and paste it into API Connector.
1- Click Add-ons > API Connector > Open in Google Sheets.
2- Enter the API URL we just defined in the Create tab
.
3- We don’t need any headers, so you can skip that segment. As we don’t need any more authentication, set authentication to none.
4- Create a new tab and press ‘Set current’ to utilize it as your data destination.
5- Give your request a name and press the Run button. In a moment, you’ll see the most recent gold data in your sheet.
Observations:
The timestamp is in UNIX format. To convert it back to a conventional human-readable date, you’ll use the Sheets function. B2/60/60/24 + DATE =B2/60/60/24 + DATE (1970,1,1)
1/rate returns the price per unit in the currency you specify. For example, per ounce of gold, 1/.00056221356 = 1788.68 USD.
FOURTH SECTION: URLS TO ADDITIONAL APIS
You can experiment with endpoints and query strings as stated in the documentation to see additional types of metals information, but if you just want to jump in and get a feel for it, play about with the URLs you enter in the API URL path sector
Also published on Medium.