How to Create a Crypto Portfolio Tracker on Google Sheets (Free Template)

Especially in a fast-moving space like crypto, it can be overwhelming to stay on top of your investments 24/7. In this article, we’ll show you how to set up your own real-time portfolio tracker using Google Sheets, so you can manage and track your crypto investments with ease. Using this free Crypto Portfolio Tracker Google Sheets template, powered by the CoinGecko API, you can automatically pull live market data into your spreadsheet without any coding, making it simple to record your holdings, analyze price movements, and tailor the tracker to your trading preferences. Investors who trade stocks and other assets can also integrate this alongside their existing portfolio trackers.

What This Free Template Offers

Live crypto data integration using the CoinGecko Google Sheets add-on.
Portfolio tracking, including holdings, total invested, realized and unrealized PnL, and ROI.
Dynamic dashboards to visualize portfolio value, asset allocation, and performance over time.
Top 1,000 coin coverage, using the =COINGECKO("top:1000") formula to instantly fetch bulk real-time market data directly into Google Sheets.
No scripting required, powered entirely by simple =COINGECKO() formulas.

To get started and follow along with the setup steps, you can make a copy of the free Google Sheets template using the download link at the end of this article.

Gsheets Crypto Portfolio Tracker Crypto Price Tracker | CoinGecko API

Prerequisites

Before you start, ensure the following requirements are met to use the template effectively:

A Google Account:
You’ll need a Google account to open, copy, and use the template in Google Sheets. This allows you to save your own version.
CoinGecko Google Sheets Add-on:
This template uses the official CoinGecko Google Sheets add-on to fetch live and historical crypto prices directly into your spreadsheet using the =COINGECKO() function.
A CoinGecko API Key:
Required to fetch live crypto prices. The free Demo API key is sufficient to get started, while a Pro plan offers higher rate limits for more frequent data refreshes. You can follow our guide to get your free Demo API key.

How to Use the Crypto Portfolio Tracker Google Sheets Template

This section provides a step-by-step guide on how to set up and use the Crypto Portfolio Tracker template effectively. Follow these instructions to connect the CoinGecko add-on, customize your portfolio, and start tracking your holdings and performance in real time.

Setting up the CoinGecko Add-on

First, connect the template to the CoinGecko Google Sheets add-on to enable it within the spreadsheet. To get started, install the add-on and configure your API credentials (Subscription Level and API Key) by following the setup guide.

Once configured, all =COINGECKO() formulas across the template will automatically fetch the required data. You don’t need to modify any formulas or configure scripts manually.

Refreshing Crypto Price Data

Now that the CoinGecko add-on is configured, your data will be fetched automatically through the =COINGECKO() formulas across the template, allowing you to retrieve crypto data directly (e.g., =COINGECKO("id:bitcoin") for individual tokens) without any manual setup. `

However, Google Sheets may cache results for a short period of time. If you need the most up-to-date prices, you can manually refresh all data in your sheet. Keep in mind that more frequent refreshes will consume your API call credits faster. If you run into rate limits, consider upgrading to a paid API plan for higher call credits and rate limits.

Once the add-on status is Live, you can start customising the template.

Logging Your Portfolio Holdings & Transactions

Once your data is connected, you can start tracking your portfolio by recording your transactions in the Buy/Sell sheet. Enter your buy and sell activity by filling in the following details:

Coin Name
Quantity (Purchased / Sold)
Price at Transaction (USD)
Notes

Each buy increases your total holdings and establishes your cost basis, while each sell reduces your holdings and automatically calculates your realized profit or loss. All values update dynamically based on live price data fetched via the =COINGECKO() formulas.

💡 Pro Tip: By default, the template tracks the top 1,000 coins using =COINGECKO("top:1000").= For additional assets, you can fetch prices directly in the Crypto Portfolio sheet using =COINGECKO("id:coin_id") or =COINGECKO("network:token_address").

Analyzing Your Portfolio in the Crypto Portfolio Sheet

Once your transactions are recorded in the Buy/Sell sheet, head to the Crypto Portfolio sheet to view your portfolio performance. This sheet acts as your main dashboard, automatically updating all values based on your transaction history and live market data.

Portfolio Overview

Provides a snapshot of your overall portfolio health. The bar chart visualizes the current holding value of each asset, showing how your capital is distributed across different coins. This helps you quickly identify your largest positions and understand your portfolio allocation at a glance.

Summary Statistics of Holdings

Displays key overall metrics that provide a quick overview of how your portfolio is performing without needing to analyze individual assets.

Individual Holdings Breakdown

The main table provides a detailed breakdown of each asset in your portfolio, combining live market data with your transaction history.

Individual Coin Data
Displays key market information for each asset, such as price, market cap, and trading activity, giving you context on how each coin is performing in the broader market.
Individual Holdings Data
Focuses on your personal portfolio metrics, including holdings, total invested, and profit or loss, allowing you to evaluate your performance for each asset at a glance.

Watchlist Table

Lets you monitor additional assets without adding them to your portfolio. Select coins from the dropdown in Column B (starting from row 26) to track their live prices and market data, helping you keep an eye on potential investments alongside your current holdings.

These dashboards provide both a high-level portfolio overview and in-depth performance insights for each asset, making it easy to track progress and refine your trading approach.

Creating a Crypto Portfolio Tracker in Google Sheets

Creating a crypto portfolio tracker in Google Sheets involves three main components:
a live data source for fetching crypto prices, a structured log for recording transactions, and a dashboard for analyzing portfolio performance.

This template integrates all three seamlessly into one connected system powered by the CoinGecko Google Sheets add-on, allowing you to fetch live market data directly using simple =COINGECKO() formulas:

Live Crypto Market Data
Live prices and market data are fetched directly using the CoinGecko Google Sheets add-on. By using the =COINGECKO() function (e.g., =COINGECKO("top:1000")), the template retrieves real-time data for a wide range of crypto assets without requiring any custom scripts or manual setup.
Google Sheets Formulas and Tables
Use built-in Sheets formulas to calculate all key portfolio metrics such as current holding value, total invested, realized and unrealized PnL, and overall portfolio value. All calculations update dynamically whenever new transactions are logged or when market data refreshes. .
Structured Transaction Logging
A dedicated Buy/Sell sheet lets you record every transaction with clear fields for coin name, quantity, price, and notes. This structured log forms the foundation for all calculations and portfolio tracking.
Interactive Dashboards
Use charts and summary tables in the Crypto Portfolio sheet to visualize your holdings, asset allocation, and overall portfolio performance for quick insights.

How to Get Live Crypto Data in Google Sheets

You can get real-time crypto data in Google Sheets using two different approaches, depending on your preferred level of control and customization.

Option 1 (Recommended): Using the CoinGecko Add-on

The simplest way is to use the official CoinGecko Google Sheets add-on with the =COINGECKO() function. This method is fully integrated into the template and allows you to fetch live crypto prices directly, without any coding or manual setup.

Option 2 (Advanced): Using Apps Script

For users who require more flexibility, you can fetch data directly from the CoinGecko API using Google Apps Script. This approach gives you fine-grained control over the request parameters, such as specifying dates, currencies, and endpoints, as well as how the returned data is structured and used within your sheet. This involves creating custom functions (such as ImportJSON) to call specific API endpoints and retrieve market data into your sheet.

Step 1: Create an ImportJSON Script

First, create the main function that will fetch and parse the data.

Open your Google Sheet and navigate to ‘Extensions’ and select ‘Apps Script’ - a new tab will appear.

On the left panel, select ‘< > Editor’ and add a new script using the ‘+’ button. Copy and paste the following importJSON script, and save the script as ‘ImportJSON’. This importJSON script is a versatile one that will allow you to import data in many different ways.

/*====================================================================================================================================* ImportJSON by Brad Jasper and Trevor Lohrbeer ==================================================================================================================================== Version: 1.5.0 Project Page: Copyright: (c) 2017-2019 by Brad Jasper (c) 2012-2017 by Trevor Lohrbeer License: GNU General Public License, version 3 (GPL-3.0) http://www.opensource.org/licenses/gpl-3.0.html ------------------------------------------------------------------------------------------------------------------------------------ A library for importing JSON feeds into Google spreadsheets. Functions include: ImportJSON For use by end users to import a JSON feed from a URL ImportJSONFromSheet For use by end users to import JSON from one of the Sheets ImportJSONViaPost For use by end users to import a JSON feed from a URL using POST parameters ImportJSONAdvanced For use by script developers to easily extend the functionality of this library ImportJSONBasicAuth For use by end users to import a JSON feed from a URL with HTTP Basic Auth (added by Karsten Lettow) For future enhancements see For bug reports see ------------------------------------------------------------------------------------------------------------------------------------ Changelog: 1.6.0 (June 2, 2019) Fixed null values (thanks @gdesmedt1) 1.5.0 (January 11, 2019) Adds ability to include all headers in a fixed order even when no data is present for a given header in some or all rows. 1.4.0 (July 23, 2017) Transfer project to Brad Jasper. Fixed off-by-one array bug. Fixed previous value bug. Added custom annotations. Added ImportJSONFromSheet and ImportJSONBasicAuth. 1.3.0 Adds ability to import the text from a set of rows containing the text to parse. All cells are concatenated 1.2.1 Fixed a bug with how nested arrays are handled. The rowIndex counter wasn't incrementing properly when parsing. 1.2.0 Added ImportJSONViaPost and support for fetchOptions to ImportJSONAdvanced 1.1.1 Added a version number using Google Scripts Versioning so other developers can use the library 1.1.0 Added support for the noHeaders option 1.0.0 Initial release *====================================================================================================================================*/ /** * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in * the JSON feed. The remaining rows contain the data. * * By default, data gets transformed so it looks more like a normal data import. Specifically: * * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values * of the rows representing their parent elements. * - Values longer than 256 characters get truncated. * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case. * * To change this behavior, pass in one of these values in the options parameter: * * noInherit: Don't inherit values from parent elements * noTruncate: Don't truncate values * rawHeaders: Don't prettify headers * noHeaders: Don't include headers, only the data * allHeaders: Include all headers from the query parameter in the order they are listed * debugLocation: Prepend each value with the row & column it belongs in * * For example: * * =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content", * "noInherit,noTruncate,rawHeaders") * * @param {url} the URL to a public JSON feed * @param {query} a comma-separated list of paths to import. Any path starting with one of these paths gets imported. * @param {parseOptions} a comma-separated list of options that alter processing of the data * @customfunction * * @return a two-dimensional array containing the data, with the first row containing headers **/ function ImportJSON(url, query, parseOptions) { return ImportJSONAdvanced(url, null, query, parseOptions, includeXPath_, defaultTransform_); } /** * Imports a JSON feed via a POST request and returns the results to be inserted into a Google Spreadsheet. The JSON feed is * flattened to create a two-dimensional array. The first row contains the headers, with each column header indicating the path to * that data in the JSON feed. The remaining rows contain the data. * * To retrieve the JSON, a POST request is sent to the URL and the payload is passed as the content of the request using the content * type "application/x-www-form-urlencoded". If the fetchOptions define a value for "method", "payload" or "contentType", these * values will take precedent. For example, advanced users can use this to make this function pass XML as the payload using a GET * request and a content type of "application/xml; charset=utf-8". For more information on the available fetch options, see * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app . At this time the "headers" option is not supported. * * By default, the returned data gets transformed so it looks more like a normal data import. Specifically: * * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values * of the rows representing their parent elements. * - Values longer than 256 characters get truncated. * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case. * * To change this behavior, pass in one of these values in the options parameter: * * noInherit: Don't inherit values from parent elements * noTruncate: Don't truncate values * rawHeaders: Don't prettify headers * noHeaders: Don't include headers, only the data * allHeaders: Include all headers from the query parameter in the order they are listed * debugLocation: Prepend each value with the row & column it belongs in * * For example: * * =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "user=bob&apikey=xxxx", * "validateHttpsCertificates=false", "/feed/entry/title,/feed/entry/content", "noInherit,noTruncate,rawHeaders") * * @param {url} the URL to a public JSON feed * @param {payload} the content to pass with the POST request; usually a URL encoded list of parameters separated by ampersands * @param {fetchOptions} a comma-separated list of options used to retrieve the JSON feed from the URL * @param {query} a comma-separated list of paths to import. Any path starting with one of these paths gets imported. * @param {parseOptions} a comma-separated list of options that alter processing of the data * @customfunction * * @return a two-dimensional array containing the data, with the first row containing headers **/ function ImportJSONViaPost(url, payload, fetchOptions, query, parseOptions) { var postOptions = parseToObject_(fetchOptions); if (postOptions["method"] == null) { postOptions["method"] = "POST"; } if (postOptions["payload"] == null) { postOptions["payload"] = payload; } if (postOptions["contentType"] == null) { postOptions["contentType"] = "application/x-www-form-urlencoded"; } convertToBool_(postOptions, "validateHttpsCertificates"); convertToBool_(postOptions, "useIntranet"); convertToBool_(postOptions, "followRedirects"); convertToBool_(postOptions, "muteHttpExceptions"); return ImportJSONAdvanced(url, postOptions, query, parseOptions, includeXPath_, defaultTransform_); } /** * Imports a JSON text from a named Sheet and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in * the JSON feed. The remaining rows contain the data. * * By default, data gets transformed so it looks more like a normal data import. Specifically: * * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values * of the rows representing their parent elements. * - Values longer than 256 characters get truncated. * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case. * * To change this behavior, pass in one of these values in the options parameter: * * noInherit: Don't inherit values from parent elements * noTruncate: Don't truncate values * rawHeaders: Don't prettify headers * noHeaders: Don't include headers, only the data * allHeaders: Include all headers from the query parameter in the order they are listed * debugLocation: Prepend each value with the row & column it belongs in * * For example: * * =ImportJSONFromSheet("Source", "/feed/entry/title,/feed/entry/content", * "noInherit,noTruncate,rawHeaders") * * @param {sheetName} the name of the sheet containg the text for the JSON * @param {query} a comma-separated lists of paths to import. Any path starting with one of these paths gets imported. * @param {options} a comma-separated list of options that alter processing of the data * * @return a two-dimensional array containing the data, with the first row containing headers * @customfunction **/ function ImportJSONFromSheet(sheetName, query, options) { var object = getDataFromNamedSheet_(sheetName); return parseJSONObject_(object, query, options, includeXPath_, defaultTransform_); } /** * An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a * spreadsheet. * * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in * the JSON feed. The remaining rows contain the data. * * The fetchOptions can be used to change how the JSON feed is retrieved. For instance, the "method" and "payload" options can be * set to pass a POST request with post parameters. For more information on the available parameters, see * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app . * * Use the include and transformation functions to determine what to include in the import and how to transform the data after it is * imported. * * For example: * * ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", * new Object() { "method" : "post", "payload" : "user=bob&apikey=xxxx" }, * "/feed/entry", * "", * function (query, path) { return path.indexOf(query) == 0; }, * function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } ) * * In this example, the import function checks to see if the path to the data being imported starts with the query. The transform * function takes the data and truncates it. For more robust versions of these functions, see the internal code of this library. * * @param {url} the URL to a public JSON feed * @param {fetchOptions} an object whose properties are options used to retrieve the JSON feed from the URL * @param {query} the query passed to the include function * @param {parseOptions} a comma-separated list of options that may alter processing of the data * @param {includeFunc} a function with the signature func(query, path, options) that returns true if the data element at the given path * should be included or false otherwise. * @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data * and row & column are the current row and column being processed. Any return value is ignored. Note that row 0 * contains the headers for the data, so test for row==0 to process headers only. * * @return a two-dimensional array containing the data, with the first row containing headers * @customfunction **/ function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) { var jsondata = UrlFetchApp.fetch(url, fetchOptions); var object = JSON.parse(jsondata.getContentText()); return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc); } /** * Helper function to authenticate with basic auth informations using ImportJSONAdvanced * * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in * the JSON feed. The remaining rows contain the data. * * The fetchOptions can be used to change how the JSON feed is retrieved. For instance, the "method" and "payload" options can be * set to pass a POST request with post parameters. For more information on the available parameters, see * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app . * * Use the include and transformation functions to determine what to include in the import and how to transform the data after it is * imported. * * @param {url} the URL to a http basic auth protected JSON feed * @param {username} the Username for authentication * @param {password} the Password for authentication * @param {query} the query passed to the include function (optional) * @param {parseOptions} a comma-separated list of options that may alter processing of the data (optional) * * @return a two-dimensional array containing the data, with the first row containing headers * @customfunction **/ function ImportJSONBasicAuth(url, username, password, query, parseOptions) { var encodedAuthInformation = Utilities.base64Encode(username + ":" + password); var header = {headers: {Authorization: "Basic " + encodedAuthInformation}}; return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_); } /** * Encodes the given value to use within a URL. * * @param {value} the value to be encoded * * @return the value encoded using URL percent-encoding */ function URLEncode(value) { return encodeURIComponent(value.toString()); } /** * Adds an oAuth service using the given name and the list of properties. * * @note This method is an experiment in trying to figure out how to add an oAuth service without having to specify it on each * ImportJSON call. The idea was to call this method in the first cell of a spreadsheet, and then use ImportJSON in other * cells. This didn't work, but leaving this in here for further experimentation later. * * The test I did was to add the following into the A1: * * =AddOAuthService("twitter", "https://api.twitter.com/oauth/access_token", * "https://api.twitter.com/oauth/request_token", "https://api.twitter.com/oauth/authorize", * "<my consumer key>", "<my consumer secret>", "", "") * * Information on obtaining a consumer key & secret for Twitter can be found at https://dev.twitter.com/docs/auth/using-oauth * * Then I added the following into A2: * * =ImportJSONViaPost("https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=fastfedora&count=2", "", * "oAuthServiceName=twitter,oAuthUseToken=always", "/", "") * * I received an error that the "oAuthServiceName" was not a valid value. [twl 18.Apr.13] */ function AddOAuthService__(name, accessTokenUrl, requestTokenUrl, authorizationUrl, consumerKey, consumerSecret, method, paramLocation) { var oAuthConfig = UrlFetchApp.addOAuthService(name); if (accessTokenUrl != null && accessTokenUrl.length > 0) { oAuthConfig.setAccessTokenUrl(accessTokenUrl); } if (requestTokenUrl != null && requestTokenUrl.length > 0) { oAuthConfig.setRequestTokenUrl(requestTokenUrl); } if (authorizationUrl != null && authorizationUrl.length > 0) { oAuthConfig.setAuthorizationUrl(authorizationUrl); } if (consumerKey != null && consumerKey.length > 0) { oAuthConfig.setConsumerKey(consumerKey); } if (consumerSecret != null && consumerSecret.length > 0) { oAuthConfig.setConsumerSecret(consumerSecret); } if (method != null && method.length > 0) { oAuthConfig.setMethod(method); } if (paramLocation != null && paramLocation.length > 0) { oAuthConfig.setParamLocation(paramLocation); } } /** * Parses a JSON object and returns a two-dimensional array containing the data of that object. */ function parseJSONObject_(object, query, options, includeFunc, transformFunc) { var headers = new Array(); var data = new Array(); if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) { query = query.toString().split(","); } // Prepopulate the headers to lock in their order if (hasOption_(options, "allHeaders") && Array.isArray(query)) { for (var i = 0; i < query.length; i++) { headers[query[i]] = Object.keys(headers).length; } } if (options) { options = options.toString().split(","); } parseData_(headers, data, "", {rowIndex: 1}, object, query, options, includeFunc); parseHeaders_(headers, data); transformData_(data, options, transformFunc); return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data; } /** * Parses the data contained within the given value and inserts it into the data two-dimensional array starting at the rowIndex. * If the data is to be inserted into a new column, a new header is added to the headers array. The value can be an object, * array or scalar value. * * If the value is an object, it's properties are iterated through and passed back into this function with the name of each * property extending the path. For instance, if the object contains the property "entry" and the path passed in was "/feed", * this function is called with the value of the entry property and the path "/feed/entry". * * If the value is an array containing other arrays or objects, each element in the array is passed into this function with * the rowIndex incremeneted for each element. * * If the value is an array containing only scalar values, those values are joined together and inserted into the data array as * a single value. * * If the value is a scalar, the value is inserted directly into the data array. */ function parseData_(headers, data, path, state, value, query, options, includeFunc) { var dataInserted = false; if (Array.isArray(value) && isObjectArray_(value)) { for (var i = 0; i < value.length; i++) { if (parseData_(headers, data, path, state, value[i], query, options, includeFunc)) { dataInserted = true; if (data[state.rowIndex]) { state.rowIndex++; } } } } else if (isObject_(value)) { for (key in value) { if (parseData_(headers, data, path + "/" + key, state, value[key], query, options, includeFunc)) { dataInserted = true; } } } else if (!includeFunc || includeFunc(query, path, options)) { // Handle arrays containing only scalar values if (Array.isArray(value)) { value = value.join(); } // Insert new row if one doesn't already exist if (!data[state.rowIndex]) { data[state.rowIndex] = new Array(); } // Add a new header if one doesn't exist if (!headers[path] && headers[path] != 0) { headers[path] = Object.keys(headers).length; } // Insert the data data[state.rowIndex][headers[path]] = value; dataInserted = true; } return dataInserted; } /** * Parses the headers array and inserts it into the first row of the data array. */ function parseHeaders_(headers, data) { data[0] = new Array(); for (key in headers) { data[0][headers[key]] = key; } } /** * Applies the transform function for each element in the data array, going through each column of each row. */ function transformData_(data, options, transformFunc) { for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[0].length; j++) { transformFunc(data, i, j, options); } } } /** * Returns true if the given test value is an object; false otherwise. */ function isObject_(test) { return Object.prototype.toString.call(test) === '[object Object]'; } /** * Returns true if the given test value is an array containing at least one object; false otherwise. */ function isObjectArray_(test) { for (var i = 0; i < test.length; i++) { if (isObject_(test[i])) { return true; } } return false; } /** * Returns true if the given query applies to the given path. */ function includeXPath_(query, path, options) { if (!query) { return true; } else if (Array.isArray(query)) { for (var i = 0; i < query.length; i++) { if (applyXPathRule_(query[i], path, options)) { return true; } } } else { return applyXPathRule_(query, path, options); } return false; }; /** * Returns true if the rule applies to the given path. */ function applyXPathRule_(rule, path, options) { return path.indexOf(rule) == 0; } /** * By default, this function transforms the value at the given row & column so it looks more like a normal data import. Specifically: * * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values * of the rows representing their parent elements. * - Values longer than 256 characters get truncated. * - Values in row 0 (headers) have slashes converted to spaces, common prefixes removed and the resulting text converted to title * case. * * To change this behavior, pass in one of these values in the options parameter: * * noInherit: Don't inherit values from parent elements * noTruncate: Don't truncate values * rawHeaders: Don't prettify headers * debugLocation: Prepend each value with the row & column it belongs in */ function defaultTransform_(data, row, column, options) { if (data[row][column] == null) { if (row < 2 || hasOption_(options, "noInherit")) { data[row][column] = ""; } else { data[row][column] = data[row-1][column]; } } if (!hasOption_(options, "rawHeaders") && row == 0) { if (column == 0 && data[row].length > 1) { removeCommonPrefixes_(data, row); } data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " ")); } if (!hasOption_(options, "noTruncate") && data[row][column]) { data[row][column] = data[row][column].toString().substr(0, 256); } if (hasOption_(options, "debugLocation")) { data[row][column] = "[" + row + "," + column + "]" + data[row][column]; } } /** * If all the values in the given row share the same prefix, remove that prefix. */ function removeCommonPrefixes_(data, row) { var matchIndex = data[row][0].length; for (var i = 1; i < data[row].length; i++) { matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex); if (matchIndex == 0) { return; } } for (var i = 0; i < data[row].length; i++) { data[row][i] = data[row][i].substring(matchIndex, data[row][i].length); } } /** * Locates the index where the two strings values stop being equal, stopping automatically at the stopAt index. */ function findEqualityEndpoint_(string1, string2, stopAt) { if (!string1 || !string2) { return -1; } var maxEndpoint = Math.min(stopAt, string1.length, string2.length); for (var i = 0; i < maxEndpoint; i++) { if (string1.charAt(i) != string2.charAt(i)) { return i; } } return maxEndpoint; } /** * Converts the text to title case. */ function toTitleCase_(text) { if (text == null) { return null; } return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); }); } /** * Returns true if the given set of options contains the given option. */ function hasOption_(options, option) { return options && options.indexOf(option) >= 0; } /** * Parses the given string into an object, trimming any leading or trailing spaces from the keys. */ function parseToObject_(text) { var map = new Object(); var entries = (text != null && text.trim().length > 0) ? text.toString().split(",") : new Array(); for (var i = 0; i < entries.length; i++) { addToMap_(map, entries[i]); } return map; } /** * Parses the given entry and adds it to the given map, trimming any leading or trailing spaces from the key. */ function addToMap_(map, entry) { var equalsIndex = entry.indexOf("="); var key = (equalsIndex != -1) ? entry.substring(0, equalsIndex) : entry; var value = (key.length + 1 < entry.length) ? entry.substring(key.length + 1) : ""; map[key.trim()] = value; } /** * Returns the given value as a boolean. */ function toBool_(value) { return value == null ? false : (value.toString().toLowerCase() == "true" ? true : false); } /** * Converts the value for the given key in the given map to a bool. */ function convertToBool_(map, key) { if (map[key] != null) { map[key] = toBool_(map[key]); } } function getDataFromNamedSheet_(sheetName) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var source = ss.getSheetByName(sheetName); var jsonRange = source.getRange(1,1,source.getLastRow()); var jsonValues = jsonRange.getValues(); var jsonText = ""; for (var row in jsonValues) { for (var col in jsonValues[row]) { jsonText +=jsonValues[row][col]; } } Logger.log(jsonText); return JSON.parse(jsonText); }

Step 2: Create the autoRefresh Script

Next, create a helper script that will allow the data to refresh automatically.

Create a second Apps Script by clicking on the ‘+’ button. Copy the code below and paste it into the script editor, saving it as ‘autoRefresh’ – this will allow your sheet to automatically refresh at fixed intervals.

/** * Time-driven trigger: runs every X minutes and bumps Read Me!A25, * which you can reference in ImportJSON formulas as a recalc trigger. */ function triggerAutoRefresh() { SpreadsheetApp.getActive() .getSheetByName('Read Me') .getRange('A25') .setValue(getRandomInt(1, 1e6)); } function getRandomInt(min, max) { min = Math.ceil(min); max = Math.floor(max); return Math.floor(Math.random() * (max - min + 1)) + min; }

Step 3: Call the Function in Your Sheet

With the scripts saved, you can now use the =IMPORTJSON() function in any cell:

Construct your CoinGecko API request URL.
Enter the formula into your desired cell, replacing the placeholder URL with your own.

For example, to get market data for the top 100 coins, your formula would look like this:

=ImportJSON("https://api.coingecko.com/api/v3/coins/marketsvs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=24h&x_cg_demo_api_key=" & 'Read Me'!$B$20, "/id,/name,/current_price,/market_cap,/market_cap_rank,/price_change_percentage_24h,/market_cap_change_24h,/market_cap_change_percentage_24h,/price_change_percentage_24h_in_currency", "noTruncate," & 'Read Me'!$A$25)

This method is modular and powerful, as the =IMPORTJSON() formula can be easily replicated across any cell with different API endpoints. For a more detailed step-by-step guide, read our article on importing crypto prices into Google Sheets.

💡 Pro Tip: If you want full control over the API requests and response via a no-code alternative, you can learn how use a visual interface to pull crypto data into Google Sheets via the API Connector extension.

The crypto trading journal Google Sheets template is powered by a combination of real-time data from the /coins/markets endpoint (Fetches the latest prices and market caps of the top 250 coins).

Creating a Crypto Transaction Log Sheet in Google Sheets

To calculate your portfolio performance, you first need a record of your transactions. The Buy/Sell sheet allows you to log all buy and sell activity in one place, which forms the foundation for all portfolio calculations.

Each log entry includes:

Coin Name
Quantity (Purchased / Sold)
Price at Transaction (USD)
Notes

This transaction log is used to track your holdings, cost basis, and realized profits or losses over time.

Creating a Portfolio Dashboard in Google Sheets

Once your transactions are recorded, the next step is to analyze your portfolio using a dashboard. The Crypto Portfolio sheet uses formulas to calculate key metrics and presents them in a structured table and visual charts for a clear overview of your portfolio.

To power the portfolio tracker, the template uses a combination of built-in Google Sheets functions:

Essentials Google Sheets Functions

SUMIFS: Calculates totals based on specific conditions, such as the total position value for a specific coin or your total realized profit and loss.
AVERAGEIFS: Calculates the average buy price for each asset
INDEX + MATCH: Used to dynamically retrieve live price data for each coin using the CoinGecko dataset.
FILTER + IF: Used to conditionally displays relevant data based on conditions (e.g., active holdings).

Calculation Logic
With the functions above, you can compute the primary metrics needed to assess your performance:

Realised PnL: The difference between the cost of sold quantities and total sale proceeds, based on matching coin names in the Buy/Sell sheet.
Unrealised PnL: The difference between your current holding value and total invested amount for assets still held.
Portfolio Value: The current value is a sum of your cash balance (from realized profits) and the market value of all your open positions.
ROI (%): Measures your overall return by comparing your total profit (realised and unrealised) against your total invested amount.

These metrics can then be used to power dashboard charts and tables, giving you an immediate visual summary of your trading performance and portfolio health.

Further Enhancements

You can further enhance this Crypto Portfolio Tracker Google Sheets template to better suit your analysis style. Here are a few ideas that leverage other CoinGecko API endpoints:

Fetch Historical Price Data
Pull Historical Prices for specific coins for more in-depth performance analysis. You can follow our guide on how to pull crypto historical data in Google Sheets and visualize long-term trends.
Add OHLC (Open, High, Low, Close) Data
If you want more granular price insights, consider fetching OHLC data for each coin. Check out our step-by-step guide on how to pull crypto OHLC data into Google Sheets.
Build a Crypto Tax Calculator
Since staking rewards can be taxable events, keeping a clean record with this template is a great first step. You can also use the data from your staking log to calculate capital gains using a dedicated crypto tax calculator. For a complete guide, see our article on how to create a crypto tax calculator in Google Sheets.

Common Issues & Fixes

If you encounter errors while using the portfolio tracker and are unsure what’s causing the issue, navigate to ‘Extensions’ > ‘CoinGecko’ > ‘View Error Logs’ to access the debug logs and view the returned error code and message.

Once you’ve identified the issue, here are a few common problems along with quick ways to fix them.

API Rate Limit (Error 429)
This happens when the free CoinGecko Demo API hits its request limit. Wait a few minutes before refreshing again, or switch to a Pro API key for uninterrupted updates. If you receive other error codes, refer to the full list of CoinGecko API status codes to troubleshoot them.
API Key or Endpoint Issues
If your data isn’t loading or you’re seeing errors such as 401 or 403, try reconnecting your CoinGecko API key within the add-on settings and ensure your subscription level is correctly configured.
Data Not Updating or Showing Older Prices
Google Sheets may cache formula results temporarily. To refresh your data, open the CoinGecko sidebar and click “Refresh All Data” to force-update all =COINGECKO() formulas.

For more detailed troubleshooting steps and a list of common errors, refer to the Error Debugging Guide.

Conclusion

A crypto portfolio tracker is one of the most effective tools for monitoring your investments and making informed decisions. This free Crypto Portfolio Tracker Google Sheets template, powered by the CoinGecko Google Sheets add-on and CoinGecko API, provides a simple yet powerful way to track your holdings, calculate portfolio value, and analyze your performance in real time. Because it is built in Google Sheets, your portfolio remains accessible across all your devices, allowing you to stay on top of your investments anytime, anywhere.

The integrated dashboard gives you a clear overview of your portfolio allocation and performance, while the transaction-based tracking ensures your holdings, PnL, and ROI are always up to date based on your latest activity.

If you require more frequent data updates or higher API rate limits for advanced analysis, consider subscribing to a paid API plan to unlock the full potential of your trading journal.

Download Your Free Template ⬇️

Credits & Acknowledgements

importJSON script by Brad Jasper and Trevor (Github)
triggerAutoRefresh script by Andrea Borruso (Github)

If you found this helpful, you might like to check out an alternative guide that walks through how to use an API connector when creating your portfolio on Google Sheets, or explore our library of crypto spreadsheet templates.