Skip to main content

Get Real Time Crypto Prices Inside Google Sheets Crypto Portfolio

By April 21, 2018February 1st, 2024API, Blog, Crypto, Google, Google Scripts, Javascript
How To Send Google Form Responses With Google Scripts

If you’re serious about investing and want to know how to make a passive income, you know that price tracking is critical to success. I’m always looking to minimize costs, so I don’t purchase any paid portfolio tracking apps. I use Google Sheets for tracking my crypto portfolio. If you are a buy and hold investor, a hodl stock company can help you ensure that your investment is going the direction you planned.

Aside from crypto, you can also invest in precious metals. Exploring reviews of Invest Diva can provide valuable insights into various investment opportunities, including strategies related to cryptocurrencies and precious metals. Just be careful in choosing your investment partner. Only go for renowned ones like Rosland Capital.

In this article, I will show you how to do research by bitcoin news nowuse the free crypto currency price API from CryptoCompare inside of your Google Sheets crypto portfolio. I will provide you with the Google Script you need to use and instructions on how to implement it, so no coding knowledge is necessary. And if you want to diversify your investments, you can try fx trading by VT markets.

When it comes to successful trading or investing, then esteemed experts such as Andrew Defrancesco comes to mind.

To start off with I’ll assume you have a simple crypto portfolio in Google Sheets. For example, something that looks like this:

Simple Crypto Portfolio

The best way to increase your wealth is looking for gold investment companies and buy them precious metals in this way you will see your income getting bigger. You should read this first before contacting Lear Capital for you gold investment.

What is this spreadsheet missing? Why, the real time price from cryptocurrency news today surely!

How To Add Real Time Crypto Prices To Google Sheet

Here’s comes the fun part of creating the Google Script that will pull prices and create a custom function that we can use anywhere in our spreadsheet. If you are looking for ways to increase your investing return, read this article about Wallet Updates – Polygon.

Step 1: Open The Script Editor

Open Google Script Editor

Open Google Script Editor

Empty Google Script

Empty Google Script

Step 2: Insert Code For Getting Crypto Price

Get your CryptoCompare API key from here (free for 100k calls per month):

function CryptoPrice(ticker) {
  var url = '' + ticker + '&tsyms=USD&api_key=API_KEY_GOES_HERE';
  var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());


  return response.USD;

This is what it should look like:

Complete Crypto Price Google Script

Complete Crypto Price Google Script

Final step here is to save the code: File > Save and close the tab.

Step 3: Using Our New CryptoPrice Function

Now we can use our new function to pull in prices for any coin ticker. For example, to get the Bitcoin price we would enter =CryptoPrice("BTC")  into a spreadsheet cell. For Stellar Lumens it would be =CryptoPrice("XLM")

Step 4: Update Our Spreadsheet With A Price Column

To make everything nice and dynamic, we will use a formula to get current price based on the coin ticker in column A and quantity in column C. For example, the Bitcoin (row 2) formula will look like this =CryptoPrice(A2)*C2. We can then copy that formula down the entire column.

This is how it will look in the end:

That is all. It really is that simple. Now if you’re a developer, you can now build secured blockchains using this blockchain app builder.

Bonus: How To Get Historical Prices To Calculate Profit / Loss

Get your CoinAPI key (for historical prices) from here (free 100 daily requests):

We update our script with this:

function CryptoPrice(ticker, date) {
  var url,
      date = String(date).includes('GMT') ? new Date(String(date)).toISOString().slice(0,10) : date;

  if (date) {
    url = '' + ticker + '/USD/history?period_id=1DAY&limit=1&time_start=' + date + '&apikey=API_KEY_GOES_HERE'
    return JSON.parse(UrlFetchApp.fetch(url).getContentText())[0].price_close;
  } else {
    url = '' + ticker + '&tsyms=USD&api_key=API_KEY_GOES_HERE';
    return JSON.parse(UrlFetchApp.fetch(url).getContentText()).USD;

Usage in an excel formula for this would be =CryptoPrice("BTC", "2021-01-01") or =CryptoPrice("BTC", Today() - 5).

And a full example of our Crypto Google Sheet:

Google Sheet Crypto Portfolio with Profit / Loss

See Example Google Sheet Crypto Portfolio

Author allure

More posts by allure

Join the discussion 23 Comments

  • J.R. says:

    Script isn’t returning values when I tried it this morning? Did they close/stop the API?

  • GV says:

    It doesn’t return values for me either.

  • MJS says:

    I tried it today but it did not return any values. Does that mean the 100k calls per month have already been used up for October?

    • Mike Doubintchik says:

      Should be good now. I’ve updated the article as well with instructions to implement the API Key from cryptocompare.

  • Gary says:

    Thank you very helpful, can someone advise how to set it in Google Sheets so it only refreshes manually so I don’t use up all my calls

    • Mike Doubintchik says:

      There’s different triggers you can setup inside the script editor. If you don’t setup any triggers, it will only refresh when you first open up the google sheet.

      I have a google drawing of an update button that I inserted into my google sheet. Then you can assign an action to the button to run a script called updateData.

      My helpers file (which contains the method updatedata) looks something like this:

       * Get all sheet names from active spreadsheet
      function sheetNames() {
        var sheetNames = [];
        var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
        for (var i = 0; i < sheets.length; i++) sheetNames.push( sheets[i].getName() );
        return sheetNames;
       * Update all formula data in cell range
      function updateData() {
          var spreadsheet     = SpreadsheetApp.getActiveSpreadsheet(),
              activeSheet     = spreadsheet.getActiveSheet()
              activeSheetName = activeSheet.getSheetName(),
              activeRange     = activeSheet.getActiveRange(),
              sheets          = sheetNames(),
              range           = '';
          // loop through all sheets
          sheets.forEach(function(sheet) {
              var originalFormulas = [],
                  range            = activeRange,
                  defaultRanges    = {
                      'Alts': 'J2:J64',
                      'Ledger': 'D2:D10'
              // set range to active range, otherwise fall back to defined cell range
              range = spreadsheet.getSheetByName(sheet).getRange(defaultRanges[sheet]);
              originalFormulas = range.getFormulas();
              // modify formulas
              var modified = (row) {
                  return (col) { 
                    return col.replace('=', '?');
              // update formulas
              // return formulas to original
  • Josef P Lagorio says:

    Great stuff! Two questions
    1. The historical script doesn’t seem to work. I tried switching the API URL to their v2 after it didn’t work the first time and still now dice.
    2. Secondly, love the refresh button with the script, but I’m running into the following error after clicking the button “exception: Argument cannot be null: a1NotationDetails”

    • Mike Doubintchik says:

      1. Yeah, the code is pretty old, but it should still give a good baseline to modify. Maybe by using a different API completely.
      2. Do you mean the refresh button in the comment section? If so, you probably just need to adjust the parameters inside the code.

  • Luke says:

    Hi – thanks for the code! Like Josef said the historical script does not appear to work – it only returns the current price (ELSE argument) no matter what. I tried tinkering with the script, but I’m not a programmer – any chance you could provide an updated script? It looks like it’s not working in your Crypto Portfolio Example link either. I double checked the URL path on CryptoCompare for historical prices and it has stayed the same so not sure where the disconnect is.

  • Luke says:

    Awesome – thanks Mike. Only modification I had to make on the formula side was =CryptoPrice("BTC", TEXT( TODAY( ) - 1, """""YYYY-MM-DD""""")) if I want the script to work with dynamic dates.

  • skinny says:

    amazing, works like a charm, thanks!

  • Clovis says:

    Works a beast, thank you!

  • Matias says:

    This works like a charm! Amazing tool, thanks so much!

  • Jory says:

    This is amazing and works perfectly. I built my own sheet to track my dollar cost averages because I couldn’t find software that does it for me.

  • PaulS says:

    I have created a spreadsheet using your script and all works really well. Could you pls explain in more detail how to return AUD instead of USD

Leave a Reply

Designed by

best down free | web phu nu so | toc dep 2017