In this article we will show how to import CoinMarketCap data to Google Sheets with the CoinMarketCap API. Simply follow the steps below.
How to Add CoinMarketCap API Data to Google Sheets
1. Get CoinMarketCap API Key
The CoinMarketCap API can be retrieved through the following link:
Click Get Your API Key Now.
You will have an option to either sign up or log in to your existing CoinMarketCap account. Choose whichever is applicable to your case. If you don’t have an account yet, you can simply sign up, choosing the Basic Plan for starters.
After signing up, you can go to the login screen to access your account.
The Developers screen will load after logging in.
The API key generated for you for accessing CoinMarketCap data is prominently placed in the main page (but censored for security).
Move your cursor to the box. A button labeled Copy Key will appear. Click it. You now have a copy of the API key.
2. Copy the CoinMarketCap Sample Spreadsheet
Next, we need to have a script added to our spreadsheet. Alternatively, you can copy the following spreadsheet. It already contains the script and has the sheet prepared:
https://docs.google.com/spreadsheets/d/1ii4RFyM9LZvf8qxEImYLjMIAJNXICXcYzQfoity8IUI/edit?usp=sharing
You cannot edit the spreadsheet, but you can make a copy to modify. Click File, then select Make a copy.
A box labeled Copy document will be loaded. Set the name of the new sheet, then click Make a copy. This action will include the script attached to the sheet.
3. Add the API Key to the Script
Your copy of the sheet will be loaded in a new tab. You can now add the API key to the script.
Open the script by clicking Extensions, then selecting Apps Script.
A new tab will be loaded containing the script.
Go to line 12. Replace ‘YOUR_API_KEY_GOES_HERE’ with the API key you got from Step 1, also enclosed in single quotes.
Once done, click Save project.
4. Run the Script
To test the API key, run the script by clicking Run the selected function icon. It is just to the right of the Save button.
As it is the first time running the script, you need to authorize it to access your data. A pop-up box will appear asking your permission to access your data. Click Review permissions.
A new window will load, asking you to select the Google account that the script can access. Select the Google account you are currently using.
A webpage will appear reminding you that Google hasn’t verified the script. Click Advanced.
Additional information will appear at the bottom. Click Go to coins (unsafe).
The list of permissions needed by the script will be listed next. Click Allow.
The window will close and the script will start executing. After some time it will be completed. The progress can be seen in the Execution log that will appear once the script starts running.
Once the Execution completed notice appears, go back to the sheet. The data is now loaded.
5. Add a Button
It is quite burdensome if we have to open Apps Script every time we need updated values of the chosen cryptocurrencies. We can, instead, add a button that we can click anytime we want to refresh the data.
To start, click Insert, then select Drawing.
The Drawing box will appear. Click the Shapes icon, then select Shapes, then choose Rounded Rectangle. Check the image below to see where the shape is.
The cursor will change to a plus sign. That is the signal for you to draw the rounded rectangle in the canvas. Click and drag your cursor until you reach the ideal size of the button. Next, click the Text box icon.
The cursor will also change shape; click and drag it until you reach the ideal size of the text. Type Access Prices or any other label you want to add to the button.
Once you are done, click Save and Close. The button will appear in the sheet. You can resize it and move it to another place in your sheet.
6. Connect the Script to the Button
To connect the script to the button, hover your cursor to the button and then click the three dots found on its upper-right corner. A drop-down list will appear. Select Assign script.
A box labeled Assign script will appear. Type coin_price, then click OK.
We can now run the script anytime we want and need!
FAQs
How to Add or Remove Crypto Coins in the List?
To add crypto coins to the list, just type the crypto symbol in Column B. For example, we will add BNB, XRP, and ADA. We simply type these symbols in Column B.
Afterwards, we click the button we made. The prices are now loaded to the sheet, along Column C.
To remove Crypto coins from the list, simply delete the symbol, then click the button again.
Recommended articles
More resources
