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.

Contents

• Before You Begin
• Part 1: Get Metalprice API Key
• Part 2: Pull Metalprice API Data into Sheets
• Part 3: More Example URLs
• Part 4: API Documentation

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: Pull MetalpriceAPI Data into Google Sheets

For this request, we'll get the latest price of gold and silver in USD. Enter your own key where it reads your_key.

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://api.metalpriceapi.com/v1/latest?base=USD&currencies=XAU,XAG&api_key=your_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 populate 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 3: 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

Part 4: API Documentation

Official API documentation: https://metalpriceapi.com/documentation