In the era of cryptocurrencies, Bitcoin was the pioneer, leading to the growth of the Web3 ecosystem with a range of amazing tools. However, tracking investments can become challenging and costly if you have multiple wallets and data spread across different exchanges.

But don't worry, QuickNode is here to help. With QuickNode, you can easily retrieve data without needing to know how to code. We provide all the tools you need to achieve your goals efficiently!

Searching for information about bitcoin transactions

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 BTC Blockbook JSON-RPC.

What is QuickNode?

QuickNode is your ally in the Web3 space, allowing you to search for NFTs across multiple blockchains and create token-gated communities. By harnessing the power of various nodes, you can simplify and enhance your Web3 infrastructure with ease.

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 Bitcoin RPC.

Selecting an endpoint on QuickNode's dashboard.

After that ,select the mainnet network.

QuickNode RPC dashboard.

As a result, you will see these plugins to activate. Please navigate to "Indexed Data" and activate the "BTC Blockbook JSON-RPC"

QuickNode marketplace plugins

In addition, you will find the RPC with the plugin activated, providing the necessary information to track your Bitcoin transactions

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

QuickNode's RPC dashboard.

Manipulating our data on Google Sheets

Before starting to manipulate our data, please create 1 sheet with this name:

BitcoinPortfolio

You can name the tab associated with the sheet differently; in my case, I named it "BitcoinUSD".

BitcoinPortfolio sheets name

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

Google App Scripts

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

Google Apps Script functions to retrieve data for our bitcoin portfolio.

FETCHBALANCEHISTORY.gs

function FETCHBALANCEHISTORY(address, fromX, to, fiatCurrency, groupBy) {
  var url = "YOUR-QUICKNODE-RPC-URL";
  var headers = {
    'Content-Type': 'application/json'
  };
  var payload = {
    "method": "bb_getbalancehistory",
    "params": [
      address, 
      {
        "from": fromX.toString(),
        "to": to.toString(),
        "fiatcurrency": fiatCurrency.toString(),
        "groupBy": groupBy
      }
    ],
    "id": 1,
    "jsonrpc": "2.0"
  };

  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload),
    'headers': headers
  };
 Logger.log(options);
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
  var data = JSON.parse(response.getContentText());

  // Check if the response contains data
  if (data && data.result && data.result.length > 0) {
    let resultArray = [];
    data.result.forEach(function(item) {
      resultArray.push([
        new Date(item.time * 1000),  // Convert UNIX timestamp to a readable date
        item.txs,
        item.rates.usd,
        //Received
        item.received ,
        item.received / 100000000 ,//Done
        (item.received / 100000000) * item.rates.usd, //Done
        //Received
        //Sent
        item.sent,
        item.sent/ 100000000 ,
        (item.sent / 100000000) * item.rates.usd,
        //Sent
        //SentToSelf
        item.sentToSelf,
        item.sentToSelf/ 100000000 ,
        (item.sentToSelf / 100000000) * item.rates.usd
        //SentToSelf
      ]);
    });
    return resultArray; // Return the array
  } else {
    return [['No data available']]; // Return a 2D array with a message if no data is found
  }
}

function putValuesBelow(address, fromX, to, fiatCurrency, groupBy) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell(); // Get the currently active cell
  var values = FETCHBALANCEHISTORY(address, fromX, to, fiatCurrency, groupBy); // Call the FETCHBALANCEHISTORY 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_FETCHBALANCEHISTORY() {
  // Simply return the headers as an array of arrays
  return [
    [
    'Timestamp', 'Transactions','Bitcoin Rate USD', 
    'Sats Received', '[Received] SATS to BTC',"[Received] BTC-USD",
    'Sats Sent','[Sent] SATS TO BTC', '[Sent] BTC-USD',
    'Sats SentToSelf','[SentToSelf]SATS TO BTC','[SentToSelf] BTC-USD',
    ]
  ];
}

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

Run in the following sequence:

FETCHBALANCEHISTORY
GETHEADERS_FETCHBALANCEHISTORY

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

Running our first script on Google Apps Script

Using Google Apps Script to select our account and set permissions for running scripts

Now, Google Apps Script will prompt you to approve the permissions.

Google Apps Script permissions

After that, Google Apps Script will run your code. Don't worry if you see a red message; this happens because the code is running without any data.

Fetching balance from Bitcoin transactions.

Furthermore, let's add headers to provide a better description of our data.

Bitcoin Portfolio header values

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

Values from our bitcoin portfolio values

To retrieve the values from your Bitcoin transactions, you need to follow these steps:

Bitcoin Address.
The starting Unix timestamp (in seconds) of the time range for which balance history is requested).
- You can get the date in Unix in the next site: https://www.epochconverter.com/
The ending Unix timestamp (in seconds) of the time range for which balance history is requested.
Fiat Currency (You can choose one of the following currencies: USD, MXN, AED, ARS, AUD, BDT, BHD, BMD, BRL, BTC, CAD, CHF, CLP, CNY, CZK, DKK, ETH, EUR, GBP, HKD, HUF, IDR, ILS, INR, JPY, KRW, KWD, LKR, MMK, MYR, NGN, NOK, NZD, PHP, PKR, PLN, RUB, SAR, SEK, SGD, THB, TRY, TWD, UAH, VEF, VND, ZAR).
The time interval (in seconds) for grouping the balance data.

Bitcoin transactions with the function FETCHBALANCEHISTORY(address, fromX, to, fiatCurrency, groupBy)

That sounds great! Now, you can see transactions in one place and in an easy way! You can use the data in a more convenient manner for you.

Explaining the data

TimeStamp - The timestamp associated with the period group by 3600 seconds (60 minutes).
Transactions.
Bitcoin Rate USD - The rate associated with that period of time.
Sats Received , [Received] SATS to BTC, [Received] BTC-USD.
Sats Sent , [Sent] SATS TO BTC , [Sent] BTC-USD.
Sats SentToSelf, [SentToSelf] SATS TO BTC, [SentToSelf] BTC-USD.

You can learn more about how to query the service here: https://www.quicknode.com/docs/bitcoin/bb_getbalancehistory

Conclusion

I'm proud of you! You've learned how to retrieve your Bitcoin transactions in Google Sheets in a simple manner. You can improve this workflow and add more features!

If you want a specific feature, you can reach us through the links below.

We are here to support you ❤️ .

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: discord.com/invite/ADjtsHVreT

Twitter: twitter.com/irwingtello

LinkedIn: linkedin.com/in/irwingtello

Email: irwing@dfhcommunity.com

Website: dfhcommunity.com

Youtube: youtube.com/@irwingtellomx

You can support my work here:

buymeacoffee.com/irwingtello

BTC: 34kXK9CpTJP1PyHKw2kUD2bt6rtGcG5CHY

EVM Address: 0x8B98F8Ff69d2A720120eD6C71A9Bc5072b8Eb46D