In this article we will show how to import Binance API data to google sheets using a custom apps script. Simply follow the steps below.
The custom apps script that we will use creates a new function called BINANCE. We will cover the syntax and what is possible with this script in more detail after the set up steps below.
By default, Google Sheets recalculates all the formulas in the sheet upon every change in the content and upon reloading. To get real time price values, we need to set it to recalculate every minute.
Click File, then select Settings.
A box labeled Settings for this spreadsheet will be loaded. Click the Calculation tab. A drop-down list of options will appear under the Recalculation tab. Select On change and every minute.
Once done, click Save settings.
After setting up the recalculation settings of the sheet, click Extensions on the main menu, then select Apps Script.
Apps Script will be loaded in a new tab.
Access BINANCE.gs and appsscript.json from the following repository:
https://github.com/rvbautista/binance-to-google-sheets-v2
You can download it or keep them open in separate tabs.
Copy the BINANCE.gs code from the repository above. To do so, click inside the box, then press Ctrl+A (Windows) or ⌘+A (Mac), then Ctrl+C (Windows) or ⌘+C (Mac). After copying it, go to the Apps Script tab. Clear the default code loaded then paste the BINANCE.gs code to it.
Rename the file by doing the following:
Click Save.
Each Google Sheets spreadsheet has its own associated appsscript.json. We will also modify it by replacing it with the one in Github. Hover your cursor over the icons on the left side of Apps Script, then select Project Settings.
The Project Settings will be loaded.
Tick the checkbox besides Show “appsscripts.json” manifest file in editor.
Once done, hover the cursor back to the icons on the left side of Apps Script. Select Editor.
Appsscript.json will appear in the list of Files. Select it.
Clear the code area again, then paste the code from Github appsscript.json file.
Click Save.
You can close the Apps Script tab. Go back to the tab containing your Google Sheets, then reload the page by refreshing it. A new option in the main menu named Binance should appear.
On the main menu, click Binance. Select Authorize add-on!
A box labeled Authorization Required will appear. Click Continue.
A new window will be loaded, asking you to choose the Google account. Most of the time, only the account you are using to create the sheet will be listed. Select it.
A warning saying that Google hasn’t verified the app will appear. Click the link labeled Advanced at the bottom of the message. A link labeled Go to Untitled project (unsafe) will appear. Click it.
A list of permissions needed by the app will be listed next. Click Allow.
The window will close, and a small box will appear on the lower-right corner of the sheet with the message Ready to rock, the add-on is authorized and running, enjoy!
Finally, you can now use the function in the Sheet! For our example, we want to import the current price of Bitcoin. The formula is
=BINANCE(“prices”, “BTC”)
And we add it to the cell where we want to place the value.
Press Enter after placing the formula.
The script works! Note that this involves data that can be publicly accessed via the official Binance website.
BINANCE is a powerful function that can access both the market data and the data you use in your orders. The most basic syntax has the following format:
=BINANCE(data, coin, options)
Where data is the specific information about the coin that you want to import, while options helps set options regarding the data you want imported.
Listed below are some ways of using it:
=BINANCE(“prices”, coin)
Where coin is the ticker code for the cryptos current value you want to import. Enclose the coin in double quotes. With this syntax, the value given will be in terms of USDT, one of the crypto coins pegged in USD. Example:
=BINANCE(“price”, “BTC”)
This will give the current value of 1 BTC in USDT.
If you want to compare the value against a different crypto, specify the other crypto using the following syntax:
=BINANCE(“prices”, coin, ref_coin)
Where ref_coin is the reference crypto used in defining the value of the coin. Enclose both the coin and the ref_coin in double quotes. For example, if you want to get the price of BTC in terms of BUSD, the formula will look like as follows:
=BINANCE(“prices”, “BTC”, “BUSD”)
To import historical crypto data, the syntax is as follows:
=BINANCE(“history”, coinpair)
Where coinpair is a combination of the crypto and the reference used. Enclose coinpair in double quotes. For example, if you want to get the history of BTC in terms of USDT, use the following formula:
=BINANCE(“history”, “BTCUSDT”)
It will give you a table containing the following information about the BTC-USDT exchange for the last 500 hours, with one hour interval each row:
The only problem with this syntax is that the results are arranged in ascending order, pushing the latest results to the end of the range. You can remedy this by simply specifying more options in the formula. For example, you can specify the interval, the date range, and the limit in the number of results. These will be explained through more examples in the next subsections.
You can specify the range of dates of historical crypto data to import:
=BINANCE(“history”, coinpair, “start: start_date, end: end_date”)
Where the start_date and the end_date are in yyyy-mm-dd format. Enclose coinpair in double quotes. For example, we want to import BTC-USDT data from March 20, 2023 to March 23, 2023. The formula becomes
=binance("history", "BTCUSDT", "start: 2023-03-20, end: 2023-03-23")
If you want to import the latest historical crypto data but limit the entries to a certain number, use the following syntax:
=BINANCE(“history”, coinpair, “limit: rows”)
Where rows is the number of rows to add. It will begin from the latest value. For example, we want to import the latest 10 hourly values of BTC-USDT. The formula is then set as
=binance("history", "BTCUSDT", "limit: 10")
You can specify the interval by using the following syntax:
=binance("history", coinpair, "interval: interval_length")
Where interval_length is the length of time interval:
Enclose coinpair in double quotes.
For example, we want to import the value of BTC-USDT exchange rates every 12 hours. Additionally, we also set that we want the latest 10 values. The formula is then set as
=binance("history", "BTCUSDT", "interval: 12h, limit: 10")
Finally, you can get the summary of the last 24 hours of trade of a specified crypto coin. The syntax is
=BINANCE(“stats/24h”, coin)
Where coin is the ticker for a specific crypto coin. Enclose coin in double quotes.
This will give you the following values:
If you enjoyed this article, you might also like our article on how to import Yahoo finance data into Google Sheets or our article on how to do conditional formatting in Google Sheets.
If you want to learn how to create an email list from Google Sheets, we also suggest checking out our detailed guide.
