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!

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.

After that ,select the mainnet network.

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

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.

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

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.

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.

Now, Google Apps Script will prompt you to approve the 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.

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

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

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.

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