Custom Queries For Beginners
The superpower of Web3 Sheets is to call any smart contract function from within your Google Sheet. This document will serve as a basic introduction for those unfamiliar to smart contracts or Google Sheets.
4 Steps
- Designate a clear goal. For this example, you want to query the wstETH/stETH conversion rate.
- Find the contract address: wstETH contract.
- Find the function to call by clicking the "Read Contract" button and scrolling until you've found the function. For this example, you want the 12th function, which is
tokensPerStEth(). - With Web3 Sheets installed and enabled on your sheet, copy & paste the following:
=CALLINT("","0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0","tokensPerStEth()")*10^-18
You should expect something close to .89.
Review
Let's understand what was copy-pasted. First, each CALLINT has three parts: the path (often empty), the smart contract address you're calling, and the function you're calling. Since the call doesn't need to parse an object, the path field is empty. Secondly, the target address is the wstETH contract address. Finally, the function is exactly as it appears on the smart contract (case sensitive).
Bonus - Historical info
To take this example further, let's graph the wstETH/stETH rate over time.
- In the left-most column in the top left cell, query for the latest block:
=CURRENTBLOCK() - In the cell to the right, query for the block's timestamp by entering:
=BLOCKTOTIMESTAMP([reference the cell to the left])/86400+DATE(1970,1,1)
- In the cell to the right query, paste this slightly modified query for the tokensPerStEth():
=CALLINT("",COPS("0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0",[reference the left most cell],"ethereum"),"tokensPerStEth()")*10^-18
- Below the top left cell enter
=[reference above cell]-10000, then copy two left cells down. You'll receive the timestamp for the block and the wstETH/stETH rate at the new block. - Lastly, duplicate the row down to least row 30 to have enough data to graph it.