I know you're as excited as we are about diving into the world of cryptocurrencies right from your Google Sheets, you're in the right place!.

We're going to walk you through how to get up-to-the-minute info on prices, trading volumes, and heaps more from a whopping 350+ exchanges, all without leaving the comfort of your spreadsheet.

Whether you're keeping an eye on your investments, exploring the crypto market, or just plain curious, we've got you covered. So, let's get started and make your Google Sheets a window to the dynamic world of crypto! 🚀

With QuickNode and CoinAPI, you can create your dream portfolio without having to build complex websites or pay for subscriptions to access analytics that you don't need.

This is one of the things that you will achieve by searching for information about prices in different exchanges, which will reduce your headaches!:

What You Will Need

• A Sheet on Google Drive.

What We Will Do

• A Sheet on Google Drive.

• Create an account on QuickNode.

• Activating the Crypto Market Data API plugin.

What is QuickNode?

QuickNode helps you get creative without having to deal with complicated web3 stuff.

Obtaining information from QuickNode

To obtain this valuable information, create an RPC by clicking on 'Endpoints' in QuickNode's dashboard.

In this case, I select the Avalanche RPC.

After that ,select the mainnet network.

As a result, you will see these plugins to activate. Please navigate to "Token & NFTs" and activate the "Crypto Market Data API."

In addition, you will find the RPC with the plugin activated, providing access to information from over 350 exchanges!.

Please click on the "Copy" button to obtain the RPC url.

Manipulating our data on Google Sheets

Before starting to manipulate our data, please create 4 sheets with these names:

• HistoricalTrade.

• Symbols.

• Assets.

• Exchanges.

After that, please search for the "Extensions" tab and click on "App scripts"

Additionally, please create these file names (functions), and copy the code from each file in the next step.

FETCHSYMBOLS.gs

function FETCHSYMBOLS(filter_symbol_id,filter_symbol_type) {
  var url = "YOUR-QUICKNODE-RPC-URL";
  var headers = {
    'Content-Type': 'application/json'
  };
  var payload = {
    "method": "v1/getSymbols",
    "params": [
      {
        "filter_symbol_id": filter_symbol_id,
        "filter_symbol_type": filter_symbol_type
      }
    ],
    "id": 1,
    "jsonrpc": "2.0"
  };

  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload),
    'headers': headers
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  // Check if the response contains data
  if (data && data.result && data.result.length > 0) {

    // Iterate through the result and push each item into the array
    let resultArray=[];
    data.result.forEach(function(item) {
      resultArray.push([item.symbol_id, item.exchange_id, item.symbol_type, item.asset_id_base, item.asset_id_quote, item.data_start, item.data_end, item.price]); 
    });
    return resultArray; // Return the array
  } else {
    return [['No data available']]; // Return a 2D array with a message if no data is found
  }
}

function putValuesBelow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell(); // Get the currently active cell
  var values = FETCHSYMBOLS(); // Call the FETCHSYMBOLS function
  var numRows = values.length;
  var numCols = values[0].length;

  // Place values into cells below the active cell
  sheet.getRange(range.getRow() + 1, range.getColumn(), numRows, numCols).setValues(values);
}
function GETHEADERS_FETCHSYMBOLS() {
  // Simply return the headers as an array of arrays
  return [
    ['Symbol ID', 'Exchange ID', 'Symbol Type', 'Asset ID Base', 'Asset ID Quote', 'Data Start', 'Data End', 'Price']
  ];
}

HISTORICALTRADE.gs

  function HISTORICALTRADE(symbol_id,time_start,time_end,limit) {
  var url = "YOUR-QUICKNODE-RPC-URL";
  var headers = {
    'Content-Type': 'application/json'
  };
  var payload = {
    "method": "v1/getHistoricalTrades",
    "params": [
      {
        "symbol_id": symbol_id,
        // Check each parameter and provide a default string if undefined
        "time_start": time_start,
        "time_end": time_end ,
        "limit": limit
      }
    ],
    "id": 1,
    "jsonrpc": "2.0"
  };
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload),
    'headers': headers
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  // Check if the response contains data
  if (data && data.result && data.result.length > 0) {   
    // Iterate through the result and push each item into the array
    let resultArray=[];
    data.result.forEach(function(item) {
      resultArray.push([item.symbol_id, item.time_exchange, item.time_coinapi, item.uuid, item.price, item.size, item.taker_side]); 
    });
    return resultArray; // Return the array
  } else {
    return [['No data available']]; // Return a 2D array with a message if no data is found
  }
}

function putValuesBelow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell(); // Get the currently active cell
  var values = HISTORICALTRADE(); // Call the FETCHSYMBOLS function
  var numRows = values.length;
  var numCols = values[0].length;

  // Place values into cells below the active cell
  sheet.getRange(range.getRow() + 1, range.getColumn(), numRows, numCols).setValues(values);
}
function GETHEADERS_HISTORICALTRADE() {
  // Simply return the headers as an array of arrays
  return [
    ['Symbol ID', 'TIME EXCHANGE', 'TIME COIN API', 'UUID', 'PRICE', 'SIZE', 'TAKER SIDE']
  ];
}

getAssets.gs

  function getAssets() {
  var url = "YOUR-QUICKNODE-RPC-URL";
  var headers = {
    "Content-Type": "application/json"
  };
  var payload = {
    "method": "v1/getAssets",
    "params": [{}],
    "id": 1,
    "jsonrpc": "2.0"
  };

  var options = {
    "method": "post",
    "headers": headers,
    "payload": JSON.stringify(payload)
  };

  var response = UrlFetchApp.fetch(url, options);
  var json = response.getContentText();
  var data = JSON.parse(json);

  // Find the "CryptoPortfolio" spreadsheet by its name
  var desiredSpreadsheetName = "CryptoPortfolio"; // Name of the spreadsheet you want to edit
  var files = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  var desiredSpreadsheet = null;
  while (files.hasNext()) {
    var file = files.next();
    if (file.getName() === desiredSpreadsheetName) {
      desiredSpreadsheet = SpreadsheetApp.open(file);
      break;
    }
  }

  if (!desiredSpreadsheet) {
    Logger.log("Spreadsheet '" + desiredSpreadsheetName + "' not found.");
    return;
  }

  // Open the specific sheet within the "CryptoPortfolio" spreadsheet
  var desiredSheetName = "Assets"; // Name of the specific sheet you want to edit
  var sheet = desiredSpreadsheet.getSheetByName(desiredSheetName);

  if (!sheet) {
    Logger.log("Sheet '" + desiredSheetName + "' not found in the spreadsheet.");
    return;
  }

  // Clear existing data
  sheet.clear();

  // Write headers
  var headers = Object.keys(data.result[0]);
  sheet.appendRow(headers);

  // Write data
  data.result.forEach(function(row) {
    var rowData = [];
    headers.forEach(function(header) {
      rowData.push(row[header]);
    });
    sheet.appendRow(rowData);
  });
}

getExchanges.gs

  function getExchanges() {
  var url = "YOUR-QUICKNODE-RPC-URL";
  var headers = {
    "Content-Type": "application/json"
  };
  var payload = {
    "method": "v1/getExchanges",
    "params": [],
    "id": 1,
    "jsonrpc": "2.0"
  };

  var options = {
    "method": "post",
    "headers": headers,
    "payload": JSON.stringify(payload)
  };

  var response = UrlFetchApp.fetch(url, options);
  var json = response.getContentText();
  var data = JSON.parse(json);

  // Find the "CryptoPortfolio" spreadsheet by its name
  var desiredSpreadsheetName = "CryptoPortfolio"; // Name of the spreadsheet you want to edit
  var files = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  var desiredSpreadsheet = null;
  while (files.hasNext()) {
    var file = files.next();
    if (file.getName() === desiredSpreadsheetName) {
      desiredSpreadsheet = SpreadsheetApp.open(file);
      break;
    }
  }

  if (!desiredSpreadsheet) {
    Logger.log("Spreadsheet '" + desiredSpreadsheetName + "' not found...");
    return;
  }

  // Open the specific sheet within the "CryptoPortfolio" spreadsheet
  var desiredSheetName = "Exchanges"; // Name of the specific sheet you want to edit
  var sheet = desiredSpreadsheet.getSheetByName(desiredSheetName);

  if (!sheet) {
    Logger.log("Sheet '" + desiredSheetName + "' not found in the spreadsheet.");
    return;
  }

  // Clear existing data
  sheet.clear();

  // Write headers
  var headers = Object.keys(data.result[0]);
  sheet.appendRow(headers);

  // Write data
  data.result.forEach(function(row) {
    var rowData = [];
    headers.forEach(function(header) {
      rowData.push(row[header]);
    });
    sheet.appendRow(rowData);
  });
}

getSymbols.gs

  function FETCHSYMBOLS(filter_symbol_id,filter_symbol_type) {
  var url = "YOUR-QUICKNODE-RPC-URL";
  var headers = {
    'Content-Type': 'application/json'
  };
  var payload = {
    "method": "v1/getSymbols",
    "params": [
      {
        "filter_symbol_id": filter_symbol_id,
        "filter_symbol_type": filter_symbol_type
      }
    ],
    "id": 1,
    "jsonrpc": "2.0"
  };

  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload),
    'headers': headers
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  // Check if the response contains data
  if (data && data.result && data.result.length > 0) {
    //var resultArray = [['Symbol ID', 'Exchange ID', 'Symbol Type', 'Asset ID Base', 'Asset ID Quote', 'Data Start', 'Data End', 'Price']]; // Initialize the array with headers
    // Iterate through the result and push each item into the array
    let resultArray=[];
    data.result.forEach(function(item) {
      resultArray.push([item.symbol_id, item.exchange_id, item.symbol_type, item.asset_id_base, item.asset_id_quote, item.data_start, item.data_end, item.price]); 
    });
    return resultArray; // Return the array
  } else {
    return [['No data available']]; // Return a 2D array with a message if no data is found
  }
}

function putValuesBelow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell(); // Get the currently active cell
  var values = FETCHSYMBOLS(); // Call the FETCHSYMBOLS function
  var numRows = values.length;
  var numCols = values[0].length;

  // Place values into cells below the active cell
  sheet.getRange(range.getRow() + 1, range.getColumn(), numRows, numCols).setValues(values);
}
function GETHEADERS_FETCHSYMBOLS() {
  // Simply return the headers as an array of arrays
  return [
    ['Symbol ID', 'Exchange ID', 'Symbol Type', 'Asset ID Base', 'Asset ID Quote', 'Data Start', 'Data End', 'Price']
  ];
}

After filling in the code in their respective files, this is how it will look

Finally, you need to run each code by clicking on the "Run" button.

As a result, Google Apps Script will prompt you to log in to your account to set the desired permissions.

As a result, Google Apps Script will prompt you to approve the permissions.

For example:
Running the getAssets function,you will see this result.

On the other hand, when you run the getExchanges function, you will obtain the result of Exchanges.

If you want to retrieve Historical Trades, you will need the following data:

• Pair: The pair for which you would like to retrieve information.

• Start Time: The time from which you want to start querying information.

• End Time: The time at which you want to end the querying process.

To retrieve the header names, you can use the function

=GETHEADERS_HISTORICALTRADE()

Moreover, you will see the headers, so you won't need to write each one every time.

To retrieve the value from the Historical Trades for a certain pair, you first need to provide the pair value, followed by the start time and end time for querying the data and the number of data that you want.

=HISTORICALTRADE(symbol_id,time_start,time_end,limit)

I'm proud of you!. You managed to retrieve the Historical Trade data for your first pair!.

Now, let's obtain the actual market data for the pair.

After building our pair, you have the formula to save your time when you want to search different pairs!.

Furthermore,let's get the headers to retrieve the actual value from our pair.

=GETHEADERS_FETCHSYMBOLS()

Now you have the relative headers from symbols.

To achieve our goal, you need to obtain the actual data from the market using this function:

=FETCHSYMBOLS(PAIR,FINANCIAL POSITION)

Finally, we've completed our Crypto Portfolio! .

Now, you can obtain information on any pair from different exchanges in seconds.

Conclusion

We've just set you up to track cryptocurrency values straight from your Google Sheets! .

Now, you can easily stay updated on prices and trading volumes across a whopping 350+ exchanges, all without leaving your familiar spreadsheet.

Whether you're watching your investments, exploring the crypto world, or just curious, we've got your back.

Let's make your Google Sheets a one-stop-shop for all things crypto!

We ❤️ Feedback!

If you have any feedback or questions on this guide, let us know.
Or, feel free to reach out to us via Twitter or our Discord community server.

We’d love to hear from you!

Author: Irwing Tello

Discord: https://discord.com/invite/ADjtsHVreT

Twitter: https://twitter.com/irwingtello

LinkedIn: https://www.linkedin.com/in/irwingtello/

Email: irwing@dfhcommunity.com

Website: dfhcommunity.com

Youtube: https://www.youtube.com/@irwingtellomx

You can support my work here:

https://www.buymeacoffee.com/irwingtello

BTC: 34kXK9CpTJP1PyHKw2kUD2bt6rtGcG5CHY

EVM Address: 0x8B98F8Ff69d2A720120eD6C71A9Bc5072b8Eb46D

Solana: Ey9oVFHW79giacRZaKxigYjeihMsY7ox8jxc7Hp1sJmS