Import MetalpriceAPI Data to Google Sheets

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

Before You Begin

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

Part 1: Get Your MetalpriceAPI API Key

1. Navigate to https://metalpriceapi.com/ and click GET FREE API KEY
   
2. You will be prompted to create an account and your will receive a verification email. Click on the email to verify your account, and you’ll be redirected to the dashboard.
3. Copy API Key

Part 2: Create Your API Request URL

In this example, we will get the latest price of gold and silver in USD.

• API root: https://api.metalpriceapi.com
• Endpoint: /v1/latest
• Query strings: ?base=USD&currencies=XAU,XAG&apikey=YOURAPI_KEY

Full API Request URL:

https://api.metalpriceapi.com/v1/latest?base=USD&currencies=XAU,XAG&api_key=Dxt3fx5NcET88EGsAtvrdJ

Part 3: Pull MetalpriceAPI Data into Google Sheets

From step 2, we will paste the final URL into API Connector.

1. Open up Google Sheets and click Extensions > API Connector > Open
2. In the Create tab, enter the API URL from step 2.
3. We don’t need OAuth Authentication so just leave that set to None. We don’t need any headers either so just leave that section blank.
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 =C2/60/60/24 + DATE(1970,1,1)
• 1/rate will give you the price per unit in your selected currency.
  • =1/D2 For example, 1/0.04078802 = 24.51 USD per ounce of silver.
  • =1/E2 For example, 1/0.00054302 = 1841.55 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 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.

• Historical price of gold and silver on 2022-01-30

https://api.metalpriceapi.com/v1/2022-01-30?api_key=[API_KEY]&base=USD&currencies=XAU,XAG

• Conversion rate of USD$25 to gold on 2022-01-30

https://api.metalpriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=XAU&amount=100&date=2022-01-30

• Conversion rate of USD$25 to silver on 2022-01-30

https://api.metalpriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=XAG&amount=100&date=2022-01-30

• Conversion rate of USD$25 to EUR on 2022-01-30

https://api.metalpriceapi.com/v1/convert?api_key=[API_KEY]&from=USD&to=EUR&amount=100&date=2022-01-30

• Time-series data for gold and silver between 2021-01-01 and 2021-12-01

https://api.metalpriceapi.com/v1/timeframe?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=XAU,XAG

• Percent change data for gold and silver between 2021-01-01 and 2021-12-01

https://api.metalpriceapi.com/v1/change?api_key=[API_KEY]&start_date=2021-01-01&end_date=2021-12-01&base=USD&currencies=XAU,XAG