In this article, we’ll learn how to get live crypto prices in Google Sheets using Live Coin Watch API.
Crypto traders and watchers have quite literally no time to even blink–for the prices of crypto could skyrocket (not quite to the moon, disappointingly) or plummet precipitously in a matter of seconds. Fortunately, for crypto aficionados, there are several ways to track the movement of cryptocurrencies.
Live Coin Watch is one platform dedicated to tracking crypto. It has real-time data on almost all major coins. And using Live Coin Watch API, we can get into greater depth and track the movements minutely so that we don’t tumble along with crypto prices if–or rather when–it does.
So here in this tutorial, we’re going to learn how to pull real-time crypto data from Live Coin Watch into Google Sheets using Amigo Data, a Google Sheets extension for data integration and automation.
Below are the steps to fetch real-time crypto prices in Google Sheets with Live Coin Watch API:
- Install the Amigo Data extension
- Generate the Live Coin Watch API key
- Select an endpoint
- Import the data
How to import data from Live Coin Watch into Google Sheets
Follow the steps below to get live crypto prices in Google Sheets using Live Coin Watch API.
1. Install the Amigo Data extension in Google Sheets
Install the Amigo Data Google Sheets add-on from the Google Workspace Marketplace.
Click here to install.
Alternatively, you can get it from Google Sheets.
- Open a new sheet
- Go to Extensions→Add-ons→Get add-ons
- Search for Amigo Data and install
2. Get Live Coin Watch API API key
Go to Live Coin Watch (livecoinwatch.com) and create an account.
Upon registration, a verification email will be sent to your email address. Confirm it.
After activating your account log in to your account and navigate to the API page.
Scroll to the API Playground section and click the Get API Key button to generate your Live Coin Watch API key.
On clicking the button, the API key will be generated.
Note the API key and keep it secret. If you lose it or suspect someone has access to yor key, you can regenerate it. This will be used to authenticate API calls to Live Coin Watch and import live crypto prices to Google Sheets.
3. Select an endpoint
Navigate to the Live Coin Watch API Documentation by clicking the READ THE DOCS link on the API page. You can also click this link to go to the documentation page.
On the documentation page, expand the Resources heading from the table of contents on the left of the Live Coin Watch API documentation page.
Select any one from the list of available endpoints, for example, coins/single to fetch the latest information about a single coin.
On the right side of the screen is the code snippet where we can find the endpoint URL and other details we need to pull live crypto data to Google Sheets from Live Coin Watch.
In the example above, the information for Ethereum will be returned with the price quoted in the US dollar. We can modify the data (-d) value to fetch the information for any coin in any currency–fiat or crypto–using the request parameters.
For example, to retrieve data for Doge in euro, the data value will be as follow:
{“currency”: “EUR”, “code”: “DOGE”, “meta”:true}
The meta parameter is for whether to include all coin information or not. True includes it while false excludes it.
Now that we have got all the things we need, we can begin to import Live Coin Watch data to Google Sheets.
4. Import crypto data to Google Sheets from Live Coin Watch
Open Google Sheets and launch Amigo Data to retrieve and import live crypto data to Google Sheets from Live Coin Watch.
You’ll be presented with a list of sources to connect to. Choose Custom API.
Change the method to POST and enter the Live Coin Watch API endpoint URL in the API URL field.
Expand the Body(JSON) tab and paste the API request body (-d values)– which for our example is: {“currency”:”EUR”,”code”:”DOGE”,”meta”:true}
Then expand the Details tab and enter the headers.
content-type: application/json
x-api-key: YOUR_API_KEY
Use your Live Coin Watch API key instead of YOUR_API_KEY.
Then click the PREVIEW button. Flatten and expand the nested datasets. And then click the IMPORT button to pull the live crypto data to Google Sheets from Live Coin Watch.
After a short while, the requested data will be imported into Google Sheets.
Auto-update the data
Once the data have been successfully imported, you can set it to update automatically at regular intervals so that you always have the latest data without starting the whole process over. And not just up-to-date information in Google Sheets but in inbox so that you don’t even need to open Google Sheets to stay updated. Visit this link to learn more about setting a refresh schedule in Amigo Data.
Some other endpoints
Below are some other endpoints that you can use to import crypto data from Live Coin Watch to Google Sheets.
Historical data a coin
API URL: https://api.livecoinwatch.com/coins/single/historY
Headers:
content-type: application/json
x-api-key: YOUR_API_KEY
Data values:
{“currency”:”USD”,”code”:”BTC”,”start”:1446316235000,
“end”:1451413835000,”meta”:true}
This will retrieve the historical value of Bitcoin between November 1, 2015 and December 31, 2015. The “start” and the “end” parameters are the date range, in Unix timestamp in milliseconds. You can use this tool to convert time to Unix.
To get the data for any other coin, replace BTC with the coin of your choice.
Top 100 coins by price
API URL: https://api.livecoinwatch.com/coins/list
Headers:
content-type: application/json
x-api-key: YOUR_API_KEY
Data values:
{“currency”:”USD”,”sort”:”price”,”order”:”descending”,
“offset”:0, “limit”:100,”meta”:false}
Visit our blog for to find out about the kinds of data that you can import to Google Sheets and how you can track stock and other financial data from Google Sheets.
FAQs
How do I automatically pull live crypto prices to Google Sheets?
Set a refresh schedule in Amigo Data after you’ve imported the crypto prices to Google Sheets with Live Coin Watch API. Amigo will then update the data automatically and you’ll always have fresh live crypto data in your Google Sheets. If you only want to update the daily, then set the refresh frequency to daily.
Can I retrieve historical cryptocurrency data using the Live Coin Watch API?
Yes, you can retrieve historical cryptocurrency data using Live Coin Watch API. Use the /coins/single/history endpoint to retrieve the historical cryptocurrency data.
How can I get get the list of all cryptocurrencies in Google Sheets?
To get the list of cryptocurrencies, use the coins/list endpoint. With this endpoint, you can get the list of the top 100 coins in circulation along with their price, market cap, volume, price change in the last one, day, week, etc. The limit is set to two by default; so change it to 100.
Some related articles you may be interested in: