A Crypto Portfolio Tracker in Google Sheets using the CoinGecko API.

With investment in crypto currencies on the rise, it's only smart that one starts to keep track of their crypto investment portfolio.

In this article, I will walk you through creating a crypto portfolio tracker in Google Sheets using the CoinGecko API. In the end we will be able to input our crypto purchases and have those purchases summarized in another sheet.

Step 1: Let's get the base sheet to build on top of.

First we are going to go to this Link and make a copy of that sheet.

We will be editing this sheet using the CoinGecko API so feel free to remove some unwanted cells. I removed the Refresh Rates cell together with its checkbox and I also removed the text below that is highlighted in yellow.

sheet 1.PNG

Step 2: Adding background logic to the Google Sheet so that it responds to the CoinGecko API.

Now that we have our sheet, lets link it to the CoinGecko API. First we will add scripts to our Google Sheets to import data and auto-refresh.

Script 1: ImportJSON – This script allows your sheet to import CoinGecko’s API data

Open a new Google Sheets try this
On the top left menu section, click on “Extensions – < > Apps Script”
Add in a new script using the “+” button
Copy and replace everything with the importJSON script from here.

sheet 2.PNG

Save and name the script ImportJSON.

Script 2: autoRefresh – So your sheet can automatically refresh

Same as above, add a new script using the “+” button.
Copy and replace everything from below, then save the script as autoRefresh.
As the name suggests – this allows you to automatically refreshes your sheet at fixed intervals.

 * This function by Vadorequest generates a random number in the "randomNumber" sheet.
 *
 * It needs to be triggered with a Google Apps Scripts trigger at https://script.google.com/home/:
 *   - Select project and add trigger
 *   - Choose which function to run: triggerAutoRefresh
 *   - Select event source: Time-driven
 *   - Select type of time based trigger: Minutes timer
 *   - Select minute interval: 10 minutes (to avoid too many requests)
 **/

// Updates cell A1 in "randomNumber" with a random number
function triggerAutoRefresh() {  
    SpreadsheetApp.getActive().getSheetByName('doNotDelete').getRange(1, 1).setValue(getRandomInt(1, 200));
}
// Basic Math.random() function
function getRandomInt(min, max) {
    min = Math.ceil(min);
    max = Math.floor(max);
    return Math.floor(Math.random() * (max - min + 1)) + min;
}

Step 3: Let’s get some auto-refresh going on!

For script 2(autoRefresh), we’ll need to make some small settings to get it to work. Only two steps here!

First, create a new sheet in our spreadsheet called doNotDelete.

Next, create a Trigger (Google Sheet function) so auto refresh works. To do that:

Head back to “Extensions – < > Apps Script – ⏰”
Set up a new Trigger (bottom right corner) with the following settings:

- triggerAutoRefresh

- Time-driven

- Minutes timer

- 5 to 10 minutes (anything less might not be useful as results are cached).

- Save and close Apps Script.

To read more about this set-up, check out this article .

Step 4 - Get coins with CoinGecko API.

We can now go to our doNotDelete sheet and call some APIs!!!!

For this project, I want to call specific coins names and get their prices. In order to do this, let's first get all the coin ids with this CoinGecko API:

=IMPORTJSON("https://api.coingecko.com/api/v3/coins/list")

To keep things organized, I will be calling this API in a separate Google Sheet from this one.

Now that I have all the coin ids that I need, I am going to use this API to get the names and prices of these specific coins:

=ImportJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=btrfly%2Colympus%2Cstaked-olympus%2Cgovernance-ohm%2Cstrong%2Cfantasy-world-gold%2Cklima-dao%2Cstaked-klima%2Cwonderland%2Cpresearch%2Cstar-atlas%2Cpolkadot%2Cethereum%2Cbitcoin%2Cbinancecoin%2Cring-financial%2Cpop-chest-token%2Cspore%2Cconvergence%2Cbutterfly-protocol-2%2Cthor&order=market_cap_desc&per_page=100&page=1&sparkline=false#","/name,/current_price","noTruncate",doNotDelete!$A$1)

This is my result.

Step 5: Map data to display in Purchases and Summary sheet

Now that we have finished calling the API with our data in the doNotDelete sheet, let's map it to display in our purchases and summary sheet.

5.1. Mapping data to create coin name dropdowns in the Purchases and Summary sheet.

In the coin column (right after timestamp), I have removed the coins and replaced them with dropdowns.

I wanted to ensure that instead of manually inputting the coin name, a user could instead just pick it from a dropdown.(pretty flexible right?)

There are quite a few articles on how to create a dropdown in Google Sheets. I liked this YouTube tutorial though. Check it out!

So after creating the dropdowns and copy-pasting them downwards, this is how my purchases sheet looks like now.

I then also did the same thing in the summary sheet with the Coin column.

Note1: When creating your dropdown and selecting your data range, make sure to map it to the doNotDelete sheet and select only the column with the names and not the whole dataset including the prices.

Note2: Whatever coin names you have selected in the dropdowns in the purchases sheet, these should be the same as those in the summary sheet or else the data won't reflect.

5.2. Map prices from the doNotDelete sheet to the summary sheet.

So basically if you have reached this far, you can see that a certain part of our sheet just displays N/A.

This is because the remaining columns in the summary sheet depend on the coin price to actually be calculated.

Lets get those prices in!

lets go.webp

So to do this, I used a VLOOKUP function.

VLOOKUP is divided into the search_key, range, index and is_sorted. VLOOKUP(search_key, range, index, [is_sorted])

A few things to note before implementing this function in our spreadsheet:

We want to call our VLOOKUP function in the summary page and in the Current Price column.
The first price will need to be identical to the first coin name in the dropdown in the first cell in the Coins column. For me the first cell is in A3.
We want to map this data from the doNotDelete sheet.
In the doNotDelete sheet, we want the prices to map to the coin name.
There are 2 columns in the doNotDelete sheet. (Name and Current Price)
We will set is_sorted to FALSE. If set to FALSE, an exact match is returned.

So from this brief breakdown, we can now use this VLOOKUP function in cell E3 in our summary sheet, in the Current Price column:

=VLOOKUP(A3,doNotDelete!$A$1:B,2,FALSE) and then just copy and paste it across all the other cells in the current price column in the same sheet.

If the coins in the dropdown are the same as those you chose in the purchases sheet, the prices and all the other data after that should come right up.

To read/watch more about VLOOKUP, you can watch this YouTube video here . (Check out this channel if you're keen on learning all things spreadsheets lol)

This is how my Summary sheet looks like now.

And we're done guys! WE DID IT!!!!

From this tutorial, we have been able to create a Portfolio Tracker using the CoinGecko API that not only calculates our crypto purchases but also summarizes them according to whatever parameters we set!

Here is a copy of my Google Sheet. Be sure to make a copy so that you can have access to edit it lol. I also changed the name of thedoNotDelete sheet to Metadata. Feel free to change that back if you want.

Also you can play around with the spread sheet and maybe pass a different API?

Happy data gathering!

Heizal Codes