How to get a wallet’s Token Balance inside Google Sheets
The cryptocurrency world is evolving each day and every day. From trading coins to making NFTs, People have found enormous ways to make money out of crypto. When it comes to tracking the investments, people use tools such as Zapper.fi. Apart from Zapper.fi, Most of us use Google Sheets to keep track of the investments we make but sometimes we have to manually update the sheets to see the progress.
Recently I started investing in rebase tokens such as Titano and Sphere and faced this a lot of times because I had to manually update the sheet. After a few months, I finally found a way to automate this. In this guide, You will get an understanding of how to retrieve the holder’s token balance for a specific smart contract.
In this project, We’re going to create a Google App Script Function and use it inside our Google Sheet as a normal formula.
Step #1 — Generating the API Token
Before creating the function, we need to generate an API key from the blockchain explorer that we’re going to use to retrieve the token balances later. For this guide, I am going to use the Polygon network.
First, we need to visit https://polygonscan.com/ and create an account
After creating the account, go to https://polygonscan.com/myapikey to generate the API Key. Click on the Add button and create a token
After generating the token, copy it to a notepad and keep it as we need it in a later part of this guide.
Step #2 — Creating the Google App Script Function
The Google app script function that we’re going to create in this step, will be used as the formula to retrieve the token balance. Just go to Google Sheets and open up a new spreadsheet.
After that, click the Extensions menu item and click “App Script”.
This will open up the App Script project editor in a new tab and You will be offered the following screen.
We can specify a function name that we like. Since this is to retrieve the token balance, I will set it as GETTOKENBALANCE and this function will accept four parameters:
- Your wallet address
- The Smart Contract address
- The network that the smart contract and the wallet belong to
- The divisor which we’ll use to divide the uint token balance that is received from the API. It will have a default value of 18.
This function will use the Polygon Scan API to retrieve the token balance. To retrieve the tokens, we can use the following endpoint:
After we add it to our code, it will look like this:
The URL will be fully created by combining the wallet address, contract address and the network.
After that, we have to call the API to fetch the token balance
That’s it! Press Ctrl + S to save the function
Step #3 — Calling the function/formula inside Google Sheets
Go back to the Google sheet that you have opened and type the function name and pass the parameters:
And voila! You will get the token balance from your wallet address:
Polygon is just only one network that you can use to query through the API but we can add more. I have added few more networks to the code and made it a GitHub Repository. You can check it out here:
Any sort of contributions are highly welcomed.
See you soon with another great topic!
