Crypto Portfolio Tracker on Google Sheets Using API Connector (Free Template)

With crypto prices moving 24/7, it's critical to have a reliable, accurate view of your investments. For a ready-made solution, you can use CoinGecko's built-in portfolio tracker. However, for more custom functionality, you may prefer fetching data into Google Sheets, where you can create your own custom dashboards and visualizations. 

There are several different ways to get your data into Google Sheets. If you're technical, you may want to use the open-source importJSON App Script that hooks into the CoinGecko API. This method requires a bit more coding than what we'll cover in this article.

If you're not comfortable with coding, this article provides an alternative API-based solution using the Google Sheets extension API Connector by Mixed Analytics. Like the importJSON method, API Connector fetches data from the CoinGecko API, but it provides a simplified user interface and built-in access to most of CoinGecko's data endpoints.

This guide will walk through:

• How to install API Connector
• How to get a CoinGecko API key
• How to import live crypto price data into Google Sheets
• How to handle pagination
• How to create a custom crypto portfolio tracker

⭐ If you prefer to use a pre-configured template, skip ahead to the last section to download the free Google Sheets template.

Let's jump in!

Step 1: Install API Connector

Install the API Connector add-on from the Google Marketplace. Once it's installed, it will be available in the Google Sheets extension menu.

The API Connector can be used for free but has some paid features to make workflows more efficient. While paid features (e.g. scheduling) are mentioned in this guide, they are not required to create a portfolio tracker.

Step 2: Get Your CoinGecko API Key

1. To get started, you'll need a CoinGecko account, so, if you haven't already, create an account and log in.
   
   

2. Once you're logged in, navigate to CoinGecko API's pricing page.

3. To subscribe to a paid API plan, click one of the Upgrade buttons in the pricing table. Alternatively, get a free API key by clicking Create Demo Account underneath the pricing table.
   
   

4. If you select a paid plan, you'll see the Billing Info form on the left. If you are creating a free demo account, you'll see the form on the right.
   
   

5. Either way, once your account is set up, navigate to the Developer Dashboard and click '+ Add New Key'.
   
   

6. You'll be prompted to label your key and click 'Create'.
   
   

7. Your API key will now be listed on the page. Copy this key and keep it safe as we'll use it shortly!
   
   

Step 3: Pull CoinGecko API Data into Google Sheets

While you can create your own custom API requests to any of CoinGecko's API endpoints, to make things easy we'll use API Connector's built-in integration to CoinGecko.

1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request).

2. If you're using a free API key through a Demo Account, select 'CoinGecko' from the drop-down list of applications. If you are using a paid CoinGecko API plan, select 'CoinGecko Pro'. The CoinGecko Pro API contains some unique endpoints that aren't accessible to free users.
   
   

3. Under Authorization, enter your API key.
   
   

4. Choose an endpoint. For this example, we'll select /coins/markets, which is the endpoint for fetching the latest market data.
   
   

5. In the parameters section, select which vs_currency you'd like to use, which is a required query param.

6. Optionally select other parameters. By default, each "page" contains data for 100 coins, so set the per_page parameter to 250 to get more (and see the section on pagination for getting more than 250). 

7. Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
   

Step 4: Handling Pagination

For several endpoints, CoinGecko limits the number of records returned in each response. By default, only 100 records will be returned unless you set the per_page parameter to 250:

To get more than 250 records, you will need to fetch multiple pages of data. In API Connector, you can loop through multiple pages automatically with page parameter pagination handling, like this:

• Pagination type: page parameter
• Page parameter: page
• Run until: choose when to stop running the request

Step 5: Configure Your Crypto Portfolio Tracker

Now that you've created the basic request, let's configure it into a portfolio tracker based on your own crypto holdings.

1. Open your earlier request to the /coins/markets endpoint

2. By default, this endpoint returns data for a set of the top cryptocurrencies. However, for your own portfolio tracker, you'll likely want to retrieve the set of currencies that you're holding or watching. To do that, select or enter your currencies of interest into the ids parameter (to see a full list of available ids, run a request to the /coins/list endpoint).
   
   

3. Click Edit fields to open the field editor.
   
   

4. You can now re-arrange the field order, rename your fields, and filter out any fields you're not interested in. Click Save fields when you're done.
   
   

5. Now that we have our report, let's add some new fields to track your own holdings. The formulas provided below are array formulas so they only need to be entered once and will apply to the entire column.

   1. Current Holdings: enter the quantities you hold of each crypto

   2. Current Holdings Value: enter =arrayformula(if(A2:A<>"",S2:S*C2:C,"")). Change S to the column holding "Current Holdings",  and C to the column holding the current price

   3. Total Invested: enter the amounts you initially spent to acquire each crypto

   4. ROI: enter =arrayformula(if(A2:A<>"",(T2:T-U2:U)/U2:U,"")). Change T to the "Current Holdings Value" column, and U to the "Total Invested" column
      
      

6. To prevent these calculated fields from being overwritten when the data refreshes, untick the 'Clear sheet data' field located under 'Output options'.
   
   

7. (Optional) To add a new custom header row separating your portfolio data from market data, right-click row 1 in Sheets and click "Insert 1 row above". In API Connector, change your data destination from A1 to A2, since we'll now be starting the data pull from the second row.

8. That's it! The tracker will be refreshed every time you hit Run. Alternatively, you can set up scheduling (paid feature) to run the report automatically in the background, or use the IMPORTAPI function to run the report with the click of a button. The final report will look like this:

Free Downloadable Template: Crypto Portfolio Tracker on Google Sheets

This portfolio tracker is already configured so you just need to input your own Demo or Pro API key and list of coins to return a template that looks like this:

Looking to import crypto prices into Excel? Visit this in-depth guide that walks through how to fetch live crypto prices in Excel for trading pairs, historical prices for specific cryptocurrencies, fetch total crypto market cap data into Excel and more!