How do I track crypto in Google Sheets?

This automation is serverless and uses the CoinMarketCap Free Tier API

Image by author

Hey there, I wrote this guide because similar to many crypto holders, I was spending too much time copying and pasting crypto price data manually into my Google Sheet portfolio trackers. I figured someone must have automated this by now, right?

Table of Contents Show

This automation is serverless and uses the CoinMarketCap Free Tier API
1) First, get yourself a Developer Account from CoinMarketCap
2) Next, create a new Google Sheet from sheets.google.com
3) From here, access Google Apps Script from Extensions > Apps Script
4) Were going to add a custom menu to our sheet and a way to store the Coin Market Cap API Key securely (e.g. not in plaintext)
5) Next, lets write the code needed to query the CoinMarketCap API
6) Now that the Endpoint works, write the returned data into your Google Sheet
7) Now Run Your Script
8) A new custom menu will appear in your Google Sheet after refreshing your browser window, or if you run OnOpen() manually
9) Add your CoinMarketCap API Key
10) Once youve set the API Key, run the Query
11) Use Google Sheets Formulas to combine data sources
12) Schedule your code to run by itself
13) Customizing the Script to retrieve other data (Optional)

At first, I tried looking online but I couldnt find anything that was easy to set up, and I didnt want to pay for premium features on a Spreadsheet Addon.

So, I did what any spreadsheet nerd would do: I coded a solution using some Google Apps wizardry and the Free Tier API from CoinMarketCapand voila, you can see my proof of concepts below:

For ease of use, I added example formulas in the Example Usage tab in the Top 5000 Coins spreadsheet

Your trading journal probably works differently than mine, so Ill end here, but hopefully you get the idea.

12) Schedule your code to run by itself

You can use time or action-based Google Apps Triggers to have this automatically refresh your sheet with live data. Just be sure to not go over the Free API limit of 333 calls per month.

13) Customizing the Script to retrieve other data (Optional)

Ill end here, but Ive included in the github repo another place where you can customize the API results, called parameters. Customizing your API results is documented in the https://coinmarketcap.com/api/documentation/v1/.

That said, if you make changes to the parameters, be prepared to change the rest of the script, in particular, changing the fields returned in Lines 40 and how they get mapped back to the Google Sheet: