Crypto Trading Journal in Google Sheets (Free Template)

A crypto trading journal is essential for any serious trader or investor. It helps you stay organized, learn from past trades, and refine your strategy. One of the best ways to maintain one is using Google Sheets, as it's easily accessible and customizable. This free crypto trading journal Google Sheets template is designed to help you get started. It automatically tracks your trades, calculates profit and loss (PnL), and analyzes your performance in real-timeusing live crypto data powered by the CoinGecko API. Whether you’re trading Bitcoin, stablecoins, or on-chain tokens, the template gives you a clean, visual way to measure your trading results, identify patterns, and refine your strategy.

What This Free Template Offers

• Real-time data updates powered by the CoinGecko API and Apps Script.

• PnL tracking, showing both realized and unrealized performance.

• Dynamic dashboards to monitor your portfolio growth and win rate.

• Extensive token support, covering over 17 million+ tokens from 1,700+ exchanges and 250+ networks via CoinGecko and GeckoTerminal data integration.

• No add-ons required, as the template is powered by lightweight, built-in Google Apps Script.

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.

Prerequisites

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

• A CoinGecko API key:
  A CoinGecko API key is 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.

• 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 and enable Apps Script for live data connections.

How to Use the Crypto Trading Journal Google Sheets Template

This section provides a step-by-step guide on how to set up the template. Follow these instructions to add your CoinGecko API key, customize your coin list, set up automatic data refreshes, and log your first trade.

Setting up the CoinGecko API Key in Google Sheets

First, connect the template to your CoinGecko API key. To do this, navigate to the “Read Me” sheet, this is your configuration page for API settings.

1. Open the “Read Me” sheet.

2. In cell A20, specify your subscription level as Demo or Pro.

3. In cell B20, paste your CoinGecko API key.

Once entered, the key is automatically recognized by all live queries across your workbook. You don’t need to modify any formulas manually.

Setting up Automated Data Refreshes

Now that the API credentials are added to the spreadsheet, you can configure it to refresh the data automatically. Go to Extensions > Apps Script, then click the Triggers icon (shaped like a clock) on the left sidebar.

Clicking on “+ Add Trigger” will open up a modal for you to configure the following settings:

• Choose which function to run: triggerAutoRefresh

• Choose which deployment should run: Head

• Select event source: Time-driven

• Select type of time based trigger: Minutes timer

• Select minute interval: Every 5 or 10 minutes (Note: anything less than this may not be useful, as results are cached)

Depending on your preferred frequency, you may also toggle between Hour timer, Day timer, or Week timer.

After saving, the script will run automatically at your specified interval, keeping your crypto data up-to-date. 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.

Adding Coins to the Template

The template tracks the top 250 coins by default. To track other assets, such as specific on-chain tokens, you must add them manually in the Add Coins sheet. The information required depends on whether the asset is listed on CoinGecko or is an on-chain token tracked on GeckoTerminal.

• For Coins found on CoinGecko:
  Set the Source to CoinGecko and enter its API ID. You can find the API ID on any individual coin page on the CoinGecko website.
  

• For Onchain Tokens:
  Set the Source to GeckoTerminal, select the Network ID (for example, bsc  for BNB Chain), and enter the Token Contract Address. To find the Network ID and Token Contract Address, head to the individual token page on GeckoTerminal:

  • The Network ID is found in the URL path (e.g., bsc in https://www.geckoterminal.com/bsc/pools/0xaead6bd31dd66eb3a6216aaf271d0e661585b0b1)

  • Token Contract Address for the base token is listed in the pool details.
    

After adding assets, the template automatically runs the data fetch using your configured API key.

You can manually refresh the data by reloading the sheet or pressing Ctrl + R to trigger a new API call through ImportJSON.

Your newly added assets will appear in the “Available Coins” sheet, starting from row 252, directly below the default Top 250 list. Once added, you only need to register a coin once for it to remain available for logging in your Trading Journal.

If a coin doesn’t appear, verify Source, CoinGecko ID, Network ID, and Token Contract Address, then refresh.

Logging Your Trades in the Trading Journal Sheet

Once your coins are added, you can start recording your trades in the Trading Journal Sheet. This is where you log every buy or sell to track your performance and portfolio value accurately.

To customize the dropdown options for trading categories, navigate to column “AC” in the Trading Journal sheet and edit the reference list. Using categories helps you analyze which trading strategies (e.g., 'Scalping', 'Swing Trade', 'HODL') are most profitable over time.

Analyzing Trades in the Dashboard

Once your trades are logged, head to the Portfolio Dashboard and Asset Analysis sheets to review your performance. All charts and summary tables automatically update whenever new trades are added or when live market data refreshes. The dashboards will automatically recalculate and display your most recent PnL, portfolio distribution, and trading performance metrics.

Portfolio Dashboard Sheet

This sheet provides a comprehensive overview of your trading portfolio and is split into two sections:

1. Portfolio Overview: Provides a snapshot of your overall portfolio health. It combines your realized profits, open position values, and asset allocations to show how your balance changes over time and where your capital is distributed across different coins.

   • Balance Overview: Portfolio Trend based on Realised PnL and Position values.
     

   • Current Holding’s Distribution: Portfolio distribution based on open positions’ values in USD.
     

   • Traded Coins Analytics: A summary table listing all open and closed positions. Each coin is sorted by name with details such as position  value, position size, total trades, realized and unrealized PnL, and current trade status.
     

2. Performance Insights: Highlights overall trading performance.

   • A summary table showing total trades, win rate, average gain, average loss, and maximum drawdown.
     

   • Balance Breakdown: Displays how each coin has contributed to your portfolio through realized profits and open position values.
     

   • Trades Distribution: Shows the frequency of trades by category, helping you understand which trading styles you use most often.
     

Asset Analysis Sheet

This sheet allows you to perform a deep-dive analysis on a single asset. In cell “A2”, select a coin from the dropdown menu to populate the dashboard with its specific performance data.

1. Individual Asset Analysis
   A breakdown on your performance for a specified asset.

   • Total PnL: Combines realized and unrealized profit or loss for the selected coin. It reflects your overall performance from all trades involving that asset.
     

   • Profit by Trading Style: Breaks down profits and losses based on your chosen trade categories. This helps identify which strategies are performing best.
     

   • Trade Breakdown: Lists every trade for the selected coin, including details like date, price, quantity, and PnL. The final column is for post-trade reflections, allowing you to add notes on what went right or wrong to inform future decisions.
     

2. Asset Performance: Shows live metrics including the asset’s current price, market cap, and 24-hour change in market cap.
   

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 Trading Journal in Google Sheets

Creating a crypto trading journal in Google Sheets involves three main components:

a live data source for fetching crypto prices, a structured log for recording trades, and a dashboard for analyzing performance.

The template integrates all three seamlessly into one connected system:

1. Live Crypto Market Data
   Live prices and market data are automatically fetched via the CoinGecko API. Google Sheets supports fetching data from external sources via built-in functions or custom Apps Script. By combining these techniques with the CoinGecko API, you can retrieve real-time data for a vast range of crypto assets directly into your sheet. This also allows you to set up automatic data refreshes using Apps Script's time-based triggers.

2. Google Sheets Formulas and Tables
   Use built-in Sheets formulas to calculate all key trading metrics such as position value, realized and unrealized PnL, and total portfolio balance. All calculations update dynamically whenever new trades are logged or when market data refreshes.

3. Structured Trade Logging
   A dedicated Trading Journal sheet lets you record every transaction with clear fields for date, coin, buy/sell, quantity, price, category, and notes. This structured log forms the foundation for all calculations and dashboard visuals.

4. Interactive Dashboards
   Use pivot tables and charts to create dashboards that summarize performance, asset allocation, and trading patterns visually for quick insights.

How to Get Live Crypto Data in Google Sheets

You can get real-time crypto data in Google Sheets by creating custom functions with Google Apps Script that fetches data directly from CoinGecko API.

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); } Copy

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; } Copy

Step 3: Call the Function in Your Sheet

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

1. Construct your CoinGecko API request URL.

2. 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:

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.

The crypto trading journal Google Sheets template is powered by a combination of real-time data from the following endpoints:

• /coins/markets
  Fetches the latest prices and market caps of the top 250 coins. 

• /onchain/networks/{network}/tokens/{address}
  Fetches the latest prices and market data of a specified token contract address on a specified network.

Creating a Crypto Trading Log Sheet in Google Sheets

To calculate your portfolio performance, you first need a record of every trade. Creating a trading log sheet lets you record all buy and sell transactions in one place and automatically calculates profit, loss, and position value using live market data.

Each log entry includes:

• Date of the trade

• Coin name

• Action (Buy or Sell)

• Quantity (number of tokens)

• Average entry/exit price (USD)

• Category (speculation, momentum, news-based, etc.)

• Notes

Creating a Dashboard for Trade Analysis in Google Sheets 

After logging your trades, the next step is to create a dashboard to analyze your performance.  A dashboard in Google Sheets uses formulas to calculate key metrics and charts to visualize your data, providing a clear overview of your trading activity and portfolio growth.

To build an effective analysis dashboard, you will need to implement several core Google Sheets functions and calculation formulas:

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: Finds the average entry price for a holding or the average gain/loss across your winning and losing trades.

• COUNTIFS:Counts the number of trades that meet given criteria — essential for calculating metrics like your win rate or total trades logged.

• INDEX + MATCH: Used to dynamically reference live market data. This combination looks up the current price of each coin from the Available Coins sheet and links it to your trade records for real-time updates.

• FILTER + IF: Used to conditionally display relevant data, such as trades filtered by coin or date range for the Asset Analysis section.

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

• Realised PnL: Taking the difference between the sell price and the average buy cost before the trade, then multiplying by the quantity sold.

• Unrealised PnL: The difference between the current live price and your buy price, multiplied by the quantity still held.

• Win Rate: The number of winning trades divided by total trades.

• Max Drawdown: Largest loss amount among all trades when sorted by PnL.

• Portfolio Value: The current value is a sum of your cash balance (from realized profits) and the market value of all your open positions.

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.

Common Issues & Fixes

If you encounter errors or missing data while using the trading journal, here are a few common issues and how to resolve them quickly.

1. 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.

2. Missing or Incorrect Coin Data
   If a coin’s data doesn’t appear after refreshing:

   • Check that the Coin ID or Contract Address in the AddCoins sheet is correct.

   • Make sure the Source and Network ID fields are filled in properly.

   • Refresh again after fixing the entry.

3. API Key or Endpoint Issues
   If your API key isn’t connecting or you’re seeing Error 401 or Error 403:

   • Confirm your API key and subscription level (demo or pro) are entered correctly in the Read Me sheet.

   • Ensure there are no extra spaces or formatting errors in the key.

4. Script Refresh Delays
   If the data doesn’t refresh automatically, make sure your Apps Script trigger is active:

   • Go to “Extensions” > “Apps Script” > “Triggers”.

   • Confirm that the “triggerAutoRefresh” function is scheduled to run every 5 minutes (or your preferred interval).

   • You can also manually click on “Run”, to force an immediate data refresh.

Further Enhancements

You can further enhance this crypto trading journal spreadsheet 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.

• Add a Crypto Portfolio Tracker
  This trading journal shares many core components with a crypto portfolio tracker. You can extend its functionality to monitor holdings across multiple wallets or exchanges by following our guide on how to create a crypto portfolio tracker in Google Sheets.

• Create a Crypto Exit Strategy Planner
  If you want to plan your trade exits more strategically, consider adding profit-taking and stop-loss levels for each position. Check out our step-by-step guide on how to build a crypto exit strategy planner in Google Sheets.

Conclusion

A trading journal is one of the most effective tools for improving discipline and decision-making as a crypto trader. This free Crypto Trading Journal Google Sheets Template, powered by the CoinGecko API, provides a simple yet powerful way to log trades, and analyze your performance. Because it's built in Google Sheets, your journal is accessible online across all your devices, ensuring you can track your trades anytime and  anywhere.

The integrated dashboard gives you a clear snapshot of your portfolio’s growth and performance trends, while the Asset Analysis sheet helps pinpoint which trading strategies and coins deliver the best results over time.

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 ⬇️